[Oracle] rename을 통한 테이블 마이그레이션

온라인 트랜잭션이 특정시간대에만 있거나, 배치를 통한 DML이 발생하는 테이블의 경우 

Online Redefinition이 아닌 rename을 통해 비교적 간단하게 테이블 마이그레이션이 가능하다.

 

이를 통해서 테이블을 파티션화할수도 있고, 컬럼 등을 변경할 수도 있다. 

 

1. 임시테이블 만들기

  - 가능하면 Create table DDL 을 통해 인덱스, CONSTRAINT 없이 NOLOGGING, PARALLEL 옵션을 통해 만들면

    빠른 마이그레이션이 가능하다.

 

  - 테이블 규모가 크지 않고, 완전 동일한 구조를 가진 테이블을 만들경우 (단편화 제거 등을 목표로 할때)

     CTAS 를 통해 간단하게 만들수 있다 (SELECT * FROM 원본테이블 WHERE 1=2)

     CTAS 참고 https://subbak2.tistory.com/29

 

2. 가능하면 LOAD AS SELECT 방식으로 INSERT

  - 스크립트 예시

INSERT /*+ APPEND NOLOGGING FULL(A) PARALLEL(A 8) */
INTO 스키마.임시테이블 A
SELECT /*+ FULL(B) PARALLEL(B 8) */ *
FROM 스키마.원본테이블 B;

COMMIT;

 

  - 12c 부터는 load as select시 통계정보가 기본적으로 생성되므로 불필요할 경우 hidden parameter를 조정해야한다.

    또는 힌트에 NO_GATHER_OPTIMIZER_STATISTICS  명시

참고 - Online Statistics Gathering for Bulk Loads

https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#GUID-E920301F-FFC4-4AB7-8DEB-11BA56088E0B

 

 

3. 임시테이블 인덱스, CONSTRAINT, NOPARALLEL, LOGGING

 - 스크립트 예시

-- 1. 인덱스, CONSTRAINT 생성
CREATE UNIQUE INDEX 스키마.PK이름
ON 스키마.임시테이블 (구성컬럼 순서별 나열)
TABLESPACE 테이블스페이스
STORAGE
(
  INITIAL 64K
  NEXT 1M
)
PARALLEL 8 NOLOGGING;

ALTER TABLE 스키마.테이블이름 ADD CONSTRAINT PK이름 PRIMARY KEY (구성컬럼 순서별 나열);


-- 2. NOPARALLEL, LOGGING
ALTER INDEX 스키마.PK이름 NOPARALLEL LOGGING;
ALTER TABLE 스키마.임시테이블 NOPARALLEL LOGGING;

 

4. RENAME을 통한 스위치

 - 스크립트 예시

-- 테이블이름, 인덱스 이름을 아래와 같이 임시로 붙인다고 가정할때,
-- 원본테이블이름 : TABLENAME
-- 임시테이블이름 : TABLENAME_$T
-- DROP하기 전 임시 테이블 이름 : D_TABLENAME 


--1) 원본 테이블을 DROP 테이블로 이름 바꿔주기
--① 원본 PK, INDEX의 이름 RENAME 
ALTER INDEX 스키마.원본PK이름 RENAME TO D_원본PK이름;

--② 원본 CONSTRAINT RENAME
ALTER TABLE 스키마.원본테이블이름 RENAME CONSTRAINT 원본PK TO D_원본PK이름;

--③ 원본 테이블 RENAME TO D_
ALTER TABLE 스키마.원본테이블이름 RENAME TO D_테이블이름;



--2) 임시테이블을 원본 테이블로 이름 바꿔주기
--① 임시 테이블 RENAME TO 원본테이블
ALTER TABLE 스키마.임시테이블이름 RENAME TO 원본테이블;

--② 임시 CONSTRAINT RENAME
ALTER TABLE 스키마.원본테이블이름 RENAME CONSTRAINT 임시PK TO 원본PK이름;

--③ 임시 PK, INDEX의 이름 RENAME 
ALTER INDEX 스키마.임시PK이름 RENAME TO 원본PK이름;

 

 

5. 혹시 모를 상황을 위한 원복 스크립트 준비

 - 스크립트 예시

--1) 원본 테이블을 DROP 테이블로 이름 바꿔주기
--① 원본 PK, INDEX의 이름 RENAME 
ALTER INDEX 스키마.원본PK이름 RENAME TO 원본PK이름_D;

--② 원본 CONSTRAINT RENAME
ALTER TABLE 스키마.원본테이블이름 RENAME CONSTRAINT 원본PK TO 원본PK이름_D;

--③ 원본 테이블 RENAME TO _D
ALTER TABLE 스키마.원본테이블이름 RENAME TO 테이블이름_D;



--2) 임시테이블을 원본 테이블로 이름 바꿔주기
--① 임시 테이블 RENAME TO 원본테이블
ALTER TABLE 스키마.D_테이블이름 RENAME TO 원본테이블;

--② 임시 CONSTRAINT RENAME
ALTER TABLE 스키마.원본테이블이름 RENAME CONSTRAINT D_임시PK TO 원본PK이름;

--③ 임시 PK, INDEX의 이름 RENAME 
ALTER INDEX 스키마.D_임시PK이름 RENAME TO 원본PK이름;

* 단, RENAME시 찰나의 순간이지만, 테이블이 존재하지 않으므로 온라인 트랜잭션시 TABLE 에러가 발생할 수 있다.

발생에러 : ORA-00942: 테이블 또는 뷰가 존재하지 않습니다.