Automatic Guaranteed Restore Point (11g)

-- Script to schedule the creation of a guaranteed restore point on a database -- and to remove the oldest guaranteed restore point when there are more than 2 -- restore points on the database

set serveroutput on declare

v_kount number := 0; v_name varchar2(100); v_name2 varchar2(100); v_time date; v_sql varchar2(4000); v_sql2 varchar2(4000);

cursor rp is select name

    , time
 from v$restore_point

order by time;

begin

dbms_output.enable(100000);

select to_char(sysdate,'MONDD') into v_name from dual;

begin

v_sql := 'create restore point ' || v_name || ' guarantee flashback database';

       dbms_output.put_line('Restore Point Created ' || v_name);

execute immediate v_sql; commit; exception when others then dbms_output.put_line(sqlerrm);

end;

begin

select count(*) into v_kount from v$restore_point;

    if v_kount > 2 then
    
    select name, time into v_name2, v_time from v$restore_point where time = (select min(time) from v$restore_point);
    
    v_sql2 := 'drop restore point ' || v_name2;
    
    execute immediate v_sql2;  
    commit;
    
    dbms_output.put_line('Restore Point Dropped ' || v_name2);
    
    
    else
      
      dbms_output.put_line('Nothing to Drop');
      
    end if;

    exception
    when others then
    dbms_output.put_line(sqlerrm);

end;

end; /