ORA-604 ORA-38301 ORA-30511 on alter/drop tablespace

一次，不经意间删除一个表空间，结果报以下错误：
SQL> drop tablespace TEST_TBS including contents and datafiles;
drop tablespace TEST_TBS including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


即使，把回收站清空和将表空间offline，也会报该错误：
SQL> purge dba_recyclebin;
DBA Recyclebin purged.

SQL> alter tablespace TEST_TBS offline;
Tablespace altered.

SQL> drop tablespace TEST_TBS including contents and datafiles;
drop tablespace TEST_TBS including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin 

这是怎么回事呢？

SQL> select SEGMENT_NAME,SEGMENT_TYPE,owner from dba_segments where tablespace_name='TEST_TBS';

SEGMENT_NAME                   SEGMENT_TYPE       OWNER
------------------------------ ------------------ ------------------------------
BIN$Pks1AnxmMCTgQ8+Ct10wJA==$0 TABLE              ORACLE     
BIN$Pks790fcQEzgQ8+Ct11ATA==$0 TABLE              ORACLE 

可以看出该表的拥有者，然后登录到该用户下：
SQL> purge recyclebin;
SQL> drop tablespace TEST_TBS including contents and datafiles; 

这样是不是可以删除了，如果还是不行可用下面这个：

SQL> conn / as sysdba
SQL> alter system set recyclebin=off;
SQL> drop tablespace TEST_TBS including contents and datafiles;
SQL> alter system set recyclebin=on;

总的来说，遇到此类问题时，可以这样解决：
1) sqlplus / as sysdba
2) ALTER SYSTEM SET recyclebin = OFF DEFERRED;
3) disconnect and exit sqlplus
4) sqlplus / as sysdba
5) drop tablespace........
6) sqlplus / as sysdba
7) ALTER SYSTEM SET recyclebin = ON DEFERRED; or ALTER SYSTEM SET recyclebin = ON;

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

转载于:http://blog.itpub.net/30430420/viewspace-1792135/