[오라클] Oracle DBMS_METADATA 패키지를 이용해 DB Link DDL 추출하기

다른 오라클 DB 서버의 테이블 구조 DDL 스크립트가 필요할 때 DB LINK를 사용할 수 있다면 오라클이 제공하는 
DBMS_METADATA.GET_DDL 패키지 함수를 이용해서 간단하게 DDL 스크립트를 추출할 수 있습니다.
​

CREATE OR REPLACE FUNCTION  USF_GET_DDL (
 
    I_OWNER      IN VARCHAR2,              
    I_TABLE      IN VARCHAR2,              
    I_DBLINK     IN VARCHAR2 DEFAULT NULL  
  )
  RETURN CLOB
  IS
    V_DDL           CLOB;            
    V_DDL_TMP       VARCHAR2(4000);  
    V_LEN           NUMBER;
   
    V_OBJECT_TYPE   VARCHAR2(200);   
    V_DBLINK        VARCHAR2(200);   
   
  BEGIN
    V_DBLINK       := CASE WHEN I_DBLINK IS NOT NULL THEN '@'||I_DBLINK END;   
    V_LEN          := 0;
   
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'STORAGE'             , FALSE;
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'TABLESPACE'          , FALSE;
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'SEGMENT_ATTRIBUTES'  , FALSE;
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'PRETTY'              , TRUE;
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'SQLTERMINATOR'       , TRUE;
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'REF_CONSTRAINTS'     , FALSE;
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'PARTITIONING'        , FALSE;
    EXECUTE IMMEDIATE  'BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM'||V_DBLINK||'(DBMS_METADATA.SESSION_TRANSFORM, :V1, :V2); END;' USING 'CONSTRAINTS_AS_ALTER', FALSE;
   
    EXECUTE IMMEDIATE 'SELECT DBMS_LOB.GETLENGTH'||V_DBLINK||'(DBMS_METADATA.GET_DDL'||V_DBLINK||'(:V_OBJECT_TYPE, :V_TABLE, :V_OWNER))  FROM DUAL'||V_DBLINK
            INTO V_LEN USING 'TABLE', I_TABLE, I_OWNER;
            
    FOR I IN 1..CEIL(V_LEN/4000) LOOP
      EXECUTE IMMEDIATE 'SELECT TO_CHAR(DBMS_LOB.SUBSTR'||V_DBLINK||'(DBMS_METADATA.GET_DDL'||V_DBLINK||'(:V_OBJECT_TYPE, :V_TABLE, :V_OWNER), 4000, ((:I-1)*4000)+1)) FROM DUAL'||V_DBLINK
              INTO V_DDL_TMP USING 'TABLE', I_TABLE, I_OWNER, I;
      V_DDL := V_DDL || V_DDL_TMP;
    END LOOP;
   
    -- Exclude Supplemental Log
    V_DDL := REGEXP_REPLACE( V_DDL, ',\s*supplemental\s+log\s+group\s+"[^"]+"\s*\("[^"]+"(,\s*"[^"]+")*\)\s*always\s*', '', 1, 1, 'i');
   
    V_DDL := REPLACE( V_DDL, '"'||V_SRC_SCHEMA||'".', '');
   
    RETURN V_DDL;
   
END;