ORA-604 ORA-38301 ORA-30511 on alter/drop tablespace or other DDL/DML

故障描述：

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

Retried after purging and making the tablespace offline also

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

解决方法：

Connect as that particular schema 'owner' and then perform purge and then drop the tablespace

SQL> purge recyclebin;

SQL> drop tablespace TEST_TBS including contents and datafiles;

If this does not solve the issue,

Then Quick Workaround is to disable the Recycle Bin and then drop the tablespace and re-enable the recycle bin

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;

If the above steps does not work then use the following method

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;

If all the above options of dba_recyclebin does not help then contact Oracle Support and mention this Note

ITPub博客

ORA-604 ORA-38301 ORA-30511 on alter/drop tablespace or other DDL/DML

最新文章