From owner-oracle-l@CCVM.SUNYSB.EDU Sun Mar 19 18:41:34 1995 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65/1.1.8.2/01Aug94-0142PM) id AA00449; Sun, 19 Mar 1995 18:41:30 -0500 Message-Id: <9503192341.AA00449@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by CCVM.sunysb.edu (IBM VM SMTP V2R2) with BSMTP id 7722; Sun, 19 Mar 95 18:23:42 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 8421; Sun, 19 Mar 1995 15:57:04 -0500 Date: Sun, 19 Mar 1995 15:57:04 -0500 Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: David Wolcott Subject: Re: table creation X-To: ORACLE-L@CCVM.sunysb.edu To: Multiple recipients of list ORACLE-L X-Orig-Date: Thu, 16 Mar 1995 16:25:14 -0800 ---------- 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@imagin1.com ---------- X-Sun-Data-Type: default X-Sun-Data-Description: default X-Sun-Data-Name: tab_create.sql X-Sun-Charset: us-ascii X-Sun-Content-Lines: 63 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-Charset: us-ascii X-Sun-Content-Lines: 1 sqlplus sys/password @tab_create DAVIDB ACCTBALS