-- connect system
-- ou en scott avec les elements suivants

-- grant insert, update, delete, select on hr.employees to scott;
-- grant insert, update, delete, select on hr.departments to scott;

-- grant flashback on hr.employees to scott;
-- select table_name from dba_tables where owner like 'HR';
-- grant flashback on scott.villes to scott; 

grant flashback any table to scott;
grant execute on dbms_flashback to scott;
grant select any transaction to scott; -- pour faire appel aux transactions flashback

create table villes (id int, nom varchar(30));

insert into villes values (1, 'vichy');
insert into villes values (2, 'clermont-ferrand');
insert into villes values (3, 'moulins');
insert into villes values (4, 'montlucon');

commit;

var ancien number;

begin
  select dbms_flashback.get_system_change_number into :ancien from dual;
end;

-- ce bloc PL/SQL peut être remplacé par un exec !

print ancien;


delete villes where id > 1;
commit;

select * from villes;
select * from villes as of scn :ancien;


declare 
   cursor cv is select * from villes where id>1;
   rv villes%rowtype;
begin
   dbms_flashback.enable_at_system_change_number(:ancien);
   open cv;
   dbms_flashback.disable;
   loop
      fetch cv into rv;
      exit when cv%notfound;
      insert into villes values (rv.id, rv.nom);
   end loop;
   commit;
end;


select * from villes;

select scn_to_timestamp(:ancien) from dual;
select current_scn from v$database;

select timestamp_to_scn(sysdate) from dual;
select timestamp_to_scn(to_timestamp('18/05/2007 14:00:00','dd/MM/YYYY HH24:MI:SS')) scn from dual;