【翻譯自mos文章】在alter/drop資料表空間時遇到錯誤ORA-38301,ORA-00604,purge dba_recyclebin 也不行，ora-38301ora-00604

【翻譯自mos文章】在alter/drop資料表空間時遇到錯誤ORA-38301,ORA-00604,purge dba_recyclebin 也不行，ora-38301ora-00604

在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相關的錯誤 

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也不行：

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

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

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

若是這麼還是不解決問題，最快速的變通方法就是disable掉recyclebin，再drop tablespace，再enable recyclbin

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

---提醒：11gR2裡邊，recyclebin貌似是靜態參數。

若是上面的步驟不能解決問題，請用下面的方法：

1) sqlplus / as sysdba2) ALTER SYSTEM SET recyclebin = OFF DEFERRED;3) disconnect and exit sqlplus4) sqlplus / as sysdba5) drop tablespace........6) sqlplus / as sysdba7) ALTER SYSTEM SET recyclebin = ON DEFERRED; or ALTER SYSTEM SET recyclebin = ON;