ORA-38301, ORA-00604, purge dba_recyclebin is not good, ora-38301ora-00604

ORA-38301, ORA-00604, purge dba_recyclebin is not good, ora-38301ora-00604

An error ORA-38301, ORA-00604, purge dba_recyclebin is encountered during alter/drop tablespace

Applicable:
Oracle Database-Enterprise Edition-Version 10.2.0.1 and later
Information in this document applies to any platform.

Symptoms:
When you try to drop an empty tablespace, you encounter a recyclebin-related error.

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

Try purge dba_recyclebin and offline the 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 

Cause:
Query dba_segments. The table space contains 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 

Solution:

Use the schema owner to log on to sqlplus, execute purge recyclebin, and then delete drop tablespace.

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

If this problem persists, the quickest work und is disable recyclebin, drop tablespace, and 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;

--- Note: In 11gR2, recyclebin seems to be a static parameter.

If the preceding steps cannot solve the problem, use the following method:

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;