Recently we did migration on one of the database from HP-UX to Linux. Post migration we have provided unlimited quotas on few of its tablespace.
Later some time it came in our compliance tool that user is having UNLIMITED TABLESPACE privilege granted.
So this privilege was revoked and later few days we observed that tablespace quota's were missing.
I've generated a scenario which explains this.
##########################
## Error
##########################
Tablespace Quota's Missing
##########################
## Command Executed
##########################
Revoke unlimited tablespace from XDB;
**************************************** Step By Step Analysis ******************************************************
#########################################
# Reproduce the Issue
#########################################
a) Grant unlimited quota on specific tablespace.
Alter user XDB quota unlimited on USER_DATA_TS01;
SQL> Alter user XDB quota unlimited on USER_DATA_TS01;
User altered.
b) Grant unlimited tablespace privilege to the user.
Grant unlimited tablespace to xdb;
SQL> Grant unlimited tablespace to xdb;
Grant succeeded.
SQL>
=====================================================================================================================
#########################################
# Check the Quota
#########################################
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
USERNAME NAME QUOTA USED
------------------------------ ------------------------------ ------------------------- ----------
XDB USER_DATA_TS01 Unrestricted 57728
=====================================================================================================================
#########################################
# Scenario 1)
#########################################
Grant Unlimited Quota on Tablespace and Revoke UNLIMITED TABLESPACE privilege.
SQL> alter user XDB quota unlimited on USER_DATA_TS01;
User altered.
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.
#### Check Quota
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
no rows selected
SQL>
#### From this we can understand that revoking unlimited tablespace privilege removes unlimited tablespace quota on any/all tablespace to that user.
=====================================================================================================================
#########################################
# Scenario 2)
#########################################
Grant Specific(1gB) Quota on Tablespace and REVOKE UNLIMITED TABLESPACE privilege.
SQL> Alter user XDB quota 1g on USER_DATA_TS01;
User altered.
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
USERNAME NAME QUOTA USED
------------------------------ ------------------------------ ------------------------- ----------
XDB USER_DATA_TS01 1,048,576 57728
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.
#### Check Quota
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
2
no rows selected
SQL>
#### From this we can understand that revoking unlimited tablespace privilege removes all quota's on all tablespace granted to that user.
=====================================================================================================================
So the bottom line is before revoking unlimited tablespace privilege from the user, make a note of the quota's that user has and then once revoked grant the tablespace quota's back.
=====================================================================================================================
