Automatic Guaranteed Restore Point (11g)
Jump to navigation
Jump to search
-- 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; /