ORA-38301: can not perform DDL/DML over objects in Recycle Bin

SQL> drop tablespace  TBS_EMPTY including contents and datafiles;
drop tablespace  TBS_EMPTY 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

When checked dba_segments there isn't any segment in the tablespaces, then tried clearing the recylebin to ensure all object clearebut that also didn't help. Finally Find out Purge recylebin using Sys user doesn't actually clear anything, neither error out,

You have to use purge dba_recyclebin to clear the contents.


select count(1) from dba_segments where tablespace_name='TBS_EMPTY';

 COUNT(1)

----------
         0

 SELECT ts.name "Tablespace Name", count(1) from recyclebin$ rs, ts$ ts
where ts.ts#=rs.ts#
group by ts.name
/
Tablespace Name                  COUNT(1)
------------------------------ ----------
TBS_EMPTY                             56
TABS001      23
............
..............
...................


SQL> PURGE RECYCLEBIN;
Recyclebin purged.

SELECT ts.name "Tablespace Name", count(1) from recyclebin$ rs, ts$ ts
where ts.ts#=rs.ts#
group by ts.name
/
Tablespace Name                  COUNT(1)
------------------------------ ----------
TBS_EMPTY                             56              <=== still same
TABS001      23
............
..............
...................

Solution :

If you want to clean the recyclebin for all users.

SQL> purge dba_recyclebin;
DBA Recyclebin purged.

OR

if you don't want to purge the recyclebin for all users, use tablespace clause.

SQL > purge TABLESPACE  tablespace_name  ;



SELECT ts.name "Tablespace Name", count(1) from recyclebin$ rs, ts$ ts
where ts.ts#=rs.ts#
 group by ts.name
  /
no rows selected

Once recycle bin is empty , Tablespace can be dropped.

SQL> drop tablespace  TBS_EMPTY including contents and datafiles;
Tablespace dropped.