Oracle SQL get_ddl でオブジェクト定義を抽出する

「本番環境と同じテーブルをテスト環境に作りたいが、最新の定義書がない」「既存の複雑なビューのソースコードが分からず修正できない」と困っていませんか？Oracle get_ddl（DBMS_METADATAパッケージ）を使えば、コマンド一つで正確な定義を取得できます。本記事では、Oracle DDL 抽出を実機で成功させるための具体的な手順と、多様なオブジェクトの抽出例を分かりやすく解説します。

Oracleユーザー作成ガイド：CREATE USERの解説

Oracle Databaseにおけるユーザー管理は、セキュリティ・権限制御・リソース制限において非常に重要な役割を担います。本記事では、CREATE USER文の基本から、表領域・クォータ・権限付与・ロック制御に至るまで、実務に耐えうるレ…

oracle-master00000.com

2024.09.25

Oracle DBLINKについて

DBLINK（データベースリンク）は、あるOracleデータベースから別のOracleデータベースに接続し、データを参照したり操作を行うためのリンクです。特に、異なるシステムやデータベースを連携させる必要がある際に便利な機能で、データのリア…

oracle-master00000.com

2024.11.07

---

結論・最短手順（やることリスト）

定義情報を素早く、かつ整形された状態で取得するための最短ステップです。

1. 出力環境の整形: SET LONG 等で表示が切れないように設定する。
2. 実行権限の確認: 自身の所有以外を抽出する場合は SELECT_CATALOG_ROLE が必要。
3. 関数の実行: SELECT DBMS_METADATA.GET_DDL('種別', '名', 'スキーマ') FROM DUAL; を実行。
4. 書式の調整: 必要に応じて SET_TRANSFORM_PARAM で不要な句（STORAGE句など）を除外する。

---

背景と基礎：DBMS_METADATA.GET_DDL とは？

DBMS_METADATA.GET_DDL は、データベース内に格納されているオブジェクトのメタデータを、再利用可能な CREATE 文（DDL）として再構築して返す標準パッケージ関数です。

初心者向けメモ

GUIツールを使わなくても、SQL*Plusの画面上に「CREATE TABLE…」というソースコードを丸ごと表示させることができる、DBA必須のツールです。

---

手順・実装：抽出前の環境準備

SQL*Plus 等のツールで実行する場合、初期設定のままでは出力が途切れたり、読みづらかったりします。

1. 表示設定の変更（必須）

以下のコマンドを実行して、CLOB型の長い出力を最後まで表示できるようにします。

コマンド	役割
SET LONG 2000000	出力バッファを拡張（デフォルトでは短いため必須）
SET PAGESIZE 0	見出しやページ区切りを抑制する
SET LINESIZE 1000	1行の表示幅を広げて折り返しを防ぐ

2. 出力のカスタマイズ（任意）

「そのまま別の環境で実行したい」場合は、セミコロンの付与や物理属性の除外設定が便利です。

-- SQLの末尾に「;」を付与し、STORAGE句（物理サイズ指定）を除外する
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);

---

実行例：具体的な DDL 抽出パターン集

実機でそのまま使える抽出例です。引数のオブジェクト名は必ず ‘大文字’ で指定してください。

※前提：SCOTTスキーマ、または対象オブジェクトへの参照権限。

テーブル定義の抽出（基本）

-- SCOTTユーザーのEMPテーブルの定義を取得
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') FROM DUAL;

SQLの意図: テーブル構造、列定義、インライン制約を CREATE TABLE 形式で取得します。

SQL> SET LONG 2000000
SQL> SET LONGCHUNKSIZE 2000000
SQL> SET PAGESIZE 0
SQL> SET LINESIZE 1000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') FROM DUAL;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

ビュー定義の抽出

-- ビューのソースSQLを確認する
SELECT DBMS_METADATA.GET_DDL('VIEW', 'EMP_DETAILS_VIEW', 'HR') FROM DUAL;

SQLの意図: 複雑な結合を含むビューであっても、完全な SELECT 文を含む定義を復元します。

インデックス定義の抽出

-- 特定の索引の定義のみをピンポイントで取得
SELECT DBMS_METADATA.GET_DDL('INDEX', 'PK_EMP', 'SCOTT') FROM DUAL;

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'PK_EMP', 'SCOTT') FROM DUAL;

  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS" ;

PL/SQL（プロシージャ等）の抽出

-- プログラムソースをそのまま抽出
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'SEND_MAIL', 'APP_USER') FROM DUAL;

SQLの意図: CREATE OR REPLACE... から始まるソースコードを取得し、再コンパイル可能な状態で出力します。

ユーザー（スキーマ）と権限の抽出

-- ユーザー作成文を取得
SELECT DBMS_METADATA.GET_DDL('USER', 'SCOTT') FROM DUAL;

SQLの意図: ユーザーのデフォルト表領域やプロファイル設定などを抽出します。

SQL> SELECT DBMS_METADATA.GET_DDL('USER', 'SCOTT') FROM DUAL;

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:2D2C45F4C113921FBC7D586A7F3C6835EE63D8BF4F8C0B1A76D68393CCD0;T:181BFCD60730CEF4264B0F20A77C354DB3FD42A346B8DE8F414EC75399B4BA96AC97A858E0104312AA1A2111A0B859BE2942FED59C477F25F621BAB7DAC54CFE039CD1B14F89FA6C52D591F2F2C2458E'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

データベース・リンクの抽出

-- 外部DBへの接続定義を確認
SELECT DBMS_METADATA.GET_DDL('DB_LINK', 'MY_REMOTE_LINK', 'SCOTT') FROM DUAL;

---

トラブルシューティング：ORAエラーへの対処

Oracle DDL 抽出でよく発生するトラブルの解決策です。

エラーコード	原因	対処法
ORA-31603	オブジェクトが見つからない	オブジェクト名やスキーマ名を ‘大文字’ で入力しているか確認してください。
ORA-31600	無効な入力値	第1引数（’TABLE’など）の綴りが正しいか確認してください。
出力が途中で切れる	表示バッファ不足	SET LONG 2000000 を実行してから再試行してください。

---

運用・監視・セキュリティ上の注意

• 権限管理: 他者のスキーマ情報を取得するには SELECT_CATALOG_ROLE が必要です。セキュリティポリシーに従い、必要な期間だけ付与してください。
• 移行時の注意点: 抽出した DDL に TABLESPACE 句が含まれている場合、移行先に同名の表領域がないとエラーになります。必要に応じて SET_TRANSFORM_PARAM で TABLESPACE を false に設定して抽出してください。
• パスワード情報: DBリンクやユーザー定義を抽出しても、パスワードはハッシュ化（または伏せ字）されるため、完全な移行には別途パスワードの再設定が必要です。

---

FAQ：よくある質問

Q: シノニムの定義も取れますか？

A: はい。種別に 'SYNONYM' を指定してください。パブリック・シノニムの場合はスキーマ名に NULL を指定します。

Q: 全テーブルをまとめて取得したいのですが。

A: USER_TABLES 等と組み合わせてループさせるか、以下のように一括実行します。

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

Q: 実行時にエラーが出ないが、何も表示されません。

A: SET LONG が設定されていないか、非常に小さい値になっている可能性があります。

---

まとめ

• DBMS_METADATA.GET_DDL は、あらゆるデータベースオブジェクトの「設計図」を復元できる。
• 抽出前には必ず SET LONG を設定して欠損を防ぐ。
• 引数は シングルクォーテーションで囲み、大文字 で指定する。
• SET_TRANSFORM_PARAM を活用すれば、他環境への移行用SQLも簡単に整形できる。

本記事は Oracle Database 19c を対象に解説します（他バージョンは画面や既定値が異なる場合があります）。

[参考]
DBMS_METADATA

CREATE TABLE AS SELECT(CTAS)で表を作成する方法

こんにちは！今回は、SQLの中でも非常に便利なCREATE TABLE AS SELECT（略してCTAS）の使い方について解説します。この方法を使えば、既存のデータを基に新しい表を簡単に作成できます。「CTASって何？」という方も、「どう…

oracle-master00000.com

2025.01.29