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

-- Index --

・Top

・Softwares

▼親父の独り言集

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

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

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

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

いきさつ

DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。

準備するもの

まず、以下のスクリプトをコピーして、"tableexport.sql"というファイルを作成します。 このとき、以下の部分をメンテしてください。

メンテ箇所
in ('TABLE1','TABLE2'..) 出力対象テーブル

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
------------------------------------------------------------------
-- 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.