【Oracle】DBMS_METADATA完全ガイド｜GET_DDL でテーブル・インデックス・ビュー・プロシージャの DDL を取得する方法まで解説

-- 基本構文: DBMS_METADATA.GET_DDL(object_type, object_name, schema)
-- schema を省略すると現在のスキーマが対象になる

-- テーブルの DDL を取得
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM DUAL;

-- 他のスキーマのテーブルを指定する（権限が必要）
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;

-- SQL*Plus / SQLcl で見やすく表示する場合
SET LONG 100000
SET LINESIZE 200
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') AS DDL FROM DUAL;

-- インデックス
SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_NAME_IX') FROM DUAL;

-- ビュー
SELECT DBMS_METADATA.GET_DDL('VIEW', 'EMP_VIEW') FROM DUAL;

-- シーケンス
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'EMP_SEQ') FROM DUAL;

-- ストアドプロシージャ（本体のみ）
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'MY_PROC') FROM DUAL;

-- ファンクション
SELECT DBMS_METADATA.GET_DDL('FUNCTION', 'MY_FUNC') FROM DUAL;

-- パッケージ仕様部
SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'MY_PKG') FROM DUAL;

-- パッケージ本体
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', 'MY_PKG') FROM DUAL;

-- トリガー
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'MY_TRIGGER') FROM DUAL;

-- 型（TYPE）
SELECT DBMS_METADATA.GET_DDL('TYPE', 'MY_TYPE') FROM DUAL;

-- データベースリンク
SELECT DBMS_METADATA.GET_DDL('DB_LINK', 'MY_DBLINK') FROM DUAL;

-- 表領域（DBA権限が必要）
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'USERS') FROM DUAL;

-- セッションレベルの設定（このセッション内の全 GET_DDL に適用される）
BEGIN
    -- セミコロン（;）を末尾に付ける（デフォルト: FALSE）
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        transform_handle => DBMS_METADATA.SESSION_TRANSFORM,
        name             => 'SQLTERMINATOR',
        value            => TRUE
    );

    -- 表領域情報を除外する（環境依存の情報を含めない）
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        transform_handle => DBMS_METADATA.SESSION_TRANSFORM,
        name             => 'TABLESPACE',
        value            => FALSE
    );

    -- ストレージ情報を除外する
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        transform_handle => DBMS_METADATA.SESSION_TRANSFORM,
        name             => 'STORAGE',
        value            => FALSE
    );

    -- セグメント属性情報を除外する（PCTFREE・PCTUSED など）
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        transform_handle => DBMS_METADATA.SESSION_TRANSFORM,
        name             => 'SEGMENT_ATTRIBUTES',
        value            => FALSE
    );
END;
/

-- 設定を適用した後に DDL を取得
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM DUAL;

-- 設定を初期値に戻す
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT');

-- テーブルのトリガーをまとめて取得
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER', 'EMPLOYEES') FROM DUAL;

-- テーブルへの権限付与（GRANT 文）を取得
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMPLOYEES') FROM DUAL;

-- テーブルのインデックスを取得（GET_DDL のスキーマ版）
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'EMPLOYEES') FROM DUAL;

-- 外部キー制約を含む制約情報
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', 'EMPLOYEES') FROM DUAL;
-- ※ 対象テーブルの主キー/ユニークキーを参照している他テーブルの外部キー制約を取得

-- スキーマ内の全テーブルの DDL を順番に取得する
DECLARE
    v_handle NUMBER;
    v_ddl    CLOB;
BEGIN
    -- ① セッション設定（セミコロン付き・ストレージ情報なし）
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

    -- ② ハンドルを OPEN（スキーマ内の全テーブルを対象に）
    v_handle := DBMS_METADATA.OPEN('TABLE');
    DBMS_METADATA.SET_FILTER(v_handle, 'SCHEMA', 'HR');  -- スキーマ名を指定

    -- ③ FETCH で1件ずつ取得
    LOOP
        v_ddl := DBMS_METADATA.FETCH_CLOB(v_handle);
        EXIT WHEN v_ddl IS NULL;   -- NULL が返ったら終了
        DBMS_OUTPUT.PUT_LINE(SUBSTR(v_ddl, 1, 200) || '...');  -- 先頭200文字を出力
    END LOOP;

    -- ④ ハンドルをクローズ
    DBMS_METADATA.CLOSE(v_handle);
END;
/

-- 実用的なパターン: ビューの一覧を取得してスクリプトとして出力
DECLARE
    v_handle NUMBER;
    v_ddl    CLOB;
    v_count  NUMBER := 0;
BEGIN
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

    v_handle := DBMS_METADATA.OPEN('VIEW');
    DBMS_METADATA.SET_FILTER(v_handle, 'SCHEMA', USER);   -- 現在のスキーマ

    LOOP
        v_ddl := DBMS_METADATA.FETCH_CLOB(v_handle);
        EXIT WHEN v_ddl IS NULL;
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('-- View #' || v_count);
        DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_ddl, 32767, 1));
    END LOOP;

    DBMS_METADATA.CLOSE(v_handle);
    DBMS_OUTPUT.PUT_LINE('合計 ' || v_count || ' 件のビュー DDL を取得しました');
END;
/