oracle定期清空回收過程

建立job排程過程實現自動化

當回收站記錄達到200記錄，就開始清空回收站
create or replace procedure clear_recyclebin is

recy_count number(9);
time_drop  varchar2(2000);
sql_1        varchar2(1000);
cursor c_drop_time is select original_name from user_recyclebin where droptime < to_char((sysdate - interval '10' day),'yyyy-mm-dd hh24:mi:ss');

begin
   select count(1) into recy_count from user_recyclebin;
   if recy_count >= 200 then
     for time_drop in c_drop_time
       loop

   

    execute immediate 'purge table||time_drop.original_name||';
    dbms_output.put_line(time_drop.original_name);
      end loop;
   end if;
end    clear_recyclebin;