플래시백 버전 질의

첫번째. 사진이 아닌 동영상으로: Flashback 버전 질의

전혀 아무런 설정 없이도 행의 모든 변경 내용을 즉시 식별 가능

Oracle9i Database에는 Flashback 질의 형태의 소위 “타임 머신” 기능이 이미 도입된 바 있습니다. DBA는 이 기능을 통해 실행 취소 세그먼트에 블록의 이전 이미지 복사본이 있으면 열의 값을 특정 시간으로 확인할 수 있습니다. 하지만 Flashback 질의는 두 시점 간의 변경된 데이타를 표시하는 대신 데이타의 고정된 스냅샷을 시간으로 나타내는 데 그칩니다. 외환 관리 등과 관련된 일부 애플리케이션에서는 값 데이타의 변경 내용을 두 시점에서가 아닌 일정 기간으로 확인해야 합니다. Oracle Database 10g에는 Flashback 버전 질의 기능이 있어 이러한 작업을 쉽고 편리하게 수행할 수 있습니다.

테이블 변경 내용 질의

이 예에서는 은행의 외환 관리 애플리케이션을 사용했습니다. 데이타베이스에는 특정 시간의 환율을 기록하는 RATES라는 테이블이 있습니다.

SQL> desc rates

Name              Null    Type

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

CURRENCY                   VARCHAR2(4)

RATE                       NUMBER(15,10)

이 테이블에는 CURRENCY 열에 표시된 기타 여러 통화에 대한 US 달러의 환율이 표시됩니다. 재무 서비스 업계에서는 환율을 단순히 변경될 때마다 업데이트하는 대신 지속적인 기록으로 관리합니다. 이 접근방법은 은행 거래가 “이미 지난 시간”에 이루어져 송금으로 인한 시간상의 손실을 수용할 수 있기 때문에 채택되는 것입니다. 예를 들어, 오전 10:12에 이뤄지지만 오전 9:12에 발효된 거래는 현재 시간이 아닌 오전 9:12의 환율이 적용됩니다.

지금까지는 환율 기록 테이블을 생성해 환율 변경 내용을 저장한 후 해당 테이블에 기록이 있는지 질의할 수 밖에 없었습니다. 이 밖에도 RATES 테이블 자체에 특정 환율이 적용되는 시작 시간과 종료 시간을 기록하는 방법이 있었습니다. 변경 내용이 발생하면 기존 행의 END_TIME 열이 SYSDATE로 업데이트되며 END_TIME이 NULL인 새 환율과 함께 행이 새로 삽입되는 것입니다.

하지만 Oracle Database 10g에는 Flashback 버전 질의 기능이 있어 기록 테이블을 유지하거나 시작 및 종료 시간을 저장할 필요가 없습니다. 대신 이 기능을 사용하면 추가로 설정하지 않고도 과거의 특정 시간에 해당하는 행의 값을 가져올 수 있습니다. 예를 들어, 일상 업무를 수행하는 DBA가 환율을 여러 번 업데이트하거나, 때로는 행을 삭제하고 다시 삽입하기도 한다고 가정합니다.

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;

이러한 일련의 작업이 끝나면 DBA는 다음과 같은 RATE 열의 현재 커밋된 값을 얻게 됩니다.

SQL> select * from rates;CURR       RATE

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

EURO     1.1011

이 결과에는 행을 처음 생성했을 때부터 발생한 모든 변경 내용이 아닌 RATE의 현재 값이 표시됩니다. 따라서 Flashback 질의를 사용하면 해당 시점의 값을 검색할 수 있지만, 여기서 보다 핵심적인 의도는 단순히 특정 시점에 얻은 일련의 스냅샷이 아니라 캠코더를 통해 변경 내용을 기록하는 것 같이 변경 내용의 감사추적(Audit Trail)을 구축하려는 것입니다.
다음 질의에는 테이블의 변경 내용이 나와 있습니다.

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

행을 삭제하고 다시 삽입했더라도 여기에 행의 모든 변경 내용이 표시됩니다. VERSION_OPERATION 열에는 행에서 수행한 작업(삽입/업데이트/삭제)이 나타나며, 이를 위한 모든 과정이 기록 테이블이나 추가 열 없이 이뤄집니다.

위의 질의에서 versions_starttime, versions_endtime, versions_xid, versions_operation 열은 ROWNUM, LEVEL 같이 친숙한 열과 유사한 의사(Pseduo) 열입니다. VERSIONS_STARTSCN 및 VERSIONS_ENDSCN 같은 다른 의사 열에는 해당 시간의 시스템 변경 번호(SCN)가 표시됩니다. 또한 versions_xid 열에는 행을 변경한 트랜잭션 식별자가 표시됩니다. 트랜잭션의 상세 내역은 FLASHBACK_TRANSACTION_QUERY 뷰에 나와 있으며, 여기서 XID 열에는 트랜잭션 ID가 표시됩니다. 예를 들어, 위에서 VERSIONS_XID 값인 000A000D00000029를 사용하면 UNDO_SQL 값에 실제 문이 표시됩니다.

SELECT UNDO_SQL

FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = '000A000D00000029';UNDO_SQL

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

insert into "ANANDA"."RATES"("CURRENCY","RATE") values ('EURO','1.1013');

이 뷰에는 실제 문 외에도 커밋의 타임 스탬프와 SCN을 비롯해 질의 시작 시의 SCN과 타임 스탬프가 표시됩니다.

기간 내 변경 내용 확인

이제 이러한 정보를 효과적으로 사용하는 방법에 대해 알아보도록 하겠습니다. 오후 3:57:54의 RATE 열 값을 확인해야 한다고 가정하면 다음과 같이 실행할 수 있습니다.

select rate, versions_starttime, versions_endtime

from rates versions

between timestamp 

to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')

and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')

/      RATE VERSIONS_STARTTIME     VERSIONS_ENDTIME

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

    1.1011

이 질의는 Flashback 질의와 유사합니다. 위의 예에서 시작 및 종료 시간은 NULL로 해당 시간 간격 동안 환율이 변하지 않았으며 시간 간격을 포함하고 있음을 나타냅니다. 또한 SCN을 사용하면 이전 버전 값을 확인할 수 있으며, SCN 번호는 의사 열인 VERSIONS_STARTSCN 및 VERSIONS_ENDSCN에서 가져옵니다. 다음은 이에 대한 예입니다.

select rate, versions_starttime, versions_endtime

from rates versions

between scn 1000 and 1001

/

키워드 MINVALUE 및 MAXVALUE를 사용하면 실행 취소 세그먼트의 모든 변경 내용이 표시됩니다. 특정 날짜 또는 SCN 값을 범위의 끝점 중 하나로 지정하고 다른 끝점을 리터럴 MAXVALUE 또는 MINVALUE로 지정할 수도 있습니다. 예를 들어, 다음은 전체 범위가 아닌 오후 3:57:52부터의 변경 내용을 알려주는 질의입니다.

select versions_starttime, versions_endtime, versions_xid, 

versions_operation, rate 

from rates versions between timestamp 

to_date('12/11/2003 15:57:52', 'mm/dd/yyyy hh24:mi:ss')

and maxvalue

order by VERSIONS_STARTTIME

/VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     V       RATE

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

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

최종 분석

Flashback 버전 질의는 틀을 벗어나 테이블 변경 내용의 짧은 휘발성 값(value) 감사(Audit)를 복제합니다. 이러한 이점을 통해 DBA는 과거의 특정 값이 아니라 일정 기간의 모든 변경 내용을 가져오므로 실행 취소 세그먼트(Undo Segment)의 데이타를 최대한 활용할 수 있습니다. 따라서 최대한으로 사용할 수 있는 버전은 UNDO_RETENTION 매개변수에 달려있다고 하겠습니다.