Oracle Recyclebin
Jump to navigation
Jump to search
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; /