Soumya's Database Blog : How to purge dba_recyclebin older than 30 days in oracle database 19c

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.

…