Oracle 프로시저·함수·뷰 소스를 테이블에 백업하기 (DBMS_METADATA)

운영 DB 에서 누군가 프로시저를 CREATE OR REPLACE 로 덮어써 버리면 이전 소스를 되찾기 어렵다. 소스를 git 으로 관리하는 것이 정답이지만, 그게 안 되는 현장이라면 DB 안에 주기적으로 DDL 을 떠 두는 것만으로도 꽤 든든하다.

1. 백업 테이블

CREATE TABLE z_obj_backup (
    backup_dt      DATE           NOT NULL
  , object_type    VARCHAR2(30)   NOT NULL
  , object_name    VARCHAR2(128)  NOT NULL
  , last_ddl_time  DATE
  , ddl_text       CLOB
  , error_msg      VARCHAR2(4000)
);

이름 길이는 12.2 부터 128바이트까지 쓸 수 있으므로 VARCHAR2(128) 로 잡았다.
last_ddl_time 을 같이 남겨 두면 “언제 바뀐 소스인지” 를 바로 알 수 있다.

2. 백업 프로시저

CREATE OR REPLACE PROCEDURE z_sp_obj_backup
IS
    v_now  DATE := SYSDATE;
    v_ddl  CLOB;
BEGIN
    -- 문장 끝에 '/' 나 ';' 를 붙여 그대로 다시 실행할 수 있게 한다
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

    FOR r IN (
        SELECT object_type
             , object_name
             , last_ddl_time
             -- USER_OBJECTS 의 타입 이름과 GET_DDL 이 받는 타입 이름이 다른 것만 바꾼다
             , CASE object_type
                   WHEN 'PACKAGE'      THEN 'PACKAGE_SPEC'
                   WHEN 'PACKAGE BODY' THEN 'PACKAGE_BODY'
                   WHEN 'TYPE'         THEN 'TYPE_SPEC'
                   WHEN 'TYPE BODY'    THEN 'TYPE_BODY'
                   ELSE object_type
               END AS meta_type
          FROM user_objects
         WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'VIEW', 'TRIGGER',
                               'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
           AND object_name <> 'Z_SP_OBJ_BACKUP'
           AND object_name NOT LIKE 'BIN$%'      -- 휴지통(recycle bin)에 있는 트리거 제외
         ORDER BY object_type, object_name
    ) LOOP
        BEGIN
            v_ddl := DBMS_METADATA.GET_DDL(r.meta_type, r.object_name);

            INSERT INTO z_obj_backup (backup_dt, object_type, object_name, last_ddl_time, ddl_text)
            VALUES (v_now, r.object_type, r.object_name, r.last_ddl_time, v_ddl);
        EXCEPTION
            WHEN OTHERS THEN
                -- 한 객체가 실패해도 나머지는 계속 백업하고, 실패 사유를 남긴다
                INSERT INTO z_obj_backup (backup_dt, object_type, object_name, last_ddl_time, error_msg)
                VALUES (v_now, r.object_type, r.object_name, r.last_ddl_time, SUBSTR(SQLERRM, 1, 4000));
        END;
    END LOOP;

    COMMIT;
END;
/

실행은 이렇게 한다.

BEGIN
    z_sp_obj_backup;
END;
/

컴파일 오류가 난(INVALID) 객체도 소스는 그대로 백업된다.

패키지·타입 이름에 주의

USER_OBJECTS.OBJECT_TYPE 은 PACKAGE BODY 처럼 공백이 들어가지만 DBMS_METADATA.GET_DDL 은 PACKAGE_BODY 같은 이름을 받는다. 또 GET_DDL('PACKAGE', ...) 은 명세(spec)와 본문(body)을 한 번에 돌려주기 때문에, 위처럼 PACKAGE_SPEC/PACKAGE_BODY 로 나눠 받아야 같은 본문이 두 번 쌓이지 않는다. 프로시저·함수·뷰·트리거는 이름이 같아서 그대로 넘기면 된다.

3. 매일 자동으로 돌리기

DBMS_SCHEDULER 에 작업으로 걸어 두면 매일 새벽에 백업이 쌓인다.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'Z_JOB_OBJ_BACKUP'
      , job_type        => 'STORED_PROCEDURE'
      , job_action      => 'Z_SP_OBJ_BACKUP'
      , repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0'
      , enabled         => TRUE
    );
END;
/

작업을 만들려면 CREATE JOB 권한이 필요하다. 매일 전체를 쌓으면 테이블이 금방 커지므로, 바뀐 것만 남기고 싶다면 직전 백업과 last_ddl_time 이 같은 객체는 건너뛰도록 조건을 더하면 된다.

4. 특정 시점의 소스 꺼내 보기

SELECT object_type, object_name, last_ddl_time, ddl_text
  FROM z_obj_backup
 WHERE object_name = 'SP_ORDER_SAVE'
 ORDER BY backup_dt DESC;

참고

다른 스키마의 객체까지 뜨려면 GET_DDL 에 소유자를 넘기고(GET_DDL(type, name, owner)), 실행 계정에 SELECT_CATALOG_ROLE 이 있어야 한다. 프로시저 안에서는 롤 권한이 적용되지 않는 점(정의자 권한)도 함께 고려해야 한다.
파일로 떠 두고 싶다면 Data Pump 로 메타데이터만 받는 방법도 있다. expdp ... CONTENT=METADATA_ONLY INCLUDE=PROCEDURE,FUNCTION,VIEW,PACKAGE
가장 좋은 방법은 여전히 소스를 git 같은 버전 관리에 두고 배포하는 것이다. 이 백업은 그게 안 될 때의 안전망이다.

다시 쓰며 바로잡은 점

원래 프로시저에는 COMMIT 이 없어서, 실행한 세션이 커밋하지 않고 끝나면 백업이 남지 않을 수 있었다. 프로시저 끝에서 커밋하도록 했다.
원래는 INSERT ... SELECT 한 문장이라 객체 하나에서 GET_DDL 이 실패하면 전체가 실패했다. 객체마다 따로 처리하고 실패 사유를 남기도록 바꿨다.
WHEN OTHERS 에서 SUBSTRB(SQLERRM, 12, ...) 로 오류 번호를 잘라 내고 다시 던지던 부분을 없앴다. 원래 오류 번호와 위치를 잃어버려 원인 찾기가 더 어려워진다.
패키지·타입·트리거도 백업하도록 넓히고, PACKAGE BODY 같은 타입 이름 차이를 맞췄다.