Enhanced purging of the Oracle recyclebin

Introduction

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:

  • the SQL>purge recyclebin command issued by a segment owner, which purges all recycled segments for that user,
  • the SQL>purge dba_recyclebin, issued by a DBA, that purges all recycled objects in the database,
  • the SQL>PURGE TABLESPACE <tablespace_name> USER <schema_name>; command, which purges objects owned by the given owner, and
  • the 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.

PL/SQL

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.

execute purge_user_recylebin(null,1,'Y');

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *