Ameer Khan

In the following post I will show you how to purge dba_recyclebin older than X number of days.

For e.g I want to purge recyclebin data older than 30 days

Oracle recommends not to use delete statement on dba_recyclebin as this doesn’t allow to delete data older than 7 days

SQL> Delete from dba_recylebin where droptime <sysdate -30; ERROR at line 1: ORA-01752: cannot delete from view without exactly one key-preserved table

Rather run the following script to generate a select sql and execute it to purge dba_recyclebin

SQL>set head off SQL>spool purge.sql SQL> select ‘purge table ‘||owner||’.”‘||OBJECT_NAME||’”;’ from dba_recyclebin where type=’TABLE’ and to_date(droptime,’YYYY-MM-DD:HH24:MI:SS’)<sysdate-30; SQL>spool off;

This will create a sql with output like below

purge table JHO.”BIN$xXn88xp3GejgU/GEF6wbcA==$0″;                                                                                                        purge table JHO.”BIN$xwxOc+5mLK7gU/GEF6yizA==$0″;                                                                                                       purge table JHO.”BIN$w4MOItowfC/gU/GEF6xrHA==$0″;                                                                                                        purge table JHO.”BIN$x4UA4M5zTZTgU/OEF6xB8w==$0″;

Execute purge.sql file to purge all the tables for older than 30 days in recylebin

SQL>@purge.sql Table purged. Table purged. Table purged. …