[Oracle] ROWDEPENDENCIES , ROW_ROWSCN

 

[트랜잭션]

 

트랜잭션(transaction)은 함께 수행해야 하는 작업의 논리적인 단위다.

계좌이체 트랜잭션은 출금 계좌의 잔고를 차감하는 작업과 입금 계좌의 잔고를 증가시키는 작업으로 구성된다.

두 작업은 반드시 하나의 트랜잭션으로 수행되어야 한다.

 

아래 쿼리는 1번 계좌에서 2번 계좌로 10원을 이체하는 트랜잭션을 수행한다.

1번 계좌의 잔고에서 10원을 빼는 쿼리와 2번 계좌의 잔고에 10원을 더하는 쿼리가 하나의 트랜잭션으로 수행되었다.

두 쿼리가 하나의 트랜잭션으로 수행되지 않으면 잔고의 합계가 달라질 수 있다.

 

UPDATE 계좌 SET 잔고 = 잔고 - 10 WHERE 계좌번호 = 1;
UPDATE 계좌 SET 잔고 = 잔고 + 10 WHERE 계좌번호 = 2;
COMMIT;

 

 

[구조]

트랜잭션은 DML 문이나 SET TRANSACTION 문이 실행되면 시작되고, COMMIT 문이나 ROLLBACK 문이 실행되면 종료된다. 트랜잭션이 시작되면 내부적으로 언두 세그먼트(undo segment)가 할당되고,

(언두 세그먼트는 변경 전의 데이터가 저장되는 테이블이다. 트랜잭션 롤백, 읽기 일관성, 트랜잭션 복구 등을 위해 사용된다.)

트랜잭션에 트랜잭션 ID(XID)가 부여된다. 트랜잭션 ID는 언두 세그먼트의 번호, 슬롯, 시퀀스의 조합으로 생성된다.

 

V$TRANSACTION 뷰에서 트랜잭션에 대한 정보를 조회할 수 있다.

아래 쿼리는 결과가 반환되지 않는다.

수행 중인 트랜잭션이 존재하지 않기 때문이다.

 

SELECT * 
 FROM V$TRANSACTION;

 

아래와 같이 INSERT 문을 수행해보자.

INSERT INTO T1 VALUES (3, 50);

 

V$TRANSACTION 뷰를 다시 조회해보면 트랜잭션이 시작된 것을 확인할 수 있다.

START_SCN 열은 트랜잭션이 시작된 SCN을 반환한다.

 

SELECT XID
     , XIDUSN
     , XIDSLOT
     , XIDSQN
     , START_DATE
     , START_SCN 
 FROM V$TRANSACTION;

 

COMMIT 문을 수행하면 트랜잭션이 종료된다.

 

COMMIT;

 

V$TRANSACTION 뷰를 다시 조회하면 결과가 반환되지 않는다.

 

SELECT * 
  FROM V$TRANSACTION;

 

[SCN]

SCN(System Change Number)은 오라클 데이터베이스의 논리적 TIMESTAMP다.

데이터베이스 내부의 작업 순서를 식별하는 용도로 사용된다. 트랜잭션도 내부적으로 SCN을 사용한다.

 

커밋된 SCN의 pseudo-column은 ora_rowscn이라고 하며, 이 컬럼은 버전 쿼리 pseudo-column 중 하나이다.

 

ORA_ROWSCN, 이  pseudo-column은 각 열의 버전에 대해 해당 열의 시스템 변경 번호 (SCN)를 반환한다.

 

T1 테이블의 CD 컬럼 값이 3인 로우의 ORA_ROWSCN 값이 앞서 살펴본 V$TRANSACTION 뷰의 START_SCN 값과 동일한 것을 확인할 수 있다.

 

ORW_ROWSCN에는 다음과 같은 제한 사항이 있다.

외부 테이블이나 뷰를 직접 쿼리할 때 지원되지 않는다.

SELECT ORA_ROWSCN
  FROM T1
 WHERE CD = 3;

 

또한 SCN_TO_TIMESTAMP 함수를 사용하면 SCN 값을 TIMESTAMP 값으로 변환할 수 있다.

SCN_TO_TIMESTAMP 함수는 테이블 데이터를 복구할 때 유용하다. 

SELECT CD
     , VL
     , ORA_ROWSCN
     , SCN_TO_TIMESTAMP (ORA_ROWSCN) AS C1 
  FROM T1;

 

현재 SCN은 V$DATABASE 뷰의 checkpoint_change# 열에서 확인할 수 있다.

SELECT checkpoint_change# 
  FROM V$DATABASE;

 

[ORA-08181 에러]

SCN_TO_TIMESTAMP 함수는 SYS.SMON_SCN_TIME 테이블을 참조한다.

해당 테이블은 데이터베이스가 시작된 시간으로부터 최대 120시간(5일) 동안의 SCN을 저장한다.

(버전에 따라 저장되는 행의 개수가 상이할 수 있다.)

5일 이전의 SCN에 SCN_TO_TIMESTAMP 함수를 사용하면 "ORA-08181: 지정된 번호는 적합한 시스템 변경 번호가 아님" 에러가 발생할 수 있다.

 

아래는 SYS.SMON_SCN_TIME 테이블을 조회한 결과다.

 

SELECT TIME_DP
     , SCN_BAS 
  FROM SYS.SMON_SCN_TIME 
 ORDER BY SCN_BAS DESC;

 

 

[ROWDEPENDENCIES 절]

 

테이블 생성 시 ROWDEPENDENCIES 절을 기술하면 row-level dependency tracking 기능이 활성화된다.

해당 기능을 활성화하면 SCN이 행 수준으로 저장되며 행의 길이가 6바이트씩 증가한다.

기본값은 NOROWDEPENDENCIES로 SCN이 블록 수준으로 저장된다.

 

기본값으로 테이블을 생성한 뒤 SCN을 확인 해보자. 

-- 예제 테이블 생성
CREATE TABLE T1 ( COL1 NUMBER, COL2 NUMBER ) ; 
INSERT INTO T1 VALUES ( 1, 1 ) ;

SELECT XID
     , XIDUSN
     , XIDSLOT
     , XIDSQN
     , START_TIME
     , START_SCN
 FROM V$TRANSACTION; 
 
--XID	            XIDUSN	XIDSLOT	XIDSQN	START_TIME	        START_SCN
--0007001F0004CF8C	7	    31	    315276	05/10/24 09:26:40	1611843511

COMMIT; 

SELECT ORA_ROWSCN 
 FROM T1 
WHERE COL1 = 1 ;

--ORA_ROWSCN
--1611850907

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN )
 FROM T1 
WHERE COL1 = 1 ;


INSERT INTO T1 VALUES ( 2, 2 ) ; 

SELECT XID
     , XIDUSN
     , XIDSLOT
     , XIDSQN
     , START_TIME
     , START_SCN
 FROM V$TRANSACTION; 
 
--XID	            XIDUSN	XIDSLOT	XIDSQN	START_TIME	        START_SCN
--000C0016000AF826	12	    22	    718886	05/10/24 09:32:31	1611857778
 
 
 COMMIT;
 
 
 SELECT COL1
      , COL2
      , SCN_TO_TIMESTAMP(ORA_ROWSCN )
  FROM T1 ; 


--COL1	COL2	SCN_TO_TIMESTAMP(ORA_ROWSCN)
--1	    1	    2024/05/10 09:33:14
--2	    2	    2024/05/10 09:33:14

 

SCN이 블록 단위로 저장되기 때문에 1번 로우와 2번 로우의 ORA_ROWSCN이 동일한 것을 확인할 수 있다.

다음엔 ROWDEPENDENCIES 를 사용하여 테이블을 만들어 확인 해 보자.

 

DROP TABLE T1 PURGE; 
CREATE TABLE T1 ( COL1 NUMBER, COL2 NUMBER ) ROWDEPENDENCIES; 

INSERT INTO T1 VALUES ( 1, 1 ) ; 

SELECT XID
     , XIDUSN
     , XIDSLOT
     , XIDSQN
     , START_TIME
     , START_SCN
 FROM V$TRANSACTION; 
 
--XID	                XIDUSN	XIDSLOT	XIDSQN	START_TIME	    START_SCN
--0010001D00032BBA	16	    29	    207802	05/10/24 09:37:25	1611871613

COMMIT; 

 SELECT COL1
      , COL2
      , ORA_ROWSCN
      , SCN_TO_TIMESTAMP(ORA_ROWSCN )
  FROM T1 ; 

--COL1 COL2 ORA_ROWSCN	SCN_TO_TIMESTAMP(ORA_ROWSCN)
--1	   1	1611873271	2024/05/10 09:38:11
  
INSERT INTO T1 VALUES ( 2, 2 ) ; 

SELECT XID
     , XIDUSN
     , XIDSLOT
     , XIDSQN
     , START_TIME
     , START_SCN
 FROM V$TRANSACTION;
 
-- XID	XIDUSN	XIDSLOT	XIDSQN	START_TIME	START_SCN
--000C0016000AF82E	12	22	718894	05/10/24 09:40:06	1611878296
 
 COMMIT; 
 
  SELECT COL1
      , COL2
      , ORA_ROWSCN
      , SCN_TO_TIMESTAMP(ORA_ROWSCN )
  FROM T1 ; 
  
--COL1	COL2 ORA_ROWSCN	SCN_TO_TIMESTAMP(ORA_ROWSCN)
--1	    1	 1611873271	2024/05/10 09:38:11
--2	    2	 1611879052	2024/05/10 09:40:23

 

SCN이 로우 수준으로 저장되기 때문에 1번 로우와 2번 로우의 ORA_ROWSCN 값이 다른것을 확인할 수 있다.