[Oracle] TIMESTAMP를 활용한 데이터 복구

 

TIMESTAMP

 

데이터베이스를 이용하다 보면 실수로 데이터를 삭제하거나 잘못 업데이트하는 경우가 있다.

 

물론 COMMIT을 하기 전이라면 상관이 없다. ROLLBACK을 하면 그만. 하지만 COMMIT을 한 이후에 문제를 확인하여 데이터를 복구해야 하는 경우가 있다.

 

이럴 때 ORACLE의 TIMESTAMP를 이용하면 데이터를 복구할 수 있다.

 

 

---

 

 

TIMESTAMP의 사용 조건

 

1) oracle 9i 이상부터 지원

2) DB 설정된 시간에 따라 오래 지난 데이터는 TIMESTAMP로 복구가 불가능함

 

 

---

 

 

TIMESTAMP 사용법

 

단위는 SECOND, MINUTE, HOUR, DAY로 지정해서 사용할 수 있다. 기준은 현재 시점이고 조건문을 함께 사용하는 것이 좋다.

 

-- 10초 전의 데이터 조회
SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' SECOND)
WHERE 컬럼 = '조건 내용'; -- 조건문 사용 가능
 
-- 10분 전의 데이터 조회
SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)
 
-- 5시간 전의 데이터 조회 
SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '5' HOUR)
 
-- 3일 전의 데이터 조회 
SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '3' DAY)
 
-- 특정 시간 기준으로 데이터 조회
SELECT * FROM USER_TB AS OF TIMESTAMP(TO_DATE('20230317 11:30:00', 'YYYYMMDD HH24:MI:SS'));

 

 

---

 

 

TIMESTAMP로 데이터 쉽게 비교하기

 

어떤 데이터가 변경되었는지 확인하려는 경우 아래와 같이 현재 데이터와 과거 데이터를 UNION ALL로 묶어 보면 비교하기 편하다.

 

-- TIMESTAMP로 현재 데이터와 10분 전 데이터 비교
SELECT * FROM 테이블 WHERE 컬럼 = '조건 내용'
UNION ALL 
SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL 10 MINUTE) WHERE 컬럼 = '조건 내용';

 

 

---

 

 

 

TIMESTAMP로 특정 시점의 데이터 생성 및 수정

 

이제 어떤 데이터가 지워졌는지/바뀌었는지까지는 확인했다. 이제 원래대로 되돌려 놓을 차례. 삭제/변경된 데이터를 하나하나 집어넣을 수는 없으니 TIMESTAMP SELECT INSERT, TIMESTAMP SELECT UPDATE를 이용해 보자.

 

-- 10분전 데이터 조회 TIMESTAMP SELECT INSERT 방식
INSERT INTO 테이블
SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL 10 MINUTE) WHERE 컬럼 = '조건 내용';


-- 10분전 데이터 조회 TIMESTAMP SELECT UPDATE 방식
UPDATE 테이블 A
SET A.변경컬럼 = (SELECT B.변경컬럼 FROM 테이블 B AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL 10 MINUTE)
WHERE A.조건컬럼= B.조건컬럼
AND B.조건컬럼 = '조건 내용')
WHERE A.조건컬럼 = '조건 내용';