테이블생성 스크립트

테이블 생성 스크립트 추출
-SQL Script / ORACLE
select table_name y,
0 x,
'create table ' ||
rtrim(table_name) ||
'('
from dba_tables
where owner = upper('&oowner')
union
select tc.table_name y,
column_id x,
rtrim(decode(column_id,1,null,','))||
rtrim(column_name)|| ' ' ||
rtrim(data_type) ||
rtrim(decode(data_type,'DATE',null,'LONG',null,
'NUMBER',decode(to_char(data_precision),null,null,'('),
'(')) ||
rtrim(decode(data_type,
'DATE',null,
'CHAR',data_length,
'VARCHAR2',data_length,
'NUMBER',decode(to_char(data_precision),null,null,
to_char(data_precision) || ',' || to_char(data_scale)),
'LONG',null,
'******ERROR')) ||
rtrim(decode(data_type,'DATE',null,'LONG',null,
'NUMBER',decode(to_char(data_precision),null,null,')'),
')')) || ' ' ||
rtrim(decode(nullable,'N','NOT NULL',null))
from dba_tab_columns tc,
dba_objects o
where o.owner = tc.owner
and o.object_name = tc.table_name
and o.object_type = 'TABLE'
and o.owner = upper('&&oowner')
union
select table_name y,
999999 x,
')' || chr(10)
||' STORAGE(' || chr(10)
||' INITIAL ' || initial_extent || chr(10)
||' NEXT ' || next_extent || chr(10)
||' MINEXTENTS ' || min_extents || chr(10)
||' MAXEXTENTS ' || max_extents || chr(10)
||' PCTINCREASE '|| pct_increase || ')' ||chr(10)
||' INITRANS ' || ini_trans || chr(10)
||' MAXTRANS ' || max_trans || chr(10)
||' PCTFREE ' || pct_free || chr(10)
||' PCTUSED ' || pct_used || chr(10)
||' PARALLEL (DEGREE ' || DEGREE || ') ' || chr(10)
||' TABLESPACE ' || rtrim(tablespace_name) ||chr(10)
||'/'||chr(10)||chr(10)
from dba_tables
where owner = upper('&&oowner')
order by 1,2
테이블생성 스크립트

DB CAFE
