In the Oracle RDBMS v10, the ‘recyclebin’ for segments was introduced. If the
recyelebin=on parameter is set, and a table is dropped, it is not actually dropped – it is renamed to a system-generated name. This can really help in situations where a table is accidentally dropped, as it can be easily recovered. However, without monitoring it, it can end up causing problems of many kinds, because it can end up swelling the total number of segments in the database to many times what it was before.
Oracle only has 4 published methods of dropping batches of recycled objects; these being:
SQL>purge recyclebincommand issued by a segment owner, which purges all recycled segments for that user,
SQL>purge dba_recyclebin, issued by a DBA, that purges all recycled objects in the database,
SQL>PURGE TABLESPACE <tablespace_name> USER <schema_name>;command, which purges objects owned by the given owner, and
SQL>PURGE TABLESPACE uwdata USER uwclass;command, which purges objects owned by the given owner and tablespace.
There are no built-in commands that allow only recycle-bin segments done before a certain time, or only recycle-bin objects with more than a certain number of recycled versions of the same table. It is relatively easy to create a stored procedure to handle these cases – this explains how that can be done.
create or replace procedure purge_user_recyclebin( p_purge_before_date in date default=NULL, p_purge_keep_versions in number default=NULL, p_test_only in varchar2 := 'Y' ) is cursor c_purge_before_date is select object_name from user_recyclebin where droptime > p_purge_before_time and can_purge = 'YES' order by droptime; cursor c_purge_before_version is select * from (select original_name, type, object_name, droptime, rank() over (partition by original_name,type order by droptime desc) as obj_rank from user_recyclebin where can_purge = 'YES') where obj_rank > p_purge_keep_versions order by droptime; v_sql varchar2(1024); exception e_bad_parameters; exception e_38302; pragma exception_init(e_38302,-38302); procedure runsql(p_object_name in varchar2) is begin v_sql := 'purge '||p_object_name; if (p_test_only = 'N') then begin execute immediate v_sql; exception when e_38302 then dbms_output.put_line('Warning; object '||p_object_name||' does not exist. Ignoring.'); when others then dbms_output.put_line('Error dropping '||p_object_name); dbms_output.put_line(dbms_utility.format_error_backtrace); end; else dbms_output.put_line(v_sql); end if; end; begin if p_purge_before_date is not null and p_purge_keep_versions is null then for r in c_purge_before_date loop runsql(r.object_name); end loop; elsif p_purge_before_date is null and p_purge_keep_versions is not null then for r in c_purge_before_version loop runsql(r.object_name); end loop; else raise e_bad_parameters; end if; end purge_user_recylebin; /
How to use it
If you’d like to test to see what it would do, without actually purging anything, just do pass the ‘p_test‘ parameter the value ‘Y’, and
set serveroutput on size 1000000. This will list the commands that it would run, but it doesn’t actually run them.
Purging all recyclebin objects before a given date
This will purge all recyclebin objects that were dropped before August 1st, midnight.
execute purge_user_recylebin(to_date('20150801 00:00:00','YYYYMMDD HH24:MI:SS'),NULL,'Y');
Purging recyclebin objects but keeping only the last recyclebin object for each
This will keep only the latest dropped version of any object.
This, when run as a schema owner, can help keep the number of recyclebin objects to a reasonable level, while still retaining the ability to recover more recently dropped segments.