[Oracle] Flashback Query AS OF 절 사용하기

Flashback Query AS OF절

Oracle 9i release 2 버전의 New Feature로 "AS OF"절을 이용해서 특정 시점의 테이블 데이터를 확인할 수 있게 되었습니다. 하지만 특정 시점의 제한은 있습니다. Flashback Query는 Undo Data의 정보를 이용해서 결과를 보여줍니다. 그래서 Undo Data가 살아있는 기간동안만 확인할 수 있는 제한이 있습니다. 보통 Transaction이 마친 후 undo_retention 파라미터값 만큼 Undo Data가 보존되지만, Oracle 11g부터 Flashback Data Archive 기능을 이용해서 더 오랜 기간동안 Undo의 정보를 보존할 수 있게 되었습니다. Flashback Data Archive 설정에 대한 내용은 아래 글을 참조합니다.

[Oracle] Flashback Data Archive 데이터 복원 작업

 

 

Flashback Query 실습

실습환경

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 11.2.0.1
SID : orcl
host : 601d2fce71dc

 

 

실습용 테이블 생성

SYS@orcl> conn scott/tiger
Connected.
SCOTT@orcl> create table emp_test as select * from emp;

Table created.

SCOTT@orcl> select * from emp_test;

scott 계정의 emp 테이블을 CTAS구문으로 emp_test라는 테이블을 생성합니다.

 

 

 

Data Update & Commit

SCOTT@orcl> conn / as sysdba
Connected.
SYS@orcl> 
select 
    current_scn
    ,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
from v$database;

Flashback Query 는 SCN과 Time을 기준으로 해당 시점으로 되돌릴 수 있습니다. 미리 확인해두겠습니다.

 

 

SYS@orcl> conn scott/tiger
Connected.
SCOTT@orcl> update emp_test set sal = 1000;

14 rows updated.

SCOTT@orcl> commit;

Commit complete.

Table의 급여를 모드 1000으로 변경하고 Commit까지 완료했습니다.

 

SCOTT@orcl> select * from emp_test;

데이터가 변경된것을 확인했습니다. 이제 위에서 조회한 SCN과 Time을 기준으로 해당 시점의 데이터를 조회하는 Flashback Query를 사용해보겠습니다.

 

Flashback Query

SCN

SCOTT@orcl> select * from emp_test as of scn 1401101;

 

 

Timestamp

SCOTT@orcl> select * from emp_test as of timestamp to_timestamp('2020-07-21 12:56:14', 'yyyy-mm-dd hh24:mi:ss');

to_timestamp함수를 이용해서 해당 시간의 시점 데이터를 조회했습니다.

SCOTT@orcl> select * from emp_test as of timestamp sysdate - 1/24/60*8;

sysdate 현재시간에서 8분전의 데이터를 확인하도록 쿼리를 작성했습니다.

 

두가지 방식을 이용해서 모두 이전 데이터를 조회하는 작업을 했습니다. Flashback Query는 어디까지나 Undo Data를 사용하기 때문에 undo_retention 혹은 Flashback Data Archive의 retention 기간 내에 데이터만 조회가 가능합니다.