Oracle 백업 테이블을 한 번에 만드는 DDL 생성 SQL (데이터·PK·코멘트)

데이터 이관이나 대량 수정 전에 대상 테이블을 통째로 복사해 두는 일이 잦다. CREATE TABLE ... AS SELECT(CTAS) 한 줄이면 데이터는 복사되지만, PK·인덱스·기본값(DEFAULT)·코멘트는 따라오지 않는다(NOT NULL 제약만 따라온다). 나중에 백업 테이블을 들여다볼 때 PK 와 코멘트가 없으면 불편하므로, 이것까지 DDL 로 만들어 주는 SQL 을 정리했다.

아래 SQL 들은 DDL 문장을 결과로 뽑아 주기만 한다. 결과를 눈으로 확인한 뒤 복사해서 실행하는 방식이라 실수가 적다.

대상 테이블 고르기

네 SQL 모두 같은 target 절을 쓴다. 대상 조건은 여기 한 곳만 고치면 된다.

WITH target AS (
    SELECT table_name
      FROM user_tables
     WHERE (table_name LIKE 'ORD%' OR table_name LIKE 'CUST%')   -- 대상 테이블 조건
       AND dropped   = 'NO'                                       -- 휴지통(BIN$...) 제외
       AND temporary = 'N'                                        -- 임시 테이블 제외
)

1. 테이블 + 데이터 복사

WITH target AS (
    SELECT table_name
      FROM user_tables
     WHERE (table_name LIKE 'ORD%' OR table_name LIKE 'CUST%')
       AND dropped   = 'NO'
       AND temporary = 'N'
)
SELECT 'CREATE TABLE Z_BACKUP_' || t.table_name
       || ' AS SELECT * FROM ' || t.table_name || ';' AS ddl
  FROM target t
 ORDER BY t.table_name;

결과 예시:

CREATE TABLE Z_BACKUP_ORDERS AS SELECT * FROM ORDERS;
CREATE TABLE Z_BACKUP_ORDER_ITEM AS SELECT * FROM ORDER_ITEM;

구조만 필요하면 SELECT * FROM ... WHERE 1 = 0 으로 바꾼다.

2. PK 추가

WITH target AS (
    SELECT table_name
      FROM user_tables
     WHERE (table_name LIKE 'ORD%' OR table_name LIKE 'CUST%')
       AND dropped   = 'NO'
       AND temporary = 'N'
)
SELECT 'ALTER TABLE Z_BACKUP_' || c.table_name
       || ' ADD CONSTRAINT Z_BACKUP_' || c.constraint_name
       || ' PRIMARY KEY ('
       || LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position)
       || ');' AS ddl
  FROM user_constraints c
  JOIN user_cons_columns cc
    ON cc.constraint_name = c.constraint_name
   AND cc.table_name      = c.table_name
  JOIN target t
    ON t.table_name = c.table_name
 WHERE c.constraint_type = 'P'
 GROUP BY c.table_name, c.constraint_name
 ORDER BY c.table_name;

결과 예시:

ALTER TABLE Z_BACKUP_ORDER_ITEM ADD CONSTRAINT Z_BACKUP_ORDER_ITEM_PK PRIMARY KEY (ORDER_NO, ITEM_SEQ);

제약 이름은 스키마 안에서 겹치면 안 된다(PK 를 만들면 같은 이름의 인덱스도 생긴다). 그래서 원래 PK 이름 앞에 Z_BACKUP_ 를 붙였다. PK 이름이 SYS_C0012345 처럼 시스템이 지은 이름이어도 겹치지 않는다.

3. 테이블 코멘트

WITH target AS (
    SELECT table_name
      FROM user_tables
     WHERE (table_name LIKE 'ORD%' OR table_name LIKE 'CUST%')
       AND dropped   = 'NO'
       AND temporary = 'N'
)
SELECT 'COMMENT ON TABLE Z_BACKUP_' || c.table_name
       || ' IS ''' || REPLACE(c.comments, '''', '''''') || ''';' AS ddl
  FROM user_tab_comments c
  JOIN target t
    ON t.table_name = c.table_name
 WHERE c.comments IS NOT NULL
 ORDER BY c.table_name;

4. 컬럼 코멘트

WITH target AS (
    SELECT table_name
      FROM user_tables
     WHERE (table_name LIKE 'ORD%' OR table_name LIKE 'CUST%')
       AND dropped   = 'NO'
       AND temporary = 'N'
)
SELECT 'COMMENT ON COLUMN Z_BACKUP_' || c.table_name || '.' || c.column_name
       || ' IS ''' || REPLACE(c.comments, '''', '''''') || ''';' AS ddl
  FROM user_col_comments c
  JOIN target t
    ON t.table_name = c.table_name
 WHERE c.comments IS NOT NULL
 ORDER BY c.table_name, c.column_name;

REPLACE(c.comments, '''', '''''') 는 코멘트 안의 작은따옴표를 두 개로 바꾼다. 코멘트에 고객's 주소 처럼 따옴표가 하나라도 있으면 이 처리가 없을 때 만들어진 문장이 문법 오류가 난다.

바로 실행하고 싶다면 (PL/SQL)

문장을 복사해 붙이는 대신 한 번에 실행하려면 아래처럼 돌린다. DDL 에는 바인드 변수를 쓸 수 없으므로, 식별자는 DBMS_ASSERT.ENQUOTE_NAME 으로 감싸 안전하게 붙인다. DDL 은 실행할 때마다 자동 커밋된다는 점도 기억해 두자.

DECLARE
    v_src VARCHAR2(200);
    v_dst VARCHAR2(200);
BEGIN
    FOR t IN (
        SELECT table_name
          FROM user_tables
         WHERE (table_name LIKE 'ORD%' OR table_name LIKE 'CUST%')
           AND dropped   = 'NO'
           AND temporary = 'N'
         ORDER BY table_name
    ) LOOP
        v_src := DBMS_ASSERT.ENQUOTE_NAME(t.table_name, FALSE);
        v_dst := DBMS_ASSERT.ENQUOTE_NAME('Z_BACKUP_' || t.table_name, FALSE);
        EXECUTE IMMEDIATE 'CREATE TABLE ' || v_dst || ' AS SELECT * FROM ' || v_src;
    END LOOP;
END;
/

PK·코멘트도 같은 방식으로 위 SQL 결과를 EXECUTE IMMEDIATE 로 돌리면 된다(끝의 ; 는 빼야 한다).

주의할 점

• 이름 길이: 12.2 미만이거나 COMPATIBLE 이 12.2 보다 낮으면 이름은 30바이트까지다. Z_BACKUP_(9자)를 붙이면 긴 테이블 이름은 넘칠 수 있으니 접두어를 ZB_ 처럼 짧게 하거나 해당 테이블만 이름을 따로 정한다. 12.2 이상(19c·23ai)은 128바이트까지 된다.
• 대소문자 이름: 따옴표로 만든 "OrderItem" 같은 테이블은 생성 SQL 결과에도 따옴표를 붙여야 한다. PL/SQL 방식은 ENQUOTE_NAME 이 처리한다.
• 큰 테이블: CTAS 는 직접 경로로 적재해 언두는 거의 쓰지 않지만, 공간은 원본만큼 필요하고 아카이브 로그 모드라면 리두도 그만큼 생긴다. 테이블스페이스와 아카이브 영역 여유를 먼저 확인한다.

다시 쓰며 바로잡은 점

• PK 이름을 REPLACE(제약 이름, 테이블 이름, 'Z_BACKUP_' || 테이블 이름) 로 만들던 방식은, PK 이름에 테이블 이름이 없으면(SYS_C..., PK_주문 등) 원래 이름이 그대로 나와 “이미 있는 이름” 오류가 났다. 접두어를 붙이는 방식으로 바꿨다.
• 코멘트에 작은따옴표가 있으면 생성된 문장이 깨지던 것을 고쳤고, 코멘트가 없는 행은 뺐다.
• USER_TAB_COMMENTS·USER_COL_COMMENTS 에는 뷰도 들어 있어 뷰 코멘트 문장까지 나오던 것을, USER_TABLES 기준 대상 목록과 조인해 막았다.
• 의미 없는 WHERE 1 = 1 과 SUBSTR(...) IN (...) 조건을 정리하고, 대상 조건을 한 곳(target)에 모았다.