quota在oracle,Oracle User Space Quota 如何回收

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.