いきさつ
DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。
準備するもの
まず、以下のスクリプトをコピーして、"tableexport.sql"というファイルを作成します。
このとき、以下の部分をメンテしてください。
| メンテ箇所 |
| in ('TABLE1','TABLE2'..) |
出力対象テーブル |
------------------------------------------------------------------
-- Copyright(c) 2015-2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.09.30 作成
-- 2016.02.26 マテリアライズド・ビューを除外
-- 2016.07.19 マテリアライズド・ビュー・ログを除外,バグ改修等
--
-- [実行方法]
-- 1)このテキストを、"tableexport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @tableexport.sql
-- 3)同一フォルダ上に、tableimport.sqlが作成されます。テーブルを生成したい場合、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @tableimport.sql
-- 4)コンパイル結果がtableimport.logとして出力されます。
--
-- [注意事項]
-- ストレージおよび複雑な指定(パーティション索引や参照整合性制約等)には未対応
------------------------------------------------------------------
set escape ?
set linesize 300
set pagesize 0
set term off
set head off
set feed off
set trim on
set trimspool on
set verify off
COLUMN CMDLINE FORMAT A300
COLUMN COL_SEP FORMAT A4
COLUMN COLUMN_NAME FORMAT A32
COLUMN DATA_TYPE FORMAT A14
COLUMN DATA_LEN FORMAT A12
COLUMN DATA_DEFAULT_T FORMAT A7
COLUMN NULLABLE FORMAT A8
COLUMN DESCEND FORMAT A4
COLUMN COMMENTS FORMAT A150
------------------------------------------------------------------
-- __tableexport1.sql(TABLE DDL)
------------------------------------------------------------------
spool __tableexport1.sql
prompt SET LONG 30
prompt COLUMN DATA_DEFAULT FORMAT A30
prompt SPOOL TABLE/?&1..sql
prompt prompt --DROP TABLE "?&?&1" PURGE
prompt prompt --/
prompt select 'CREATE ' || decode(tbl.TEMPORARY,'Y','GLOBAL TEMPORARY ') || 'TABLE "' || tbl.TABLE_NAME || '"(' as cmdline
prompt from USER_TABLES tbl
prompt where tbl.TABLE_NAME = '?&?&1'
prompt /
prompt select decode(CLM.COLUMN_ID,1,' ',' ,') as COL_SEP
prompt , '"' || CLM.COLUMN_NAME || '"' as COLUMN_NAME
prompt , CLM.DATA_TYPE
prompt , case when CLM.DATA_TYPE = 'NUMBER' then
prompt '(' || lpad(CLM.DATA_PRECISION,4) || ',' || lpad(CLM.DATA_SCALE,2) || ')'
prompt when CLM.CHAR_USED = 'C' AND PRM.VALUE = 'CHAR' then
prompt '(' || lpad(CLM.CHAR_LENGTH,4) || ')'
prompt when CLM.CHAR_USED = 'B' AND PRM.VALUE = 'CHAR' then
prompt '(' || lpad(CLM.DATA_LENGTH,4) || ' BYTE)'
prompt when CLM.DATA_TYPE in ('CHAR', 'VARCHAR2') then
prompt '(' || lpad(CLM.DATA_LENGTH,4) || ')'
prompt end as DATA_LEN
prompt , case when CLM.DATA_DEFAULT is not null then 'DEFAULT' end as DATA_DEFAULT_T
prompt , CLM.DATA_DEFAULT
prompt , decode(CLM.NULLABLE,'N','NOT NULL',' NULL') as NULLABLE
prompt , nvl2(CMN.COMMENTS,'-- ' || CMN.COMMENTS,null) as COMMENTS
prompt from USER_TAB_COLUMNS CLM
prompt , USER_COL_COMMENTS CMN
prompt , V$PARAMETER PRM
prompt where CLM.TABLE_NAME = CMN.TABLE_NAME(+)
prompt and CLM.COLUMN_NAME = CMN.COLUMN_NAME(+)
prompt and PRM.NAME = 'nls_length_semantics'
prompt and CLM.TABLE_NAME = '?&?&1'
prompt order by CLM.COLUMN_ID
prompt /
prompt select ')' || decode(tbl.DURATION,'SYS$SESSION',' ON COMMIT PRESERVE ROWS','SYS$TRANSACTION',' ON COMMIT DELETE ROWS') as cmdline
prompt from USER_TABLES tbl
prompt where tbl.TABLE_NAME = '?&?&1'
prompt /
prompt prompt /
------------------------------------------------------------------
-- __tableexport1.sql(APPEND CHECK)
------------------------------------------------------------------
prompt select case when sqlline = 1 then
prompt 'ALTER TABLE "?&?&1" ADD CONSTRAINT "' || cons.CONSTRAINT_NAME || '" CHECK(' || cons.SEARCH_CONDITION_VC || ')'
prompt else '/' end as cmdline
prompt from USER_CONSTRAINTS cons
prompt , (select 1 as sqlline from dual union all select 2 from dual )
prompt where cons.TABLE_NAME = '?&?&1'
prompt and cons.CONSTRAINT_TYPE = 'C' -- PK制約、UNIQUE制約、外部キー制約は除外
prompt and not exists( -- NOT NULL制約は除外
prompt select *
prompt from USER_TAB_COLUMNS cols
prompt where cons.TABLE_NAME = cols.TABLE_NAME
prompt and cons.SEARCH_CONDITION_VC = '"' || cols.COLUMN_NAME || '" IS NOT NULL' )
prompt order by sqlline
prompt /
------------------------------------------------------------------
-- __tableexport1.sql(APPEND TABLE-COMMENT)
------------------------------------------------------------------
prompt select case when sqlline = 1 then
prompt 'COMMENT ON TABLE "?&?&1" IS ''' || tblc.COMMENTS || ''''
prompt else '/' end as cmdline
prompt from USER_TAB_COMMENTS tblc
prompt , (select 1 as sqlline from dual union all select 2 from dual )
prompt where tblc.TABLE_NAME = '?&?&1'
prompt and tblc.COMMENTS IS NOT NULL
prompt order by sqlline
prompt /
------------------------------------------------------------------
-- __tableexport1.sql(APPEND COLUMN-COMMENT)
------------------------------------------------------------------
prompt select case when sqlline = 1 then
prompt 'COMMENT ON COLUMN "?&?&1".' || rpad('"' || colc.COLUMN_NAME || '"',32) || ' IS ''' || colc.COMMENTS || ''''
prompt else '/' end as cmdline
prompt from USER_COL_COMMENTS colc
prompt , USER_TAB_COLUMNS col
prompt , (select 1 as sqlline from dual union all select 2 from dual )
prompt where colc.TABLE_NAME = col.TABLE_NAME
prompt and colc.COLUMN_NAME = col.COLUMN_NAME
prompt and colc.TABLE_NAME = '?&?&1'
prompt and colc.COMMENTS IS NOT NULL
prompt order by col.COLUMN_ID, sqlline
prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __tableexport2.sql(APPEND INDEX)
------------------------------------------------------------------
spool __tableexport2.sql
prompt SET LONG 70
prompt COLUMN INDEX_EXPRESSION FORMAT A70
prompt SPOOL TABLE/?&1..sql APPEND
prompt select case when cons.CONSTRAINT_NAME = idx.INDEX_NAME then 'ALTER TABLE "' || idx.TABLE_NAME || '" ADD CONSTRAINT "' || idx.INDEX_NAME || '" PRIMARY KEY('
prompt when idx.INDEX_TYPE = 'FUNCTION-BASED BITMAP' then 'CREATE BITMAP INDEX "' || idx.INDEX_NAME || '" ON "' || idx.TABLE_NAME || '"('
prompt when idx.UNIQUENESS = 'UNIQUE' then 'CREATE UNIQUE INDEX "' || idx.INDEX_NAME || '" ON "' || idx.TABLE_NAME || '"('
prompt when idx.UNIQUENESS = 'NONUNIQUE' then 'CREATE INDEX "' || idx.INDEX_NAME || '" ON "' || idx.TABLE_NAME || '"('
prompt end cmdline
prompt from USER_INDEXES idx
prompt , USER_CONSTRAINTS cons
prompt where cons.TABLE_NAME(+) = idx.TABLE_NAME
prompt and cons.INDEX_NAME(+) = idx.INDEX_NAME
prompt and cons.CONSTRAINT_TYPE(+) = 'P'
prompt and idx.TABLE_NAME = '?&?&1'
prompt and idx.INDEX_NAME = '?&2'
prompt /
prompt select decode(col.COLUMN_POSITION,1,' ',' ,') as COL_SEP
prompt -- , nvl(exp.COLUMN_EXPRESSION, to_clob('"' || col.COLUMN_NAME || '"')) as INDEX_EXPRESSION ORA-0600回避
prompt , nvl2(exp.COLUMN_EXPRESSION,'/* EXPRESSION */','"' || col.COLUMN_NAME || '"') as COLUMN_NAME
prompt , exp.COLUMN_EXPRESSION as INDEX_EXPRESSION
prompt , decode(col.DESCEND,'DESC','DESC') as DESCEND
prompt from USER_INDEXES idx
prompt , USER_IND_COLUMNS col
prompt , USER_IND_EXPRESSIONS exp
prompt where idx.TABLE_NAME = col.TABLE_NAME
prompt and idx.INDEX_NAME = col.INDEX_NAME
prompt and idx.TABLE_NAME = '?&?&1'
prompt and idx.INDEX_NAME = '?&?&2'
prompt and col.TABLE_NAME = exp.TABLE_NAME(+)
prompt and col.INDEX_NAME = exp.INDEX_NAME(+)
prompt and col.COLUMN_POSITION = exp.COLUMN_POSITION(+)
prompt order by col.COLUMN_POSITION
prompt /
prompt prompt )
prompt prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __tableexport3.sql(RUN)
------------------------------------------------------------------
spool __tableexport3.sql
select '$ECHO Output-TABLE : ' || tbl.TABLE_NAME || '
?@__tableexport1.sql ' || tbl.TABLE_NAME as cmdline
from USER_TABLES tbl
where tbl.TABLE_NAME in ('TABLE1','TABLE2'..)
and not exists(
select *
from USER_OBJECTS obj
where obj.OBJECT_NAME = tbl.TABLE_NAME
and obj.OBJECT_TYPE = 'MATERIALIZED VIEW'
)
and tbl.TABLE_NAME not like 'MLOG$\_%' escape'\'
order by 1
/
spool off
spool __tableexport3.sql APPEND
select '$ECHO Output-INDEX : ' || rpad(idx.INDEX_NAME,30) || ' ON ' || idx.TABLE_NAME || '
?@__tableexport2.sql ' || idx.TABLE_NAME || ' ' || idx.INDEX_NAME as cmdline
from USER_INDEXES idx
where idx.TABLE_NAME in ('TABLE1','TABLE2'..)
and not exists(
select *
from USER_OBJECTS obj
where obj.OBJECT_NAME = idx.TABLE_NAME
and obj.OBJECT_TYPE = 'MATERIALIZED VIEW'
)
and idx.TABLE_NAME not like 'MLOG$\_%' escape'\'
order by 1
/
spool off
------------------------------------------------------------------
-- tableimport.sql
------------------------------------------------------------------
spool tableimport.sql
prompt SPOOL tableimport.log
prompt set linesize 300
prompt set pagesize 0
prompt set term off
prompt set head off
prompt set feed on
prompt set trim on
prompt set trimspool on
prompt set verify off
select 'PROMPT Create-TABLE : ' || tbl.TABLE_NAME || '
?$ECHO Create-TABLE : ' || tbl.TABLE_NAME || '
?@TABLE/' || tbl.TABLE_NAME || '.sql' as cmdline
from USER_TABLES tbl
where tbl.TABLE_NAME in ('TABLE1','TABLE2'..)
and not exists(
select *
from USER_OBJECTS obj
where obj.OBJECT_NAME = tbl.TABLE_NAME
and obj.OBJECT_TYPE = 'MATERIALIZED VIEW'
)
and tbl.TABLE_NAME not like 'MLOG$\_%' escape'\'
order by 1
/
prompt SPOOL OFF
prompt $ECHO 全テーブルの生成が完了しました。
prompt EXIT
spool off
set escape off
$MKDIR TABLE >> __tableexport1.log 2>> __tableexport2.log
@__tableexport3.sql
$del __tableexport*.*
$echo スクリプトを生成しました。
exit
実行(DDL取得)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @tableexport.sql
| 実行時に生成されるサブフォルダ |
| TABLE |
TABLEおよびINDEXのCREATE文を生成 |
| 実行時に生成されるスクリプト |
| tableimport.sql |
生成されたCREATE文を反映するコマンドを生成 |
実行(反映)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @tableimport.sql
| 実行時に生成されるログ |
| tableimport.log |
生成コマンドを実行時のエラー情報を格納 |