[10g] 플래쉬백 Row Level : Version Query, Tranaction Query - ver.dbrang

/********************************************************************************************
-- Title : [10g] 플래쉬백 Row Level : Version Query, Tranaction Query - ver.dbrang
-- Reference : dbrang.tistory.com
-- Key word : flashback 로우레벨 버전쿼리 트랜잭션쿼리
********************************************************************************************/

/************************************************
-- Row Level : Version Query
************************************************/
-- 테이블 생성
-- drop table scott.ttt;
create table scott.ttt
( a number not null primary key
, b number not null
, c varchar2(10) not null
) tablespace users;

-- 데이터 입력
insert into scott.ttt
select 1,1,'aaa' from dual union all
select 2,2,'bbb' from dual union all
select 3,3,'ccc' from dual;
commit;

-- 입력 확인
select * from scott.ttt;

-- 데이터 수정
update scott.ttt
set b = 222
where b = 2;
commit;

-- 입력 확인
select * from scott.ttt;

-- Version Query
select scott.ttt.*
     , versions_startscn, versions_endscn, versions_xid, versions_operation
     , versions_starttime, versions_endtime
from scott.ttt versions between scn minvalue and maxvalue;

-- 복구
update scott.ttt
set b = 2
where b = 222;
commit;

-- 복구 확인
select *
from scott.ttt;

-- Version Query Again
select scott.ttt.*
     , versions_startscn, versions_endscn, versions_xid, versions_operation
     , versions_starttime, versions_endtime
from scott.ttt versions between scn minvalue and maxvalue;

 

/************************************************
-- Row Level : Transaction Query
************************************************/
-- Version Query 확인 & version_xid 확인   
select scott.ttt.*
     , versions_startscn, versions_endscn, versions_xid, versions_operation
     , versions_starttime, versions_endtime
from scott.ttt versions between scn minvalue and maxvalue;

-- Transaction Query & UNDO_SQL 확인
select xid, undo_change#, table_owner || table_name "TABLE", operation, undo_sql
     , start_scn, start_timestamp, commit_scn, commit_timestamp
from flashback_transaction_query
where table_owner = 'SCOTT' and table_name = 'TTT'
and xid = '1300190006000000';

-- UNDO_SQL로 복구
update "SCOTT"."TTT" set "B" = '222' where ROWID = 'AAAMkMAAEAAAACmAAB';
commit;

-- 복구 확인
select * from scott.ttt;