Re: creating sql scripts for existing tables ?

This is what I do and it has been working fine without any problem : export table without data and grep -i -e 'create table' -e 'alter table' expdat.dmp > script.sql. You can pass the ouput of grep through sed, awk, perl, tr whatever you need for formatting it nicely and getting rid of null chars. You will get a perfectly valid script with column, table constraints, table sizing, column defs etc. If you export indexes, you cen also get create index script. Our production reorgs use this and I have never had any problem.

ruud_at_waubel.wau.mis.ah.nl (Ruud de Gunst) wrote:

>creinke_at_ccnet.com (Charles Reinke) wrote:
 

>>The only way I've been able to create scripts for tables is to save them
>>at the time I type them in. Is there a way to create ".sql" scripts from
>>existing database in oracle ?
 

>>Thanks in advance, Chuck
 

>Charles,
 

>It's all in the datadictionary. Here's an example script for creating
>"create table" statements using the information stored in the Oracle
>datadictionary. This script does not produce the storage clause, but
>that's a feature you can add yourself.
 

>Bye,
>Ruud
 

>Script:
>rem
>=======================================================================
>rem Program : get_tab.sql
>rem Type : SQL script
>rem Input : tab_owner char - owner of the table(s)
>rem Output : tmp.sql file - file, can be removed afterwards
>rem owner.tbr file - file containing create table
>stats
>rem Date : 10-10-94
>rem Author : Ruud de Gunst, Bergler Nederland bv
>rem Purpose : Generates create table statements for a given user.
>rem
>=======================================================================
 

>set heading off
>set verify off
>set feedback off
>set pagesize 0
>set linesize 80
>set newpage 0
 

>prompt
>prompt Recreate create table statements
>accept tab_owner char prompt ' for owner: '
>prompt
 

>set termout off
 

>column spool_ext noprint new_value ext
 

>select substr('&&tab_owner', 2, 3) spool_ext
>from sys.dual
>/
 

>spool tmp.sql
 

>prompt spool &&tab_owner..tbr
 

>select 'prompt prompt' || chr(10) ||
> 'prompt prompt Create table ' || tb.owner || '.' ||
>tb.table_name || chr(10) ||
> 'prompt prompt' || chr(10) ||
> 'prompt CREATE TABLE ' || tb.owner || '.' || tb.table_name ||
>chr(10) ||
> 'select rpad(decode' || chr(10) ||
> ' (tc.column_id' || chr(10) ||
> ' ,1, ''(''||tc.column_name' || chr(10) ||
> ' ,'',''||tc.column_name), 31, '' '') ||' ||
>chr(10) ||
> ' rpad(decode' || chr(10) ||
> ' (tc.data_type' || chr(10) ||
> ' ,''DATE'', ''DATE'' ' || chr(10) ||
> ' ,''NUMBER'' ' || chr(10) ||
> ' ,''NUMBER(''||to_char(nvl(tc.data_precision,
>tc.data_length))||' || chr(10) ||
> ' decode' || chr(10) ||
> ' ('',''||to_char(tc.data_scale)' || chr(10) ||
> ' ,'',0'', null' || chr(10) ||
> ' ,'','', null' || chr(10) ||
> ' ,'',''||to_char(tc.data_scale)' || chr(10) ||
> ' ) ||'')'' ' || chr(10) ||
> '
>,tc.data_type||''(''||to_char(tc.data_length)||'')'' ' || chr(10) ||
> ' ), 16, '' '') ||' || chr(10) ||
> ' decode(tc.nullable' || chr(10) ||
> ' ,''N'' ' || chr(10) ||
> ' ,''NOT NULL'' ' || chr(10) ||
> ' ,''NULL'' ' || chr(10) ||
> ' )' || chr(10) ||
> 'from sys.dba_tab_columns tc' || chr(10) ||
> 'where tc.table_name = '||''''||table_name||'''' || chr(10) ||
> 'and tc.owner = '||''''||owner||'''' || chr(10) ||
> 'order by tc.column_id' || chr(10) ||
> '/' || chr(10) ||
> 'prompt )' || chr(10) ||
> 'prompt TABLESPACE '|| tablespace_name || chr(10) ||
> 'prompt /' || chr(10) ||
> 'prompt' || chr(10)
>from sys.dba_tables tb
>where tb.owner like upper('&&tab_owner')
>order by tb.table_name
>/
 

>prompt spool off
 

>spool off
 

>_at_tmp.sql
 

>clear columns
>clear breaks
 

>set heading on
>set verify on
>set feedback on
>set pagesize 24
>set newpage 1
>set termout on
Received on Thu Feb 22 1996 - 00:00:00 CET