Get a Movie, Not a Picture: Flashback Versions Query

Tags:

Oracle Database 10g: The Top 20 Features for DBAs의 Week 1 내용입니다.
(http://www.oracle.com/technology/pub/articles/10gdba/week1_10gdba.html)

다음과 같은 변화가 있다고 할 때,

Insert Statements

insert into rates values (‘EURO’,1.1012);
commit;
update rates set rate = 1.1014;
commit;
update rates set rate = 1.1013;
commit;
delete rates;
commit;
insert into rates values (‘EURO’,1.1016);
commit;
update rates set rate = 1.1011;
commit;

기존의 질의는 최종 결과만 보여주죠

최종 결과만 보여주는 질의

SQL> select * from rates;

CURR RATE
—- ———-
EURO 1.1011

그런데, Flashback Query는 변화되는 내용을 다 볼 수 있다는 거죠.

Flashback query

select versions_starttime, versions_endtime, versions_xid, 
versions_operation, rate
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
/

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
———————- ———————- —————- – ———-
01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM 0002002800000C61 I 1.1012
01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A000A00000029 U 1.1014
01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A000B00000029 U 1.1013
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011

정리하자면 from tbl_name versions between xxx and xxx order by versions_starttime 이 핵심이죠..

되돌리기 SQL

SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = ‘000A000D00000029’;

UNDO_SQL
—————————————————————————-
insert into “ANANDA”.”RATES”(“CURRENCY”,”RATE”) values (‘EURO’,’1.1013′);

무한히 이렇게 되돌려서 볼 수 있다는 건 아니고 UNDO SEGMENT 내에 남아있는 만큼만 볼 수 있습니다. 그래서 UNDO_RETENTION 파라미터와 연관됨.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *