Pages

Sunday, February 7, 2016

Unable to empty RECYCLEBIN even after PURGE DBA_RECYCLEBIN

Recently, while upgrading a database from 11g to 12c, I faced an issue where the RECYCLEBIN just wouldn't empty even after multiple attempts at PURGE DBA_RECYCLEBIN command. Obviously, as you may have already guessed - this is a Bug! 

As usual the 1st line of solution is the Oracle Support documentation, and this is what saved the day.

Unable To Empty or delete rows from Sys.recyclebin$ which is causing dbua (upgrade) stopped and purge dba_recyclebin not helping (Doc ID 1910945.1)

Solution is to manually truncate the recyclebin$.
This can be done by following the simple below steps.


spool truncate_recyclebin.txt
alter system set recyclebin=off scope=spfile;
shutdown immediate
startup
purge recyclebin;
purge dba_recyclebin;
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
truncate table sys.RECYCLEBIN$;
execute dbms_stats.gather_table_stats('SYS','RECYCLEBIN$');
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
purge recyclebin;
purge dba_recyclebin;
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
alter system set recyclebin=on scope=spfile;
shutdown immediate
startup
spool off

I was then able to proceed with my database upgrade, and it's running fine in Oracle 12c as of now.

4 comments: