13일차 # 3-31: Flashback Version Query

  - SELECT의 확장: VERSIONS ... BETWEEN ... 절

  - 데이터의 변경 이력 확인

  - Version Query Pseudocolumns

     : versions_starttime, versions_startscn

       versions_endtime,   versions_endscn

drop table c_emp purge;

create table c_emp as select * from emp;

select empno, ename, sal from c_emp

where empno = 7369;

    EMPNO ENAME             SAL

---------- ---------- ----------

     7369 SMITH             800

update c_emp

set sal = 8000

where empno = 7369;

commit;

----------

update c_emp

set sal = 1000

where empno = 7369;

commit;

----------

update c_emp

set sal = 300

where empno = 7369;

update c_emp

set sal = sal * 1.5

where empno = 7369; --> 한 Tx내에서 여러번의 DML

commit;

----------

update c_emp

set sal = sal + 1000

where empno = 7369; --> commit 하지 않음

select empno, ename, sal from c_emp

where empno = 7369;

    EMPNO ENAME             SAL

---------- ---------- ----------

     7369 SMITH            1450

select sal from c_emp

 versions between scn minvalue and maxvalue

where empno = 7369;

      SAL

----------

      450

     1000

     8000

      800

commit;

select sal from c_emp

 versions between scn minvalue and maxvalue

where empno = 7369;

      SAL

----------

     1450

      450

     1000

     8000

      800

  * flashback versions query pseudocolumns

col versions_starttime format a20

col versions_endtime   format a20

select versions_starttime as start

    , versions_endtime as end

    , sal as salary

from c_emp

 versions between scn minvalue and maxvalue

where empno = 7369;

oj_SQL.txt