Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

DBMS_METADATA による定義文の取り出し

DBMS_METADATA は テーブル定義などを DDL として取り出すためにオラクルに組み込まれているパッケージ。 データ・ディクショナリからテーブルや索引、制約などを DDL 文として取り出すことができる。

テーブル定義と関連インデックスと各種属性の DDL を取得する例

指定したテーブルにインデックスが存在しないテーブルの場合エラーがでます。 利用頻度が高い場合にはカーソルを使って使い勝手を改善するなどしてください。
参考 ⇒ カーソル・ループ処理

テーブル以外の スキーマ も同じように取得することが可能

取得できるスキーマの種類

TABLE、INDEX、PACKAGE、PACKAGE_BODY、TRIGGER、TYPE、VIEW、OUTLINE など

SQLPlus で実行

SET TRIMS ON
SET LONG 1000000
SET PAGESIZE 0
BEGIN
	-- 動作オプションの設定
	-- TERMINATOR 処理
	DBMS_METADATA.SET_TRANSFORM_PARAM(
		TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM,
		NAME => 'SQLTERMINATOR', VALUE => TRUE);
	-- SEGMENT を有効にする
	DBMS_METADATA.SET_TRANSFORM_PARAM(
		TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM,
		NAME => 'SEGMENT_ATTRIBUTES', VALUE => TRUE);
	-- SEGMENT 子属性を有効にする
		DBMS_METADATA.SET_TRANSFORM_PARAM(
			TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM,
			NAME => 'STORAGE', VALUE => TRUE);
		DBMS_METADATA.SET_TRANSFORM_PARAM(
			TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM,
			NAME => 'TABLESPACE', VALUE => TRUE);
	-- CONSTRAINTS を有効にする
	DBMS_METADATA.SET_TRANSFORM_PARAM(
		TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM,
		NAME => 'CONSTRAINTS', VALUE => TRUE);
	-- CONSTRAINTS 子属性を有効にする
		DBMS_METADATA.SET_TRANSFORM_PARAM(
			TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM,
			NAME => 'CONSTRAINTS_AS_ALTER', VALUE => TRUE);
	-- 動作オプション設定 終了
END;
/
SELECT 
	DBMS_METADATA.GET_DDL('TABLE', USER_TABLES.TABLE_NAME )
	,DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', USER_TABLES.TABLE_NAME )
FROM USER_TABLES
WHERE
     USER_TABLES.TABLE_NAME IN ('TABLE_NAME1', 'TABLE_NAME2', '...') ;
                                 -- ↑ 必ず大文字にする

パッケージとパッケージボディのソースコードの取り出し

パッケージ名： DBMS_MY_PACKAGE

SET TRIMS ON
SET LONG 1000000
SET PAGESIZE 0
SELECT
      DBMS_METADATA.GET_DDL('PACKAGE', 'DBMS_MY_PACKAGE' )
      ,DBMS_METADATA.GET_DDL('PACKAGE_BODY', 'DBMS_MY_PACKAGE')
FROM DUAL;

JOB / SCHEDULER 情報の取り出し

JOB 名： MY_JOB_NAME

SET TRIMS ON
SET LONG 1000000
SET PAGESIZE 0
SELECT
      DBMS_METADATA.GET_DDL('PROCOBJ', 'MY_JOB_NAME' )
FROM DUAL;

---

テーブル定義の関連事項

• テーブルの定義、テーブルの削除
• SQL の種類(データ定義言語、データ操作言語、データ制御言語)
• エクスポート・インポート ユーティリティ
• テーブル一覧、インデックス一覧、ビューの定義の一覧