SQL*Plusを使いパラメータで指定するテーブルのDDLをファイル出力します。手順は簡単コピペだけの２ステップです。（同時にインデックス、コメントの出力も可能なので便利）
手順１ SQLファイル（get_table_ddl.sql）を作成する。
まずは、適当なフォルダにget_table_ddl.sqlという名前のSQLファイルを作成します。記載する内容は以下の通り。(有効行は全てASCIIコードのはずなのでShift_JIS、utf-8どちらで作成しても動作するはずですが基本はクライアント環境にあわせてSQLファイルを作成してください）
SET LIN 20000
-- １行に収まらない内場合改行して表示
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
;
-- TABLEの取得（テーブル、カラム何れにもコメントが設定されていない場合
-- エラーになるのでis not 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文出力(2022/10/22追記)
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',t.TABLE_NAME,t.OWNER)
from (
select t.TABLE_NAME,t.OWNER
from dba_tab_privs t where t.OWNER = '&1'
and t.TABLE_NAME ='&2'
) t
;
spool off;
exit
手順２．実際にsqlplusを使いDDLを出力する。
SQL*plusをサイレントモード（-sオプション）で動作させ上記作成したSQLファイルを@で指定し、出力対象のテーブル名を指定します。
以下はSCOTTスキーマが持つテーブル名「PRODUCT_MST」のDDLを出力する例です。パラメータとして渡すスキーマ名、テーブル名は大文字／小文字意識するのでご利用の際はご注意ください
sqlplus -s system/pass@vm013/orau8 @get_table_ddl.sql SCOTT PRODUCT_MST
するとSQL*Plus実行フォルダに以下DDLファイルが作成されます。
TABLE_SCOTT.PRODUCT_MST.sql
以上で終了です。ここではテーブルのDDLと、インデックスのDDLまとめて出力していますが、それぞれ分けて出力したいとリクエストがあったので、テーブル用、インデックス用それぞれ分けてみました。よろしかったらどうぞ。
以下はそれぞれの環境での実行結果です
Linux環境
$ sqlplus -s system/pass@vm013/orau8 @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 UNIQUE INDEX "SCOTT"."UNI_KEY01" ON "SCOTT"."PRODUCT_MST" ("NAME")
;
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."CATEGORY" IS 'カテゴリー';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."CODE" IS '商品コード';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."NAME" IS '商品名';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."PRICE" IS '金額';
COMMENT ON TABLE "SCOTT"."PRODUCT_MST" IS '商品マスタ';
Windows環境（dos窓）
D:\work>sqlplus -s system/pass@vm013/orau8 @get_table_ddl.sql SCOTT PRODUCT_MST
D:\work>type 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 UNIQUE INDEX "SCOTT"."UNI_KEY01" ON "SCOTT"."PRODUCT_MST" ("NAME")
;
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."CATEGORY" IS 'カテゴリー';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."CODE" IS '商品コード';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."NAME" IS '商品名';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."PRICE" IS '金額';
COMMENT ON TABLE "SCOTT"."PRODUCT_MST" IS '商品マスタ';
D:\work>Windows環境（PowerShell）おまけ
PowerShellからsqlplusを実行する場合、外部ファイルの指定には以下の通りシングルクォーテーションで囲む必要があります。
PS D:\work> sqlplus -s system/pass@vm013/orau8 '@get_table_ddl.sql' SCOTT PRODUCT_MST
PS D:\work> Get-Content .\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 UNIQUE INDEX "SCOTT"."UNI_KEY01" ON "SCOTT"."PRODUCT_MST" ("NAME")
;
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."CATEGORY" IS 'カテゴリー';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."CODE" IS '商品コード';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."NAME" IS '商品名';
COMMENT ON COLUMN "SCOTT"."PRODUCT_MST"."PRICE" IS '金額';
COMMENT ON TABLE "SCOTT"."PRODUCT_MST" IS '商品マスタ';
PS D:\work>
