Annoying Tablespaces Quotas

There's one thing about tablespace quotas which I really don't like. If I allocate quota on a tablespace to a user and then I drop that tablespace the quota is not automatically revoked. It still can be seen in DBA_TS_QUOTAS view but with the DROPPED column set as YES. However, if i create afterwards a tablespace with the same name as the one previously dropped the old quota is auto-magically reactivated on this new tablespace which might not be my intention. Let's see it in action:

1. first of all, let's create a dummy tablespace:

SQL> create tablespace test_tbs datafile size 20M;                                                                               

Tablespace created.

2. let's also create a user and grant quota on the TEST_TBS tablespace:

SQL> create user gogu identified by xxx quota unlimited on users;                                                                

User created.

SQL> alter user gogu quota unlimited on test_tbs;                                                                                

User altered.

3. take a look at quotas:

SQL> select * from dba_ts_quotas where username='GOGU';                                                                          

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
USERS           GOGU                     0         -1          0         -1 NO
TEST_TBS        GOGU                     0         -1          0         -1 NO

4. now drop the TEST_TBS tablespace and look again at quotas:

SQL> drop tablespace test_tbs including contents and datafiles;                                                                  

Tablespace dropped.

SQL> select * from dba_ts_quotas where username='GOGU';                                                                          

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
USERS           GOGU                     0         -1          0         -1 NO
TEST_TBS        GOGU                     0         -1          0         -1 YES

Just notice that the DROPPED column is now set to YES for the TEST_TBS tablespace. This I don't like and if I want to revoke the quota oracle complains that it doesn't know anything about the TEST_TBS tablespace.

SQL> alter user gogu quota 0 on test_tbs;                                                                                        
alter user gogu quota 0 on test_tbs
*
ERROR at line 1:
ORA-00959: tablespace 'TEST_TBS' does not exist

Obvious, but then why preserving that quota in DBA_TS_QUOTAS anyway?

5. Let's recreate the TEST_TBS tablespace and then look at quotas:

SQL> create tablespace test_tbs datafile size 20M;                                                                               

Tablespace created.

SQL> select * from dba_ts_quotas where username='GOGU';                                                                          

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
USERS           GOGU                     0         -1          0         -1 NO
TEST_TBS        GOGU                     0         -1          0         -1 NO

See how the "DROPPED" column is now back on "NO". But wait... this TEST_TBS tablespace is a new tablespace which just happen to be named like an old dropped tbs. Bleah... ugly!

So, this boils down to the conclusion that when you are about to drop a tablespace is a good thing to check the quotas allocated to users and to revoke them before dropping the tablespace. Otherwise they will remain in DBA_TS_QUOTAS and they'll be reactivated when a tablespace with the same name is created. Furthermore, I don't know how you can get rid of them if the tablespace no longer exists. Of course, you can create a dummy tablespace with the same name, revoke quotas and after that to drop the dummy tablespace. But this is an awful workaround.

Update: Yet, I see an advantage of the above behaviour. In 11gR2 you can recover a dropped tablespace with TSPITR. After the TSPITR successfully completes and the dropped tablespace is recovered, the old quotas are also reactivated which is a good thing for the users who had objects in that tablespace.