テーブル生成コマンド取得

-- Index --

・Top

・Softwares

▼親父の独り言集

▼Oracleデータベースに対する独り言集

・ご利用の前に
・CSVデータ出力バッチ作成
・SQLLoader実行バッチ作成(制御ファイルもね)
・主キー作成・削除コマンド取得
・実行中セッションKILLコマンド取得
・PL/SQLソース反映コマンド取得
→テーブル生成コマンド取得
・ビュー生成コマンド取得
・マテリアライズド・ビュー生成コマンド取得
・マテリアライズド・ビューログ生成コマンド取得
・順序(シーケンス)生成コマンド取得
・データベース・リンク生成コマンド取得
・シノニム生成コマンド取得
・サブクエリーのアクセス順指定
・グループ単位の通し番号とグループ内の連番

・VB.NETやC#に対する独り言集

・我が家の家電事情について

いきさつ

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 生成コマンドを実行時のエラー情報を格納


Copyright(c) 2014-2022 pakkin. All Rights Reserved.