oracle

Oracle DDL を取得する(DBMS_METADATA.GET_DDL でテーブル・インデックス・コメントをファイル出力)

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 の名前内容
SQLTERMINATORTRUE で、DDL の終わりにセミコロンを付ける
CONSTRAINTS_AS_ALTERTRUE で、主キーなどの制約を CREATE TABLE の中ではなく、ALTER TABLE 文で出力する
SEGMENT_ATTRIBUTESFALSE で、PCTFREE などの記憶域の設定を省く
TABLESPACEFALSE で、表領域の指定を省く
EMIT_SCHEMAFALSE で、スキーマ名(”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…
スポンサーリンク
タイトルとURLをコピーしました