René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Oracle flashback query example 1

create table flashback_query_ex_1 (
  col_1  number,
  col_2  varchar2(10)
);
exec dbms_lock.sleep(5*60+1)
insert into flashback_query_ex_1 values (1, 'one'  );
insert into flashback_query_ex_1 values (3, 'three');
insert into flashback_query_ex_1 values (5, 'five' );

commit;
variable v_scn number
execute :v_scn := dbms_flashback.get_system_change_number
insert into flashback_query_ex_1 values (3, 'two'  );
insert into flashback_query_ex_1 values (4, 'four' );

update flashback_query_ex_1 
       set   col_2 = 'ONE'
       where col_1 = 1;

delete flashback_query_ex_1
       where col_1 = 5;
column nm format 99

select * from flashback_query_ex_1;
---------- ----------
         1 ONE
         3 three
         3 two
         4 four
select * from flashback_query_ex_1 as of scn :v_scn;
     COL_1 COL_2
---------- ----------
         1 one
         3 three
         5 five