oracle 10g recyclebin的一次问题解决

建立测试用户test;

oracle@localhost ~$sqlplus "/as sysdba"

 

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 17 19:59:36 2009

 

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user test identified by 1234;

 

User created.

 

SQL> grant connect,resource to test;

 

Grant succeeded.

 

 

SQL> select NAME,BYTES/1024/1024 from v$datafile where file#=4;

NAME                                               BYTES/1024/1024
-------------------------------------------------- ---------------
/opt/oracle/oradata/KONG/users01.dbf                           100

 

USERS表空间初始大小为100M，自动扩展，最大可扩展到200M，用于测试

 

 

建立测试表：

SQL> CONN /AS SYSDBA
Connected.

 

SQL> CREATE TABLE TEST.PP TABLESPACE USERS AS SELECT * FROM DBA_OBJECTS;

 

Table created.

 

 

建立禁止TEST用户DROP的触发器:

SQL> CREATE OR REPLACE TRIGGER trg_dropdeny
  2  before drop on database
  3  begin
  4  if (ora_login_user='TEST')
then
raise_application_error(
num=>-20000,
  5  msg=>'You can not drop ' || ora_dict_obj_name() || ' ! please ask dba to finish version control.thank you1');
  6  end if;
end;  7    8    9   10 
 11  /

 

Trigger created.

 

SQL> DROP TABLE TEST.PP;

 

Table dropped.

 

SQL> CREATE TABLE TEST.PP1 TABLESPACE USERS AS SELECT * FROM DBA_OBJECTS;

 

Table created.

 

SQL> CONN TEST/1234

Connected.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
PP              BIN$euup9G5zwKDgQAB/AQAX0A==$0 TABLE        2009-12-17:20:21:34

这里我们可以看到删除的表PP存在于recyclebin中，改名为BIN$euup9G5zwKDgQAB/AQAX0A==$0;

 

SQL> INSERT INTO PP1 SELECT * FROM PP1;

 

50795 rows created.

 

SQL> /

 

101590 rows created.

 

SQL> /

 

203180 rows created.

 

SQL> /

 

406360 rows created.

 

SQL> /

 

812720 rows created.

 

SQL> INSERT INTO PP1 SELECT * FROM PP1 WHERE ROWNUM<400000;
INSERT INTO PP1 SELECT * FROM PP1 WHERE ROWNUM<400000
            *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20000: You can not drop BIN$euup9G5zwKDgQAB/AQAX0A==$0 ! please ask dba to
finish version control.thank you1
ORA-06512: at line 4

 

SQL> select NAME,BYTES/1024/1024 from v$datafile where file#=4;

NAME                                               BYTES/1024/1024
-------------------------------------------------- ---------------
/opt/oracle/oradata/KONG/users01.dbf                           100

 

可以看到USERS表空间并没有扩展，当初始分配的大小用完后，就抛出了异常。

 

 

问题分析如下：

recyclebin为oracle10g新增的功能，清除recyclebin里的表的条件：

1、用户执行如下语句：

SQL> PURGE RECYCLEBIN;

2、当recyclebin所在的表空间空闲空间不足时,Oracle就会将recyclebin中的表给彻底删除,然后腾出空间来放要创建的新对象.

 

我们现在遇到的问题刚好属于第二种,通过测试我们发现ORACLE并不去扩展表空间(表空间为自动扩展)，而是认为空闲空间不足，直接触发清除recyclebin里的表。

 

而TEST用户刚好没有这个DROP权限，导致异常出现。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28419/viewspace-622743/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28419/viewspace-622743/