在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purgedba_recyclebin也不行

在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purgedba_recyclebin也不行

2015-02-26      0 个评论    来源：msdnchina的专栏  

收藏    我要投稿

在alter/drop表空间时遇到错误ORA-38301,ORA-00604,purge dba_recyclebin 也不行

适用于：
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

症状：
当你试图drop一个empty的tablespace时，遇到与recyclebin相关的错误

?

1 2 3 4 5 6

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

尝试purge dba_recyclebin 并 offline该tablespace也不行：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

原因：
查询 dba_segments显示该表空间内还有segments

?

1 2 3 4 5 6

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

解决方案：

用该schema的owner登陆sqlplus，然后执行purge recyclebin，然后再删除drop tablespace

?

1 2 3

SQL> purge recyclebin;   SQL> drop tablespace TEST_TBS including contents and datafiles;

若是这么还是不解决问题，最快速的变通方法就是disable掉recyclebin，再drop tablespace，再enable recyclbin

?

1 2 3 4 5 6 7

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;

---提醒：11gR2里边，recyclebin貌似是静态参数。

若是上面的步骤不能解决问题，请用下面的方法：

?

1 2 3 4 5 6 7

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;