Oracle DDL取得 (DBMS_METADATAパッケージを使用) Oracle9i以降

Oracle12.2 PL/SQLパッケージおよびタイプ・リファレンス DBMS_METADATA 使用されるOracleのバージョンのマニュアルを確認してからお使いください。

DBMS_METADATA は多機能だが、バグの可能性があるのでオラクル・サポートにパッチが提供されていないか確認が必要(サポート契約は必須)

get_ddl.sql

--
-- DDL取得 (DBMS_METADATAパッケージを使用) get_ddl.sql
--
--  ここではすべてのOracle ObjectのDDLを取得してる訳ではない。
--  必要ならUSER_OBJECTSから取得するように改良されたし。
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 32767
SET LONG 20000000
SET LONGCHUNKSIZE 20000000
SET PAGESIZE 0
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- DBMS_METADATA 準備
COLUMN TEXT  FORMAT A32767
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM, NAME => 'SQLTERMINATOR', VALUE => TRUE) ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM, NAME => 'SEGMENT_ATTRIBUTES', VALUE => TRUE) ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM, NAME => 'STORAGE', VALUE => FALSE) ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM, NAME => 'TABLESPACE', VALUE => TRUE) ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM, NAME => 'CONSTRAINTS', VALUE => TRUE) ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM, NAME => 'CONSTRAINTS_AS_ALTER', VALUE => TRUE) ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM, NAME => 'FORCE', VALUE => FALSE) ;
--
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL get_ddl_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL get_ddl_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..sql
--
-- 現状、存在しない Oracle ObjectにDBMS_METADATAを実行すると以下のエラーになる。
--
-- ORA-31603: オブジェクト"xxxxxx"(タイプxxxxxxx)がスキーマ"xxxxxxx"で見つかりません。
-- ORA-06512: "SYS.DBMS_METADATA", 行6069
-- ORA-06512: "SYS.DBMS_METADATA", 行8666
-- ORA-06512: 行1
--
-- このエラーを出したくない時はUSER_OBJECTS をPL/SQLでカーソルで対象のオブジェクトが存在するかチェックしてからDBMS_METADATAを実行することが必要。
--
-- CREATE TABLE, COMMENT, INDEX をテーブル毎にまとめたい時はPL/SQLで明示カーソルを作って対応することが必要。
--
-- CREATE TABLEを取得
SELECT
    DBMS_METADATA.GET_DDL('TABLE', TA.TABLE_NAME, USER) AS TEXT
FROM USER_TABLES TA
WHERE
    TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
AND TA.TABLE_NAME NOT IN
    (
    'CHAINED_ROWS'  -- except Oracle side table
  , 'EXCEPTIONS'
  , 'PLAN_TABLE'
  , 'PLAN_TABLE$'
    )
ORDER BY
    TA.TABLE_NAME ;

-- オブジェクト権限やテーブルにぶら下がるTRIGGERもテーブル定義といっしょに取得しておくと便利なんだが。

-- CREATE INDEXを取得
SELECT
    DBMS_METADATA.GET_DDL('INDEX', IX.INDEX_NAME, USER) AS TEXT
FROM USER_TABLES TA
INNER JOIN USER_INDEXES IX
ON  IX.TABLE_NAME = TA.TABLE_NAME  -- INDEXがないテーブルもある
LEFT OUTER JOIN USER_CONSTRAINTS CN
ON  IX.TABLE_NAME = TA.TABLE_NAME
AND IX.INDEX_NAME = CN.CONSTRAINT_NAME  -- 制約がない場合もあるので OUTER JOIN
WHERE
    TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
AND TA.TABLE_NAME NOT IN
    (
    'CHAINED_ROWS'  -- except Oracle side table
  , 'EXCEPTIONS'
  , 'PLAN_TABLE'
  , 'PLAN_TABLE$'
    )
AND (CN.CONSTRAINT_TYPE <> 'P'  -- PRIMARY KEY は DBMS_METADATA.GET_DDL で CREATE TABLE 文の中に出力されるため、ここでは除く。
OR  CN.CONSTRAINT_TYPE IS NULL)
ORDER BY
    IX.TABLE_NAME
  , IX.INDEX_NAME ;

-- COMMENTを取得
SELECT
    DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT', B.TABLE_NAME , USER) AS TEXT
FROM(
    SELECT DISTINCT
        A.TABLE_NAME
    FROM(
        SELECT
            TM.TABLE_NAME
        FROM USER_TABLES TA
        INNER JOIN USER_TAB_COMMENTS TM
        ON  TM.TABLE_NAME = TA.TABLE_NAME
        WHERE
            TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
        AND TA.TABLE_NAME NOT IN
            (
            'CHAINED_ROWS'  -- except Oracle side table
          , 'EXCEPTIONS'
          , 'PLAN_TABLE'
          , 'PLAN_TABLE$'
            )
        AND TM.COMMENTS IS NOT NULL
        UNION ALL
        SELECT DISTINCT
            CM.TABLE_NAME
        FROM USER_TABLES TA
        INNER JOIN USER_COL_COMMENTS CM
        ON  CM.TABLE_NAME = TA.TABLE_NAME
        WHERE
            TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
        AND TA.TABLE_NAME NOT IN
            (
            'CHAINED_ROWS'  -- except Oracle side table
          , 'EXCEPTIONS'
          , 'PLAN_TABLE'
          , 'PLAN_TABLE$'
            )
        AND CM.COMMENTS IS NOT NULL
        ) A
    ) B
ORDER BY
    B.TABLE_NAME ;

-- CREATE MATERIALIZED VIEW を取得
SELECT
    DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', MV.MVIEW_NAME, USER) AS TEXT
FROM USER_MVIEWS MV
ORDER BY
    MV.MVIEW_NAME ;

-- CREATE FUNCTION, PACKAGE, PROCEDURE, SYNONYM, TRIGGER, TYPE を取得
SELECT
    DBMS_METADATA.GET_DDL(OB.OBJECT_TYPE, OB.OBJECT_NAME, USER) AS TEXT
FROM USER_OBJECTS OB
WHERE OB.OBJECT_TYPE IN
    (
    'FUNCTION'
  , 'PACKAGE'  -- PACKAGE BODYを含む
  , 'PROCEDURE'
  , 'TRIGGER'
  , 'TYPE'  -- TYPE BODYを含む
    )
ORDER BY
    OB.OBJECT_TYPE
  , OB.OBJECT_NAME ;


-- CREATE SEQUENCE を取得
SELECT
    DBMS_METADATA.GET_DDL('SEQUENCE', SQ.SEQUENCE_NAME, USER) AS TEXT
FROM USER_SEQUENCES SQ
ORDER BY
    SQ.SEQUENCE_NAME ;

-- CREATE SYNONYM を取得
SELECT
    DBMS_METADATA.GET_DDL('SYNONYM', SY.SYNONYM_NAME, USER) AS TEXT
FROM USER_SYNONYMS SY
ORDER BY
    SY.SYNONYM_NAME ;

-- CREATE DATABASE LINK を取得
SELECT
    DBMS_METADATA.GET_DDL('DB_LINK', DL.DB_LINK, USER) AS TEXT
FROM USER_DB_LINKS DL
ORDER BY
    DL.DB_LINK ;
SPOOL OFF
COLUMN TEXT  CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET PAGESIZE 10000
SET TERMOUT ON