Re: table creation

From: David Wolcott <davidw_at_IMAGIN1.COM>
Date: Sun, 19 Mar 1995 15:56:58 -0500
Message-Id: <9503192341.AA26745@alice.jcc.com>

---

X-Sun-Data-Type: text
X-Sun-Data-Description: text
X-Sun-Data-Name: text
X-Sun-Charset: us-ascii
X-Sun-Content-Lines: 15

Dave:

a long time ago i needed to replicate some production tables on a very small database. because the database was so small (due to hardware requirements) i could not use the exp and imp utilities. to get around this i used the scripts that are attached to this message. one is just a sql script and one is a shell script that executes the sql script. its output should be a create statement for a given table. i haven't used it in a while so check out its results to make sure their accurrate.

good luck

david wolcott

davidw_at_imagin1.com

---

X-Sun-Data-Type: default
X-Sun-Data-Description: default
X-Sun-Data-Name: tab_create.sql
X-Sun-Content-Lines: 63
X-Sun-Charset: us-ascii

set pages 0

col maxid new_value maxid noprint

select max(column_id) maxid from dba_tab_columns where owner='&&1' AND table_name = '&&2' ;

set term off
set echo off
set concat +
spool &&1+_&&2.sql
set concat .
set feedback off
set heading off
set pagesize 0
set ver off
select 'create ' ||
' table ' ||table_name || '('
 from sys.dba_tables where owner= '&&1' and table_name = '&&2' /

select column_name||' '||data_type||' '


||decode(data_type,'VARCHAR2','(','CHAR','(',null)

||decode(data_type,'VARCHAR2',data_length,'CHAR',data_length,null)

||decode(data_type,'VARCHAR2',')','CHAR',')',null)

||decode(data_scale,null,null,'(')

||decode(data_precision,null,null,data_precision)

||decode(data_scale,null,null,0,null,',')

||decode(data_scale,null,null,0,null,data_scale)

||decode(data_scale,null,null,')')

||decode(nullable,'N',' not null',null)
||decode(column_id,&&maxid,null,',')

from sys.dba_tab_columns
where owner='&&1' AND table_name = '&&2' order by column_id
/
select ' ) tablespace ' || tablespace_name
|| ' initrans ' || ini_trans
|| ' maxtrans ' || max_trans

 from sys.dba_tables where owner= '&&1' and table_name = '&&2' /
select ' storage ( initial '|| sum(bytes)  from sys.dba_segments where owner= '&&1' and segment_name = '&&2'  and segment_type= 'TABLE'
/
select ' next '|| sum(bytes)
 from sys.dba_segments where owner= '&&1' and segment_name = '&&2'  and segment_type= 'TABLE'
/
select ' minextents '||min_extents
|| ' maxextents '||max_extents
|| ' pctincrease '|| pct_increase || ');'
 from sys.dba_tables where owner= '&&1' and table_name = '&&2' /
select ' '
FROM DUAL;

select 'REM ***********************  NEW TABLE ********************'

FROM DUAL;
select ' '
FROM DUAL;
spool off
exit

---

X-Sun-Data-Type: msdos-executable
X-Sun-Data-Description: msdos-executable
X-Sun-Data-Name: create_test1_tables.exe
X-Sun-Content-Lines: 1
X-Sun-Charset: us-ascii

sqlplus sys/password @tab_create DAVIDB ACCTBALS Received on Sun Mar 19 1995 - 18:41:28 CST