Oracle 10g Recyclebin With Retention

Following is a SQL*Plus script which can purge recycle bin with retention date

Syntax: sqlplus system/password@TNS @purge_recyclebin <Days to keep>

REM Script: purge_recyclebin.sql
REM User must has satisfy any of the following priviledge
REM 1. Has DROP ANY priviledge, like SYSTEM, or DBA
REM 2. Or SYSDBA, like SYS

set pagesize 0 feedback off
set verify off

REM Initialize Date format
alter session set nls_date_format = 'DD-MON-YY hh24:mi:ss';

select sysdate || ' Recycle Bin Purging Start' from dual;

col param1 new_value param1

REM prompt Getting parameter 1 from command line
select &1 Param1 from dual;

REM Printing parameter 1 captured
select 'Retention Day: &param1' Param_In from dual;

col type format a8
col ORIGINAL_NAME format a15
col owner format a9
col ts_name format a10

set pagesize 100
prompt
prompt Recyclebin Summary - Before
select substr(droptime, 1, 7) month, owner, count(*) num_obj,
   sum(space) total_space
from dba_recyclebin group by owner, substr(droptime, 1, 7);

prompt
prompt Recyclebin To Be Purge
select substr(droptime, 1, 7) month, owner, type, count(*) num_obj,
   sum(space) total_space
from dba_recyclebin
where droptime < to_char(sysdate - &&param1 , 'yyyy-mm-dd')
group by owner, substr(droptime, 1, 7), type;


REM List of tables in recyclebin
REM select owner, original_name, droptime, TYPE, ts_name, space
REM from dba_recyclebin order by droptime;

set termout off pagesize 0

spool purge_this
select 'purge table ' || owner || '.' || original_name || ';' Purge_command
from dba_recyclebin
where droptime < to_char(sysdate - &&param1 , 'yyyy-mm-dd')
and type = 'TABLE';
spool off

REM Executing the script
set echo on time on
spool purge_this.out
@purge_this.lst
spool off
set echo off

set pagesize 100 termout on

prompt
prompt Recyclebin Summary - After
select substr(droptime, 1, 7) month, owner, count(*) num_obj,
   sum(space) total_space
from dba_recyclebin group by owner, substr(droptime, 1, 7);

prompt

set pagesize 0
select sysdate || ' Recycle Bin Purging End' from dual;
set pagesize 100 termout on verify on feedback on