Oracle User Space Quota 如何回收

david058

看了刘大  《Know more about Oracle User Space Quota》 http://www.askmaclean.com/archiv ... er-space-quota.html
这篇文章后，我在实验过程中 发现quota unlimited on 用户后，在数据字典基表tsq$中就一直保留，无论后续 quota 0 on 或者 drop tablespace 操作都只是更新  tsq$ 状态，但在 数据库字典视图 USER_TS_QUOTAS 都能看到数据，想请教 除了 drop tablespace xxx including contents and datafiles drop quota; 能删除tsq$数据外 是否还有其他方式删除？

我的实验过程如下：


SQL> CREATE tablespace TEST datafile 'D:\oracle\oradata\a10g\tsb_test.dbf' SIZE 5M;

表空间已创建。

SQL> create user david identified by david;

用户已创建。


SQL> grant create session to david;

授权成功。

SQL> alter user david quota unlimited on test;

用户已更改。

SQL> SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED FROM DBA_TS_QUOTAS WHERE username='DAVID';

TABLESPACE_NAME                USERNAME                        MAX_BYTES DRO
------------------------------ ------------------------------ ---------- ---
TEST                           DAVID                                  -1 NO

SQL> alter user david quota 0 on test;

用户已更改。

SQL> SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED FROM DBA_TS_QUOTAS WHERE username='DAVID';

未选定行

SQL> conn david/david
已连接。


SQL> SELECT TABLESPACE_NAME,MAX_BYTES,DROPPED FROM USER_TS_QUOTAS;

TABLESPACE_NAME                 MAX_BYTES DRO
------------------------------ ---------- ---
TEST                                    0 NO                         -- 只是 MAX_BYTES 修改为 0



SQL> conn / as sysdba
已连接。
SQL> drop tablespace test including contents and datafiles;

表空间已删除。

SQL> conn david/david;
已连接。
SQL> SELECT TABLESPACE_NAME,MAX_BYTES,DROPPED FROM USER_TS_QUOTAS;

TABLESPACE_NAME                 MAX_BYTES DRO
------------------------------ ---------- ---
TEST                                    0 YES                           -- 只是更新DROPPED标识



SQL> CREATE tablespace TEST datafile 'D:\oracle\oradata\a10g\tsb_test.dbf' SIZE

表空间已创建。

SQL> conn david/david;
已连接。
SQL> SELECT TABLESPACE_NAME,MAX_BYTES,DROPPED FROM USER_TS_QUOTAS;

TABLESPACE_NAME                 MAX_BYTES DRO
------------------------------ ---------- ---
TEST                                    0 NO                          -- 新建的test表空间竟然david用户还能使用，是否安全方面存在问题？


SQL> conn / as sysdba
已连接。
SQL> drop tablespace test including contents and datafiles drop quota;

表空间已删除。

SQL>  conn david/david;
已连接。
SQL> SELECT TABLESPACE_NAME,MAX_BYTES,DROPPED FROM USER_TS_QUOTAS;

未选定行                                                              -- 最终删除了，算终极必杀了吧


刘大，是否可以通过 alter 命令可以直接删除基表tsq$数据？

david058

谢谢刘大指点，受教了

Maclean Liu(刘相兵

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 <tablespace> datafile '<datafile spec>' size 2M;
SQL> alter user <user> quota 0 on <tablespace>;
SQL> drop tablespace <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.

david058

补充一下我的数据库版本 10.2.0.5.0  ，操作系统 xp sp3的

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 32-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production