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

Oracle flashback version query example 1

create table version_query_ex_1 (
  ts  date,
  nm  number,
  vc  varchar2(10)
);
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss' 
             nls_timestamp_format='dd.mm.yyyy hh24:mi:ss';
insert into version_query_ex_1 values (sysdate, 11, 'apple' );
insert into version_query_ex_1 values (sysdate, 12, 'pear'  );
insert into version_query_ex_1 values (sysdate, 13, 'orange');

commit;
exec dbms_lock.sleep(2)

column sysdate new_value tm_1 noprint
select sysdate from dual;

exec dbms_lock.sleep(2)
insert into version_query_ex_1 values (sysdate, 21, 'Porsche' );

delete      version_query_ex_1 where nm = 12;

commit;
exec dbms_lock.sleep(2)

column sysdate new_value tm_2 noprint
select sysdate from dual;

exec dbms_lock.sleep(2)
insert into version_query_ex_1 values (sysdate, 31, 'Europe'  );
insert into version_query_ex_1 values (sysdate, 32, 'Africa'  );

delete from version_query_ex_1               where nm = 11;
update      version_query_ex_1 set vc='Asia' where nm = 21;

commit;
exec dbms_lock.sleep(2)

column sysdate new_value tm_3 noprint
select sysdate from dual;

exec dbms_lock.sleep(2)
insert into version_query_ex_1 values (sysdate, 41, 'Coke');

delete from version_query_ex_1 where nm = 31;

commit;
exec dbms_lock.sleep(2)

column sysdate new_value tm_4 noprint
select sysdate from dual;

exec dbms_lock.sleep(2)
delete from version_query_ex_1 where nm = 32;

commit;
exec dbms_lock.sleep(2)

column sysdate new_value tm_5 noprint
select sysdate from dual;

exec dbms_lock.sleep(2)

select * from version_query_ex_1;
column versions_starttime format a19
column versions_endtime   format a19
set    linesize           85

select versions_starttime, versions_endtime, ts, nm, vc 
  from version_query_ex_1 versions between timestamp to_date('&tm_2') and to_date('&tm_3')
 order by nm;