Revoke Quota Unlimited on Tablespace from User

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.