Oracle Recyclebin

Spazio occupato dal recyclebin:

with a as (select sum(r.space) as sp from dba_recyclebin r) 
 select (a.sp*(p.value/1024))/1024 mb_recyclebin from a,v$parameter p where p.name='db_block_size';

Elimina dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni:

begin
  for c1 in (select owner,type,OBJECT_NAME from dba_recyclebin where to_date(DROPTIME,'YYYY-MM-DD:HH24:MI:SS') < sysdate-3 and CAN_PURGE='YES')
   loop
     begin
       dbms_output.put_line(c1.object_name);
       execute immediate 'purge ' || c1.type || ' ' || c1.owner || '."' || c1.object_name || '"';
       exception
        when others then
          null;
     end;
   end loop;
end;
/

Schedula job che elimina periodicamente dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_recyclebin_3days',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
begin
  for c1 in (select owner,type,OBJECT_NAME from dba_recyclebin where to_date(DROPTIME,''YYYY-MM-DD:HH24:MI:SS'') < sysdate-3 and CAN_PURGE=''YES'')
   loop
     begin
       execute immediate ''purge '' || c1.type || '' '' || c1.owner || ''."'' || c1.object_name || ''"'';
       exception
        when others then
          null;
     end;
   end loop;
end;
',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/