【PL/SQL】DBMS_METADATA完全解説｜DDL取得・SET_TRANSFORM_PARAM・スキーマ一括出力・エラー対処

Oracle でテーブルやビュー、インデックス、シーケンスなどのオブジェクト定義（DDL）を SQL だけで取得したい場面は多くあります。移行スクリプト作成・バージョン管理・環境差分確認など、現場で頻繁に使われる DBMS_METADATA パッケージを、基本から実務パターンまで体系的に解説します。

DBMS_METADATA とは

DBMS_METADATA は Oracle が標準で提供するパッケージで、データベース内のオブジェクト定義（DDL）をプログラムから取得できます。SQL*Plus や SQL Developer の「DDL 表示」機能もこのパッケージを内部で使用しています。

GET_DDL の基本構文

DBMS_METADATA.GET_DDL(
  object_type  IN VARCHAR2,   -- オブジェクト種別（'TABLE', 'VIEW' など）
  name         IN VARCHAR2,   -- オブジェクト名
  schema       IN VARCHAR2 DEFAULT NULL,  -- スキーマ名（省略時は現在のスキーマ）
  version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model        IN VARCHAR2 DEFAULT 'ORACLE',
  transform    IN VARCHAR2 DEFAULT 'DDL'
) RETURN CLOB;

テーブルの DDL を取得する（最もよく使う例）

-- SQL*Plus / SQLcl での前準備
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200

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

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

object_type の主な種別一覧

SET_TRANSFORM_PARAM で出力を整形する

デフォルトの GET_DDL 出力には STORAGE 句・TABLESPACE 句・PCTFREE などの物理属性が含まれ、別環境に流用しにくい場合があります。SET_TRANSFORM_PARAM でこれらを除去できます。

よく使う変換パラメータ一覧

物理属性をすべて除去してシンプルな DDL を取得

-- セッション全体に適用（以降の GET_DDL 呼び出しに有効）
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
END;
/

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM DUAL;

GET_DEPENDENT_DDL で関連オブジェクトを取得

テーブルに紐付くインデックスや制約をまとめて取得するには GET_DEPENDENT_DDL を使います。

-- テーブルに付属するインデックスを取得
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'EMPLOYEES', 'HR') FROM DUAL;

-- テーブルに付属する制約を取得（PK/UK/FK/CHECK）
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'EMPLOYEES', 'HR') FROM DUAL;

-- 参照制約（外部キー）のみ取得
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', 'EMPLOYEES', 'HR') FROM DUAL;

GET_GRANTED_DDL で権限（GRANT 文）を取得

オブジェクト権限やシステム権限の GRANT 文を取得するには GET_GRANTED_DDL を使います。スキーマ移行時に権限設定を再現する際に便利です。

-- EMPLOYEES テーブルのオブジェクト権限（GRANT 文）を取得
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'EMPLOYEES', 'HR') FROM DUAL;

-- ユーザー HR のシステム権限（GRANT 文）を取得
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'HR') FROM DUAL;

-- ユーザー HR のロール付与（GRANT 文）を取得
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'HR') FROM DUAL;

複数オブジェクトの DDL を一括取得

USER_OBJECTS と組み合わせて同種オブジェクトを一括出力

-- 自スキーマのすべてのテーブル DDL を取得
SET LONG 1000000
SET PAGESIZE 0

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name)
FROM   user_tables
ORDER  BY table_name;

-- 自スキーマのすべてのビュー DDL を取得
SELECT DBMS_METADATA.GET_DDL('VIEW', view_name)
FROM   user_views
ORDER  BY view_name;

OPEN / FETCH_DDL / CLOSE でスキーマ全体を一括出力

より細かく制御する場合は、ハンドルベースの API を使います。フィルタや変換パラメータをハンドル単位で指定できるため、セッション設定を汚さずに済みます。

DECLARE
  h    NUMBER;
  th   NUMBER;
  obj  SYS.KU$_DDLS;
BEGIN
  -- ハンドルをオープン（種別: TABLE）
  h  := DBMS_METADATA.OPEN('TABLE');

  -- フィルタ: スキーマを HR に限定
  DBMS_METADATA.SET_FILTER(h, 'SCHEMA', 'HR');

  -- 変換ハンドルで物理属性を除去
  th := DBMS_METADATA.ADD_TRANSFORM(h, 'DDL');
  DBMS_METADATA.SET_TRANSFORM_PARAM(th, 'STORAGE',            FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(th, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(th, 'SQLTERMINATOR',      TRUE);

  -- FETCH ループで全テーブルを出力
  LOOP
    obj := DBMS_METADATA.FETCH_DDL(h);
    EXIT WHEN obj IS NULL;
    FOR i IN 1..obj.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(obj(i).ddltext);
    END LOOP;
  END LOOP;

  DBMS_METADATA.CLOSE(h);
END;
/

実務パターン集

パターン 1：移行スクリプト自動生成（スキーマ全体）

-- すべての種別を含む移行スクリプトをコンソール出力
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',            FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',      TRUE);
END;
/

-- テーブル → インデックス → ビュー → シーケンス → プロシージャ の順で出力
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM (
  SELECT object_type, object_name, owner,
    CASE object_type
      WHEN 'TABLE'     THEN 1
      WHEN 'INDEX'     THEN 2
      WHEN 'VIEW'      THEN 3
      WHEN 'SEQUENCE'  THEN 4
      WHEN 'PROCEDURE' THEN 5
      WHEN 'FUNCTION'  THEN 6
      WHEN 'PACKAGE'      THEN 7
      WHEN 'PACKAGE_BODY'  THEN 8
      WHEN 'TRIGGER'       THEN 9
      ELSE 10
    END AS sort_order
  FROM dba_objects
  WHERE owner = 'HR'
    AND object_type IN ('TABLE','INDEX','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE','PACKAGE_BODY','TRIGGER')
    AND status = 'VALID'
)
ORDER BY sort_order, object_name;

パターン 2：特定テーブルの完全定義を一括出力（テーブル＋制約＋インデックス）

DECLARE
  PROCEDURE print_clob(p_clob CLOB) IS
    v_offset  INTEGER := 1;
    v_amount  INTEGER := 32767;
    v_buffer  VARCHAR2(32767);
  BEGIN
    LOOP
      EXIT WHEN v_offset > DBMS_LOB.GETLENGTH(p_clob);
      DBMS_LOB.READ(p_clob, v_amount, v_offset, v_buffer);
      DBMS_OUTPUT.PUT_LINE(v_buffer);
      v_offset := v_offset + v_amount;
    END LOOP;
  END;
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',            FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',      TRUE);

  -- テーブル本体
  print_clob(DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR'));
  -- インデックス
  print_clob(DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',         'EMPLOYEES', 'HR'));
  -- 制約
  print_clob(DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT',    'EMPLOYEES', 'HR'));
  -- 外部キー
  print_clob(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','EMPLOYEES', 'HR'));
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
END;
/

パターン 3：バージョン管理用に CONSTRAINTS_AS_ALTER で出力

-- 制約を ALTER TABLE 形式で分離出力（Git 管理に向く）
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',              FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES',   FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',          TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',        TRUE);
END;
/

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL;

よくあるエラーと対処法

必要な権限

よくある質問（FAQ）

まとめ

DBMS_METADATA を使いこなすことで、GUI ツールなしに SQL だけでスキーマ全体の DDL を抽出・整形できます。SET_TRANSFORM_PARAM で出力をシンプルに制御し、ループ処理で一括取得するパターンを覚えれば、データ移行・バージョン管理・環境差分確認のほぼすべてに対応できます。