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.
