【ORACLE】DDLの取得
テーブル・マテビュー・INDEXのDDLを取得して、テーブル（マテビュー）別にファイルに吐きだすスクリプト。
DBMS_METADATA.GET_DDL、DBMS_METADATA.GET_DEPENDENT_DDLをカーソルと併用して使用し、
オブジェクト毎にDDLを取得しつつ、ファイルへの出力はUTL_FILEパッケージで行う。
declare
cursor CUR_TARGET_TABLES is
select tab_name
from (
-- ↓ここに調査対象となるテーブルを並べる
-- 全テーブルならUSER_TABLESで良い
select 'TABLE_NAME1' tab_name from dual
union all
select 'TABLE_NAME2' tab_name from dual
);
REC_TARGET_TABLES CUR_TARGET_TABLES%ROWTYPE;
cursor CUR_TARGET_OBJ_INDEXES(target_tab_name REC_TARGET_TABLES.TAB_NAME%TYPE) is
select ui.index_name
from user_indexes ui
where ui.table_name = target_tab_name
and not exists (
select 1
from USER_CONSTRAINTS uc
where uc.TABLE_NAME = target_tab_name
and uc.CONSTRAINT_TYPE = 'P'
and uc.CONSTRAINT_NAME = ui.INDEX_NAME
)
;
REC_TARGET_OBJ_INDEXES CUR_TARGET_OBJ_INDEXES%ROWTYPE;
NMB_CHECK_TAB number;
NMB_CHECK_MVW number;
NMB_CHECK_IDX number;
NMB_CHECK_COM number;
CHR_TARGET_OBJ_TYPE varchar2(30);
CHR_TARGET_OBJ_DDL_FILENAME varchar2(50);
TYP_FILE_HUNDLE UTL_FILE.FILE_TYPE;
CHR_FILE_PATH varchar2(100) := 'REEEEEEEEEMAN_TEST_DIR';
CHR_DDL varchar2(32767);
CHR_IDX_DDL varchar2(32767);
CHR_COM_DDL varchar2(32767);
begin
-- セミコロン出力を有効にする
DBMS_METADATA.SET_TRANSFORM_PARAM(
TRANSFORM_HANDLE => DBMS_METADATA.SESSION_TRANSFORM,
NAME => 'SQLTERMINATOR', VALUE => TRUE);
open CUR_TARGET_TABLES;
loop
-- 変数初期化
NMB_CHECK_TAB := 0;
NMB_CHECK_MVW := 0;
NMB_CHECK_IDX := 0;
NMB_CHECK_COM := 0;
CHR_TARGET_OBJ_TYPE := '';
CHR_DDL := '';
CHR_IDX_DDL := '';
CHR_COM_DDL := '';
fetch CUR_TARGET_TABLES into REC_TARGET_TABLES;
exit when CUR_TARGET_TABLES%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE('TABLE_NAME=' || REC_TARGET_TABLES.TAB_NAME);
-- テーブルとして存在するかチェック
select count(1) cnt
into NMB_CHECK_TAB
from user_tables
where table_name = REC_TARGET_TABLES.TAB_NAME;
if NMB_CHECK_TAB > 0 then
-- マテビューとして存在するかチェック
select count(1) cnt
into NMB_CHECK_MVW
from user_mviews
where MVIEW_NAME = REC_TARGET_TABLES.TAB_NAME;
if NMB_CHECK_MVW = 0 then
-- テーブルとして定義を取得
CHR_TARGET_OBJ_TYPE := 'TABLE';
-- ファイル名を生成
CHR_TARGET_OBJ_DDL_FILENAME := 'DDL_TAB_' || REC_TARGET_TABLES.TAB_NAME || '.sql';
else
-- マテビューとして定義を取得
CHR_TARGET_OBJ_TYPE := 'MATERIALIZED_VIEW';
-- ファイル名を生成
CHR_TARGET_OBJ_DDL_FILENAME := 'DDL_MVW_' || REC_TARGET_TABLES.TAB_NAME || '.sql';
end if;
-- DDL取得（テーブルorマテビュー）
select DBMS_METADATA.GET_DDL(CHR_TARGET_OBJ_TYPE,REC_TARGET_TABLES.TAB_NAME)
into CHR_DDL
from dual;
CHR_DDL := replace(CHR_DDL,'"','');
-- DDL書き出し（テーブルorマテビュー）
TYP_FILE_HUNDLE := UTL_FILE.FOPEN(CHR_FILE_PATH,CHR_TARGET_OBJ_DDL_FILENAME,'W',32767);
UTL_FILE.PUT_LINE(TYP_FILE_HUNDLE,CHR_DDL);
UTL_FILE.NEW_LINE(TYP_FILE_HUNDLE,1);
-- 対象オブジェクトのインデックス数を取得
select count(1) cnt into NMB_CHECK_IDX
from user_indexes
where table_name = REC_TARGET_TABLES.TAB_NAME;
-- インデックスがあればそのDDLも取得して書き出し
if NMB_CHECK_IDX > 0 then
open CUR_TARGET_OBJ_INDEXES(REC_TARGET_TABLES.TAB_NAME);
loop
fetch CUR_TARGET_OBJ_INDEXES into REC_TARGET_OBJ_INDEXES;
exit when CUR_TARGET_OBJ_INDEXES%NOTFOUND;
-- DDL取得（インデックス）
select DBMS_METADATA.GET_DDL('INDEX',REC_TARGET_OBJ_INDEXES.INDEX_NAME)
into CHR_IDX_DDL
from dual;
CHR_IDX_DDL := replace(CHR_IDX_DDL,'"','');
-- DDL書き出し（インデックス）
UTL_FILE.PUT_LINE(TYP_FILE_HUNDLE,CHR_IDX_DDL);
UTL_FILE.NEW_LINE(TYP_FILE_HUNDLE,1);
end loop;
close CUR_TARGET_OBJ_INDEXES;
end if;
-- コメント数をチェック
select sum(cnt) into NMB_CHECK_COM
from (
select 'TAB_COMMENTS' as KEY ,count(1) cnt
from user_tab_comments
where table_name = REC_TARGET_TABLES.TAB_NAME
union all
select 'COL_COMMENTS' as KEY ,count(1) cnt
from user_col_comments
where table_name = REC_TARGET_TABLES.TAB_NAME
);
if NMB_CHECK_COM > 0 then
-- DDL取得
select DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT',REC_TARGET_TABLES.TAB_NAME)
into CHR_COM_DDL
from dual;
CHR_COM_DDL := replace(CHR_COM_DDL,'"','');
-- DDL書き出し（コメント）
UTL_FILE.PUT_LINE(TYP_FILE_HUNDLE,CHR_COM_DDL);
UTL_FILE.NEW_LINE(TYP_FILE_HUNDLE,1);
end if;
UTL_FILE.FCLOSE(TYP_FILE_HUNDLE);
end if;
end loop;
close CUR_TARGET_TABLES;
EXCEPTION WHEN OTHERS THEN
UTL_FILE.FCLOSE_ALL;
RAISE;
end;
/
↑のコードを貼りつけてsqlplus等のツールで実行するだけで良い。
パーティションテーブル、パーティションINDEXにも対応。
出力はテーブルもしくはマテビューごとに別のファイルに出力されて、
そのテーブルやマテビューにインデックスやコメントがあればそれも一緒に出力される。
以下、注釈とか。
■事前準備
このスクリプトではUTL_FILEパッケージを使ってDDLを出力するので、
事前にディレクトリオブジェクトが必要になる。
↑のコーディングではdeclare部の変数「CHR_FILE_PATH」にそのディレクトリオブジェクト名を定義している
（例のためそのディレクトリオブジェクト名は"REEEEEEEEEMAN_TEST_DIR"）
当然ながらサーバ上でディレクトリオブジェクトが指すパス位置に実際にディレクトリが存在することが前提になる。
また、実行するOracleのユーザーに対するディレクトリオブジェクトへの書き込み権限付与と、
同様にサーバ上の実際のディレクトリに適切な権限付与をしておくことも事前準備として必要になる。
◆ディレクトリオブジェクトの作成例
create or replace directory REEEEEEEEEMAN_TEST_DIR as '/home/oracle/reeeeeeeeeman_test';
◆ディレクトリオブジェクトへの権限付与（sysdbaにて、このスクリプトを実行するOracleのユーザーがSCOTTである場合）
grant write on directory REEEEEEEEEMAN_TEST_DIR to SCOTT;
◆サーバ上のディレクトリへの権限付与
$ chmod -R 777 /home/oracle/reeeeeeeeeman_test
■対象テーブル（マテビュー）の指定について
DDL取得対象のオブジェクトはカーソル定義「CUR_TARGET_TABLES」に直書きするつくりになっている。
全テーブル取得するならここのfrom句を「USER_TABLES」に変更すれば良い。
特定の１テーブルに絞りたいなら「USER_TABLES」に加えてWHERE句を付け、
かつそのテーブル名部分をバインド変数化してしまうと汎用的になるだろう。
cursor CUR_TARGET_TABLES is
select table_name as tab_name
from user_tables
where table_name := :target_tab_name
↑みたいな。
ただしこの場合sqlplusで実行する際は事前に以下のようにバインド変数定義が必要になる。
SQL> variable target_tab_name nvarchar2(20); SQL> execute :target_tab_name := 'TEST_TABLE';
■テーブルとマテビューとの切り分けについて
USER_TABLESにはマテビューも含まれるので、
カーソルから取得した後USER_MVIEWSと照合してテーブルかマテビューかの切り分けを行っている。
テーブルと判断されたら「DDL_TAB」マテビューと判断されたら「DDL_MVW」が、
出力後のファイル名の先頭にそれぞれ付与される形になる。
■その他実装面での工夫
DBMS_METADATA.GET_DDL、DBMS_METADATA.GET_DEPENDENT_DDLは、
指定したオブジェクトが存在しない場合エラーになる。
たとえばインデックスが一本もついていないテーブルTEST_TABLE_NO_IDXに対して
DBMS_METADATA.GET_DDL('INDEX','TEST_TABLE_NO_IDX')を実行するとエラーになる。
同様にコメントがまったくついていないテーブルTEST_TABLE_NO_COMMENTに対して
DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT','TEST_TABLE_NO_COMMENT')を実行するとエラーになる。
なので、インデックスの場合はUSER_INDEXES、コメントの場合はUSER_TAB_COMMENTSとUSER_COL_COMMENTSを参照し、
そのテーブルないしマテビューにインデックスやコメントが付与されているかを調べてからDDL取得に移る。
■心残り
DBMS_METADATA.GET_DDLで取得したCLOBにはダブクォ（"）
が含まれる。
これが嫌だったので、除去するために無理矢理replaceを挟み込んでいる。
よって、物理名ならともかく、コメント等の論理名にダブクォを含む場合、これも除去されてファイルに出力される。
DBMS_METADATA.SET_TRANSFORM_PARAMあたりで指定できるのかといろいろ探したが見つからなかったので、
もういいやと思ってあきらめた。
■愚痴
他のサイトでも書かれてたけどUTL_FILEパッケージの公式の説明わかりづらいよな…
