ODM FINDING:
DBA_TS_QUOTAS and USER_TS_QUOTAS Reference Dropped Tablespaces [ID 345316.1]
Applies to:
Oracle Server Enterprise Edition - Version: 8.1.7.4 to 9.2.0.8
This problem can occur on any platform.
Oracle Server - Enterprise Edition - Version: 8.1.7.4.0 to 9.2.0.8.0
Symptoms
When a tablespace that has references to user quotas is dropped the quota information it still displayed in DBA_TS_QUOTAS and USER_TS_QUOTAS.
To reproduce the issue:
SQL> create tablespace quota_tst datafile '/tmp/quota_tst.dbf' size 2M;
SQL> alter user scott quota 1M on quota_tst;
SQL> select TABLESPACE_NAME, USERNAME, MAX_BYTES from dba_ts_quotas;
TABLESPACE_NAME USERNAME MAX_BYTES
------------------- ------------------------------ ----------
QUOTA_TST SCOTT 1048576
SQL> connect scott/tiger
SQL> select TABLESPACE_NAME, MAX_BYTES from user_ts_quotas;
TABLESPACE_NAME MAX_BYTES
------------------------------ ----------
USERS 0
QUOTA_TST 1048576
SQL> connect / as sysdba
SQL> drop tablespace quota_tst including contents;
SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES from dba_ts_quotas;
.
TABLESPACE_NAME USERNAME MAX_BYTES
----------------- ------------------------------ ----------
QUOTA_TST SCOTT 1048576
SQL> connect scott/tiger
SQL> select TABLESPACE_NAME,MAX_BYTES from user_ts_quotas;
.
TABLESPACE_NAME MAX_BYTES
----------------------- ----------
USERS 0
QUOTA_TST 1048576
Cause
When a tablespace is dropped the entry for that tablespace remains and is marked as invalid. Oracle avoids reusing this row unless a tablespace of the same name is recreated. Additionally, any quotas that may have existed on the tablespace would have not be dropped either.
Solution
Workaround 1:
Recreate the tablespace, alter user(s) to have 0 quota on the tablespace, and
then drop the tablespace.
.
SQL> create tablespace datafile '' size 2M;
SQL> alter user quota 0 on ;
SQL> drop tablespace including contents;
.
NOTE: This workaround only removes the references in the DBA_TS_QUOTAS view
but in the USER_TS_QUOTAS view the tablespace is still referenced w/ quota of 0.
@ Workaround 2:
@ This is an unpublished method found in Note 138228.1 "After a 'DROP
@ TABLESPACE' statement, users still have quota". This involves changing and
@ rerunning the catalog.sql code that creates the DBA_TS_QUOTAS and USER_view to
@ check for invalid tablespaces.
@ this workaround works but not really recommended as customers would be making updates
@ to core catalogs or views.
This behavior changes in 10gR2.
Options now include KEEP QUOTA and DROP QUOTA clauses for DROP TABLESPACE. Top of Page
该文档介绍了 KEEP QUOTA 和 DROP QUOTA 选项是 10gR2以后出现
在10gR2之前提供的一种workaround是重建 DBA_TS_QUOTAS 和 USER_view 视图 你可以尝试一下
@ Workaround 2:
@ This is an unpublished method found in Note 138228.1 "After a 'DROP
@ TABLESPACE' statement, users still have quota". This involves changing and
@ rerunning the catalog.sql code that creates the DBA_TS_QUOTAS and USER_view to
@ check for invalid tablespaces.