Oracle (2) - COMMIT한 데이터를 다시 가져오자 (TIMESTAMP)

Oracle을 다루다 보면 데이터를 잘못 DELETE나 UPDATE 하는 경우가 있습니다. 그런 경우 ROLLBACK을 하면 해결할 수 있습니다. 하지만 COMMIT을 하면 어떻게 될까요? 이미 데이터가 적용되어 일반적인 방법으로는 복구를  못합니다.

 

하지만 Oracle에는 COMMIT를 하였을 때 임시로 데이터가 저장하게 되어있고, TIMESTAMP를 이용하여 데이터를 복구할수 있습니다.

 

TIMESTAMP

구문은 아래와 같습니다.

SELECT [COLUMN / *] -- 칼럼
FROM [table] -- 대상 테이블
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '[NUMBER]' [SECOND/MINUTE/HOUR/DAY]);
[WHERE]
-- [NUMBER]은 되돌릴 기준 초, 분, 시간, 날짜 설정이 가능하다


-- 특정 시간을 기준으로 데이터 조회
SELECT * 
FROM mytable
AS OF TIMESTAMP(TO_DATE('20230606000000', 'YYYYMMDDHH24MISS'));

TIMESTAMP는 특정 시간의 데이터를 보는 구문입니다. 해당 명령을 이용하여 다양한 방법으로 데이터를 이용할 수 있습니다. 그리고 임시로 데이터를 저장하기 때문에 너무 오래된 데이터는 조회할 수 없습니다.

추가로 TIMESTAMP 명령어는 오라클 9i 버전부터 제공됩니다.

 

사용 예시

1. 1시간 전의 데이터를 조회해보자

SELECT * 
FROM mytable
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '1' HOUR);

mytable의 1시간 전 데이터를 가져왔습니다. 

 

2. (1)번으로 조회한 데이터를 INSERT 해보자 

INSERT INTO mytable
SELECT * 
FROM mytable
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '1' HOUR);

해당 구문은 데이터의 전체를 INSERT 하는 방법입니다. 전체를 지운 경우가 아니고 Primary Key가 걸려있는 경우 무결성 제약 조건 오류가 날 수 있습니다. 그럴 WHERE을 이용하여 적절히 조절하도록 합니다.

 

3. (1)번으로 조회한 데이터를 UPDATE 해보자

UPDATE mytable T1
SET T1.STU_NUM = (
                   SELECT T2.STU_NUM
                   FROM mytable
                   AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '1' HOUR) T2
                   WHERE T1.STU_NUM = T2.STU_NUM
                   AND T2.STU_NM = '홍길동'
              )
WHERE T1.STU_NM = '홍길동';

mytable의 STU_NM이 홍길동인 경우 1시간 전의 학번으로 UPDATE합니다. UPDATE를 하는 경우에 두 테이블을 묶어서 처리할 수 있습니다.

 

4. (1)번으로 조회한 데이터와 현재 데이터 비교하기

SELECT *
FROM mytable
AND STU_NM = '홍길동'
UNION ALL
SELECT *
FROM mytable
AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '1' HOUR)
AND STU_NM = '홍길동';

해당 구문은 mytable에서 STU_NM이 홍길동인 데이터를 조회합니다. 첫 번째는 현재 데이터를 검색하고 두 번째는 1시간 전의 데이터를 검색합니다. 그다음 UNION ALL을 통하여 두 결과를 조회합니다.

 

Reference

https://inseok9068.github.io/oracle/oracle-commit-rollback/

[Oracle] 커밋한 데이터한 복구 방법