Oracle でテーブルの DDL(CREATE TABLE 文)を取得するには、DBMS_METADATA.GET_DDL 関数を使います。結果は CLOB なので、SQL*Plus では SET LONG と SET LONGCHUNKSIZE を大きくしておかないと、途中で切れたり改行されたりします。この記事の後半では、インデックス・コメント・GRANT 文まで、まとめてファイルに出力するスクリプトを紹介します。
DBMS_METADATA.GET_DDL でテーブルの DDL を取得する
引数は、オブジェクトの種類(TABLE・INDEX・VIEW など)、名前、スキーマ名の順です。
SQL> set long 100000 longchunksize 100000 pagesize 0 linesize 200
SQL> select dbms_metadata.get_ddl('TABLE', 'PRODUCT_MST', 'SCOTT') from dual;
CREATE TABLE "SCOTT"."PRODUCT_MST"
( "CATEGORY" VARCHAR2(20),
"CODE" VARCHAR2(10),
"NAME" VARCHAR2(30),
"PRICE" NUMBER,
CONSTRAINT "PK_PRODUCT_MST" PRIMARY KEY ("CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
SQL>
そのままだと、PCTFREE や TABLESPACE などの記憶域の設定まで出力されます。別の環境で使う DDL にしたいときは、下のスクリプトのように SET_TRANSFORM_PARAM で省きます。
インデックス・コメント・GRANT までファイルに出力するスクリプト
手順 1:SQL ファイル(get_table_ddl.sql)を作る
次の内容で、get_table_ddl.sql という名前のファイルを作ります。
SET LIN 20000
-- 1行に収まらない場合は改行して表示
SET WRAP ON
SET PAGESIZE 0
SET LONG 400000
SET LONGCHUNKSIZE 400000
-- 問い合わせの結果レコード件数表示制御
SET FEEDBACK OFF
-- コマンド表示制御
SET ECHO OFF
-- コマンド結果表示制御
SET TERMOUT OFF
-- 行末の空白除去
SET TRIMS ON
-- バインド変数新旧表示除去
SET VERIFY OFF
--DDLの終わりにセミコロン
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );
-- PK情報はalter tableで表現
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );
-- STORAGE属性を省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );
-- --セグメント属性省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
--
-- デフォルトはTRUEなので何も指定しなければ出力される
-- セグメント属性をFALSEにした場合「表領域出力も連動してFALSE」の動作となるので注意が必要。
-- 表領域を出力(TRUE)/省略(FALSE)
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE );
--
-- --パーティション省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',FALSE);
--
-- スキーマ名省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', FALSE);
--出力ファイル名変数定義
-- 『@』はスプールファイル名に使えないようなので、『_』に置き換える 2021/06/04
col spoolout_file_name new_value spool_file_name format a50
select replace('./TABLE_'||'&1'||'.'||'&2'||'.sql','@','_') spoolout_file_name
from dual;
spool &spool_file_name
-- TABLE用DDLの取得
select dbms_metadata.get_ddl('TABLE',t.TABLE_NAME,owner) from dba_tables t
where owner= '&1' and t.TABLE_NAME = '&2'
;
-- INDEXの取得(PKはcreate tableに含めているので外す)
select dbms_metadata.get_ddl('INDEX',t.INDEX_NAME,t.OWNER )
from dba_indexes t
left outer join DBA_CONSTRAINTS c
on t.OWNER =c.INDEX_OWNER and t.INDEX_NAME = c.INDEX_NAME
where t.OWNER= '&1' and t.TABLE_NAME ='&2'
and c.CONSTRAINT_TYPE is null
;
-- コメントの取得(テーブルにもカラムにもコメントがないと
-- エラーになるので、コメントがあるときだけ取得する)
select DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT',t.TABLE_NAME,t.OWNER)
from (
select c.OWNER,c.TABLE_NAME
from all_col_comments c
where c.OWNER = '&1'
and c.TABLE_NAME = '&2'
and c.COMMENTS is not null
group by c.OWNER,c.TABLE_NAME
union
select b.OWNER,b.TABLE_NAME from all_tab_comments b
where b.OWNER = '&1'
and b.TABLE_NAME = '&2'
and b.COMMENTS is not null
) t
;
-- GRANT文の取得(権限が付いているときだけ取得する)
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME,t.OWNER)
from (
select distinct t.TABLE_NAME,t.OWNER
from dba_tab_privs t where t.OWNER = '&1'
and t.TABLE_NAME ='&2'
) t
;
spool off;
exit
手順 2:SQL*Plus で実行する
SQL*Plus をサイレントモード(-s)で起動し、作った SQL ファイルを @ で指定して、スキーマ名とテーブル名を渡します。実行したフォルダに、TABLE_スキーマ名.テーブル名.sql というファイルができます。
$ sqlplus -s system/パスワード@接続先 @get_table_ddl.sql SCOTT PRODUCT_MST
$ cat TABLE_SCOTT.PRODUCT_MST.sql
CREATE TABLE "SCOTT"."PRODUCT_MST"
( "CATEGORY" VARCHAR2(20),
"CODE" VARCHAR2(10),
"NAME" VARCHAR2(30),
"PRICE" NUMBER
) ;
ALTER TABLE "SCOTT"."PRODUCT_MST" ADD CONSTRAINT "PK_PRODUCT_MST" PRIMARY KEY ("CODE")
USING INDEX ENABLE;
CREATE INDEX "SCOTT"."IDX_CAT" ON "SCOTT"."PRODUCT_MST" ("CATEGORY")
;
CREATE UNIQUE INDEX "SCOTT"."UNI_KEY01" ON "SCOTT"."PRODUCT_MST" ("NAME")
;
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."CODE" IS '商品コード';
COMMENT ON TABLE "SCOTT"."PRODUCT_MST" IS '商品マスタ';
GRANT SELECT ON "SCOTT"."PRODUCT_MST" TO "U1";
GRANT UPDATE ON "SCOTT"."PRODUCT_MST" TO "U1";
GRANT SELECT ON "SCOTT"."PRODUCT_MST" TO "U2";
PowerShell から実行するときは、'@get_table_ddl.sql' のようにシングルクォーテーションで囲みます(囲まないと、@ が PowerShell の記号として扱われます)。
スクリプトで使っているオプション
| SET_TRANSFORM_PARAM の名前 | 内容 |
|---|---|
| SQLTERMINATOR | TRUE で、DDL の終わりにセミコロンを付ける |
| CONSTRAINTS_AS_ALTER | TRUE で、主キーなどの制約を CREATE TABLE の中ではなく、ALTER TABLE 文で出力する |
| SEGMENT_ATTRIBUTES | FALSE で、PCTFREE などの記憶域の設定を省く |
| TABLESPACE | FALSE で、表領域の指定を省く |
| EMIT_SCHEMA | FALSE で、スキーマ名(”SCOTT”.)を省く(スクリプトではコメントにしている) |
うまく取得できないとき
| 症状 | 原因と対処 |
|---|---|
| ORA-31603(オブジェクトが見つかりません) | テーブル名・スキーマ名は大文字で指定する。または、ほかのスキーマのテーブルを取得する権限がない |
| ほかのスキーマのテーブルで ORA-31603 | そのテーブルの SELECT 権限があっても取得できない。SELECT_CATALOG_ROLE ロールが必要 |
| DDL が途中で切れる・変な位置で改行される | SET LONG と SET LONGCHUNKSIZE を大きくする |
-- テーブル名を小文字で書くと見つからない
SQL> select dbms_metadata.get_ddl('TABLE', 'product_mst', 'SCOTT') from dual;
ERROR:
ORA-31603: オブジェクト"product_mst"(タイプTABLE)がスキーマ"SCOTT"で見つかりません。
(以下省略)
SQL>
例は Oracle AI Database 26ai Free(バージョン 23.26)で実行しています。インデックスだけを別のファイルに出力したいときは「Oracle INDEXのDDLをファイル出力する」のスクリプトも使えます。
ストアドプログラム(パッケージ)の DDL を取得する

ストアドパッケージをファイル出力する(仕様部、本体部を別々に出力できるので便利!)
以下の手順に従うと、ストアドパッケージをヘッダー用ファイルと、ボディー用ファイルを一気に出力する事ができます。個人的にも基本こちらの方法で出力したソースファイルをgitで管理するようにしています。今回はSQLファイルを利用していますが、SQ…
トリガーの DDL を取得する

Oracle トリガー作成DDLを取得
Oracleのトリガーのソースを取得以下手順にてトリガーのソース(DDL)をファイルに取得する事ができます。手順1 SQLファイル(get_torigger_ddl.sql)を作成する。まずは、適当なフォルダにget_torigger_dd…
