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.
Specific Tablespace
To turn quota off, just use 0 to revoke its usage on the tablespace.
SQL> alter user erpapp quota 0 on erpapp_tbs_01;
User altered.
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.
All Tablespace
To take UNLIMITED TABLESAPCE privilege back from the user, we can do this:
SQL> revoke unlimited tablespace from erpapp;
Revoke succeeded.
The system privilege has been removed from the user.