Purge Recycle Bin Older than…

Hello all!

Have your recyclebin being full/big but don’t want to make a complete “PURGE RECYCLE BIN;”?
Want remove only older than x days (like older than 30 days)?

Please don’t:

DELETE from DBA_RECYCLEBIN where droptime<sysdate-30;
PURGE DBA_RECYCLEBIN where droptime<sysdate-30;

A good way to perform that is to execute output from:

# 30 Days
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' 
and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;

# 30 minutes
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' and 
to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(30/(24*60));

There is some reference here too:
How To Drop / Delete / Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes or selective purge of objects from recycle bin (Doc ID 1596147.1)

And here you can see an awesome procedure in case you want to automate that in a job or similar:
http://dbspecialists.com/enhanced-purging-of-the-oracle-recyclebin/

Hope it help you!
Cheers!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.