山下寛人オフィシャルブログ

オイシックス株式会社 執行役員 システム本部長 山下寛人の公式ブログです。

Oracleで既存テーブルのDDLを取得する方法

Oracleで既存テーブルのDDLを取得するには

いろいろな方法があります。

しかし意外とやりたいことをするには工夫が

必要な場合があります。

今回は自動テスト用に本番と同じテーブルを

空の状態で作ろうとしたら単純にはできませんでした。


まず最初にやろうとしたのがexp/imp。

expコマンドでrowsというオプションがあるので

exp rows=n

とすればデータなしでテーブルのみ作成できます。

さてこれでやろうとしたら、テストサーバーで

impできません。

storage句がついてしまっているので本番の

巨大なデータ量のサイズでテーブルを作ろうとして

領域不足になったせいでした。


ではstorage句なしにするにはどうしたらよいか。

コマンドのオプションではできません。


impするときのオプションにshow=yというものが

あります。これを使うとDDLが表示されます。

ところがこのDDLが変なところで改行されているし

行の先頭と最後にダブルコーテーションがついて

いるし、storage句は手動で取らないといけないので

かなりめんどくさいです。

250くらいテーブルがありますので。

そこで他の方法を探ります。


OEMを使ってDDLを取得できます。

しかしこれもstorage句がついているし

1つ1つクリックしないといけません。

SQL DeveloperでもDDLを取得できます。

しかしこれもOEMと同じ問題があります。


DBMS_METADATA.GET_DDLでもDDLを取得できます。

こちらはオプションでstorage句なしなどを

指定することができました。

execute DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false
);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false
);

さらにSQLでテーブル名を列挙することで一気にDDL

取得できます。

select
dbms_metadata.get_ddl('TABLE',t.table_name)||';'
from user_tables t
where t.table_name in (
'テーブル名',
'テーブル名',・・・);

これでほぼ思ったとおりのcreate tableが作成できました。


さて、テーブルはできましたがインデックスもほしい

ところです。

DBMS_METADATA.GET_DDLで同じようにインデックスの

DDLも取得できます。

select
dbms_metadata.get_ddl('INDEX',t.index_name)||';'
from user_indexes t where not exists (select c.constraint_name from user_constraints c where c.constraint_name=t.index_name) and index_type<>'LOB'
and t.table_name in (
'テーブル名',
'テーブル名',・・・);

user_constraintsと突き合わせているのは、

主キーはcreate tableのほうで作成されるので、

それを除くためです。

単純にuser_indexesだけだと主キーのインデックスまで

出てきてしまいます。


これで完了です。