Revoke Tablespace Quota
In previous post, we have talked about how to set quota on tablespace for users. Now, let's see how to revert it.
To turn quota off, just use 0 to revoke its usage on the tablespace.
SQL> alter user erpapp quota 0 on erpapp_tbs_01;
To check the result, we perform a query on DBA_TS_QUOTAS.
SQL> select * from dba_ts_quotas where username = 'ERPAPP' and tablespace_name = 'USERS';
no rows selected
The quota has gone.
Even though you has reset his quota to 0, the user might still be able to use that tablespace. It's because the user has UNLIMITED TABLESAPCE privilege. So, let's continue.
To take UNLIMITED TABLESAPCE privilege back from the user, we can do this:
SQL> revoke unlimited tablespace from erpapp;
The system privilege has been removed from the user.