ゴール
MySQL ではSHOW CREATE TABLE で DDL を復元できる。
Oracle 10g で同じことをやりたい。
DBMS_METADATA.GET_DDL 関数を使う
DBMS_METADATA パッケージの GET_DDL 関数を使うと、 DDL を復元できる。
GET_DDL
関数の定義は以下
DBMS_METADATA.GET_DDL ( object_type IN VARCHAR2, name IN VARCHAR2, schema IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;
マニュアルの GET_DDL の使用例にあるように、次の SQL で DDL を復元できる
SELECT dbms_metadata.get_ddl('TABLE',u.table_name) FROM user_all_tables u WHERE u.nested='NO' AND u.iot_type is null or u.iot_type='IOT';
The Evolution of DBMS_METADATA.GET_DDL
実際に get_ddl を出力し、素の表示では DDL を再利用しにくいので、
テーブルを作成
3種類のテーブルを用意
- 一時テーブル
- テーブル
- 外部キーを使ったテーブル
CREATE GLOBAL TEMPORARY TABLE temp_t ( id number(10,0) ) ON COMMIT DELETE ROWS; CREATE TABLE t ( id number(10,0) PRIMARY KEY ); CREATE TABLE foreign_t ( t_id number(10,0) ); ALTER TABLE foreign_t ADD CONSTRAINT "fk_t" FOREIGN KEY (t_id) REFERENCES t(id);
version 1 : デフォルトの表示
デフォルトの SELECT
結果は以下
DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) -------------------------------------------------------------------------------- CREATE GLOBAL TEMPORARY TABLE "TEST"."TEMP_T" ( "ID" NUMBER(10,0) CREATE TABLE "TEST"."T" ( "ID" NUMBER(10,0), PRIMARY KEY ("ID") DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) -------------------------------------------------------------------------------- CREATE TABLE "TEST"."FOREIGN_T" ( "T_ID" NUMBER(10,0), CONSTRAINT
version 2 : set pagesize
14 行ごとにページングが発生するのは邪魔なので、これを抑制する
set pagesize 0
とすると、ページングが無効化される。
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2699247
CREATE GLOBAL TEMPORARY TABLE "TEST"."TEMP_T" ( "ID" NUMBER(10,0) CREATE TABLE "TEST"."T" ( "ID" NUMBER(10,0), PRIMARY KEY ("ID") CREATE TABLE "TEST"."FOREIGN_T" ( "T_ID" NUMBER(10,0), CONSTRAINT
version 3 : set long
テーブルごとに80バイトで切れてしまっている。
GET_DDL のような CLOB 型は SET LONG で指定したバイト数までしか取得できない。
set long 20000
とデフォルトの80より大きい値を指定。
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2699121
CREATE GLOBAL TEMPORARY TABLE "TEST"."TEMP_T" ( "ID" NUMBER(10,0) ) ON COMMIT DELETE ROWS CREATE TABLE "TEST"."T" ( "ID" NUMBER(10,0), PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXE XTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DE FAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGIN G STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO L DEFAULT) TABLESPACE "USERS" CREATE TABLE "TEST"."FOREIGN_T" ( "T_ID" NUMBER(10,0), CONSTRAINT "fk_t" FOREIGN KEY ("T_ID") REFERENCES "TEST"."T" ("ID") ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMP RESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147 483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
version 4 : set longchunksize
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXE XTENTS 2147483645
というように、文字列の途中で改行されてしまっている。
CLOB 型は longchunksize
に応じてラップされるのが原因。
set longchunksize 20000
とデフォルトの80より大きい値を指定。
CREATE GLOBAL TEMPORARY TABLE "TEST"."TEMP_T" ( "ID" NUMBER(10,0) ) ON COMMIT DELETE ROWS CREATE TABLE "TEST"."T" ( "ID" NUMBER(10,0), PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" CREATE TABLE "TEST"."FOREIGN_T" ( "T_ID" NUMBER(10,0), CONSTRAINT "fk_t" FOREIGN KEY ("T_ID") REFERENCES "TEST"."T" ("ID") ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
version 5 : SESSION_TRANSFORM オプション
SESSION_TRANSFORM
のオプションを指定すると、 DDL として出力される内容を細かく制御できる。
詳細はマニュアルを参照
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm
○storage
のセクションを省く
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
のような STORAGE 句はいらない場合、以下を実行
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
○外部キーは alter table にする
プライマリーキーや外部キー制約などの制約は ALTER TABLE にしたい場合、以下を実行
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
○セグメント属性を省く
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
のようなセグメント属性はいらない場合、以下を実行
execute dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false );
○DDL 単位でセミコロンをつける
複数の DDL をまとめて流したい場合、DDL ごとに ; がないとシンタックスエラーになる。
DDL 単位でセミコロンを含めたい場合、以下を実行
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
最終形
set pagesize 0 set long 20000 longchunksize 20000 set linesize 1000 execute dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true); execute dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false ); execute dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false ); execute dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true ); spool /tmp/foo.sql SELECT dbms_metadata.get_ddl('TABLE',u.table_name) FROM user_all_tables u WHERE u.nested='NO' AND u.iot_type is null or u.iot_type='IOT'; spool off
出力結果
CREATE GLOBAL TEMPORARY TABLE "TEST"."TEMP_T" ( "ID" NUMBER(10,0) ) ON COMMIT DELETE ROWS ; CREATE TABLE "TEST"."T" ( "ID" NUMBER(10,0) ) ; ALTER TABLE "TEST"."T" ADD PRIMARY KEY ("ID") ENABLE; CREATE TABLE "TEST"."FOREIGN_T" ( "T_ID" NUMBER(10,0) ) ; ALTER TABLE "TEST"."FOREIGN_T" ADD CONSTRAINT "fk_t" FOREIGN KEY ("T_ID") REFERENCES "TEST"."T" ("ID") ENABLE;