Question: How do I schedule a
periodic purge of the items in my recycle bin? I want
to automatically remove all recycle bin tables that were
dropped more then 30 days old.
Answer:
You must always be careful when emptying the recycle bin,
and many DBA's will manually inspect all recycle bin entries
before purging the recycle bin. For more details on
purging from the recycle bin, see my important notes on
recycle bin management.
Scheduled purging of the recycle bin
tables
Note that for autoextend tablespaces, objects
are purged from the recycle bin to reclaim space before any
datafiles are extended. The following script will identify all tables that were
dropped within the past 30 days, and this script can be used
to purge all tables in the recycle bin that are over 30 days
old. This script can be scheduled to run daily via
dbms_scheduler or via a crontab.
This script is for dropped tables. but it can be modified
for other schema objects.
spool
runme.sql
select
-- owner,
--
original_name,
'purge table "' || owner ||
'"."' || object_name || '";'
from
dba_recyclebin
where
can_purge = 'YES'
and
type = 'TABLE'
and
to_date(droptime, 'YYYY-MM-DD:HH24:MI:SS')
< sysdate - 30
order by 1, 2;
-- Inspect the runme.sql output
@runme
