Re: Duplicating Oracle DB w/o Schema or Scripts
Date: 1997/08/01
Message-ID: <33E1FB9E.94C_at_uk.sun.com>
Tom Patton wrote:
> > Hi, > [Quoted] [Quoted] > Does anyone know of a method to generate a sql script or schema > to create the tables, views, etc, of an Oracle/7 database? > I am thinking about just massaging query output from the > USER_* data dictionary tables using Sql*Plus, perl, and > a bunch of elbow grease, but it seems like there should > be an easier way (yeah-i know-like maintaining the schema > definitions in a script from the beginning). > > Any suggestions are appreciated (especially via e-mail since > I don't browse here all the time) > > -tom > Tom-n-Tracy_at_msn.com
Hi Tom
Take a look at some old scripts that I carry around with me. There are 10 SQL*Plus scripts - you'll have to separate them using a text editor.
They are a little long in the tooth now, but should still do the basics well enough. There's no copyright or any of that nonsense.
Regards
John R.
rem MODULE REF : gc_comm.sql rem PURPOSE : To generate a SQL script which can be used to rem recreate comments based on currently existing ones. rem CREATION : 11-OCT-94 by J. Rudman
rem
SET COMPATIBILITY V6
SET PAGES 0 FEEDBACK OFF RECSEP OFF ARRAYSIZE 1 MAXDATA 20000
SET VERIFY OFF LINESIZE 255
SET TERMOUT OFF
COLUMN cr NEWLINE
rem
--------------------------------------------------------------------------.
rem Set the "batch mode" flag.
rem If we are in batch mode, the script will not be interactive and all
rem values will be defaulted.
rem
--------------------------------------------------------------------------.
DEFINE batch_mode=N
rem
--------------------------------------------------------------------------.
rem Pre-define option flags.
rem
--------------------------------------------------------------------------.
DEFINE Table_name=''
DEFINE Output_File=''
rem
--------------------------------------------------------------------------.
rem Determine default output file name (based on ORACLE username) and
construct
rem the prompt text.
rem
--------------------------------------------------------------------------.
COLUMN dflt_out NEW_VALUE dflt_out
COLUMN out_prompt NEW_VALUE out_prompt
SELECT REPLACE(USER,'$','_')||'.cmt' dflt_out,
LPAD('['||REPLACE(USER,'$','_')||'.cmt]',18) out_prompt FROM
DUAL;
rem
--------------------------------------------------------------------------.
rem If we are in interactive mode, get the options.
rem If the user wants storage information, then we additionally prompt
whether
rem to compress extents.
rem
-------------------------------------------------------------------------.SPOOL gcmtmp1.sql
SELECT 'SET TERMOUT ON',
'ACCEPT Table_name PROMPT '||
'''Enter the table (wild cards accepted) [%%] : ''' cr,
'ACCEPT Output_File PROMPT '||
'''Enter the output file name &out_prompt : ''' cr,
'SET TERMOUT OFF' cr
FROM DUAL
WHERE '&batch_mode' = 'N';
SPOOL OFF
START gcmtmp1
rem
--------------------------------------------------------------------------.
rem If we are in batch mode, we supply the defaults here.
rem If we are in interactive mode and the user just pressed [Return] at
any
rem prompt, we also supply the default. This code will convert the
reply to
rem upper case if necessary. Invalid values will be substituted by the
rem default.
rem
--------------------------------------------------------------------------.
SPOOL gcmtmp2.sql
SELECT DECODE('&Table_name',
'', 'DEFINE Table_name=''%''',
NULL),
DECODE('&Output_File',
'', 'DEFINE Output_File='''||RTRIM('&dflt_out')||'''',
NULL) cr
FROM DUAL; SPOOL OFF
START gcmtmp2
rem HOST rm -f gcmtmp*.sql
rem
rem The main script generation is done here rem
SET TERMOUT ON
PROMPT Working...
SET TERMOUT OFF
SPOOL gcmtmp3.sql
PROMPT SPOOL gcmtmp.out
SELECT
'PROMPT',
'SELECT ''COMMENT ON TABLE '||LOWER(ut.table_name)||' IS'',' cr,
' '' ''''''||REPLACE(comments, '''''''', '''''''''''')||
'''''';'' cr' cr,
'FROM user_tab_comments' cr,
'WHERE table_name = '''||ut.table_name||'''' cr,
'AND comments IS NOT NULL;' cr,
'SELECT ''COMMENT ON COLUMN '||
LOWER(ut.table_name)||'.''||LOWER(column_name)||'' IS'',' cr,
' '' ''''''||REPLACE(comments, '''''''', '''''''''''')||
'''''';'' cr' cr,
'FROM user_col_comments' cr,
'WHERE table_name = '''||ut.table_name||'''' cr,
'AND comments IS NOT NULL;' cr
FROM user_tables ut
WHERE ut.cluster_name IS NULL
AND ut.table_name LIKE UPPER(NVL('&Table_name','%')) AND (EXISTS (SELECT ''
FROM user_tab_comments utc
WHERE utc.table_name = ut.table_name
AND utc.comments IS NOT NULL)
OR
EXISTS (SELECT ''
FROM user_col_comments ucc
WHERE ucc.table_name = ut.table_name
AND ucc.comments IS NOT NULL));
PROMPT SPOOL OFF
SPOOL OFF
START gcmtmp3
rem
--------------------------------------------------------------------------.
rem Remove trailing blanks from output and rename to user's choice
rem
--------------------------------------------------------------------------.
HOST echo "SPOOL `basename &Output_File .sql`.log" > &Output_File HOST echo "SET DEFINE OFF" >> &Output_File HOST echo "SET COMPATIBILITY V6" >> &Output_FileHOST cat gcmtmp.out | sed 's/ *$//g' >> &Output_File HOST echo "SPOOL OFF" >> &Output_File
rem
--------------------------------------------------------------------------.
rem Remove temporary files
rem
--------------------------------------------------------------------------.
HOST rm -f gcmtmp*
rem
--------------------------------------------------------------------------.
rem Restore default SQL*Plus settings, if installation uses them.
rem
--------------------------------------------------------------------------.
rem PROMPT _at_?/dbs/glogin
SET TERMOUT ON
rem EXIT
rem MODULE REF : gc_cons.sql rem PURPOSE : To generate a SQL script which can be used to rem recreate constraints based on currently existing ones. rem CREATION : 11-OCT-94 by J. Rudman rem rem OVERVIEW : This SQL*Plus script looks up the DBMS data dictionary rem and creates a DDL script which may subsequently be used rem to recreate the constraints on existing tables. rem rem The actual name of this output script may be chosen by rem the user at run-time. rem rem When looking at CHECK constraints, those which are added rem by the system for NOT NULL columns are ignored. rem rem All constraints will be implemented by ALTER TABLE commands rem to add table-level constraints, rather than column-level rem constraints. This makes no difference to the way that they rem are stored in the dictionary.
rem
COLUMN cr NEWLINE
SET COMPATIBILITY V6
SET FEEDBACK OFF PAGESIZE 0 VERIFY OFF RECSEP OFF LONG 512
SET TERMOUT OFF
rem
--------------------------------------------------------------------------.
rem Set the "batch mode" flag.
rem If we are in batch mode, the script will not be interactive and all
rem values will be defaulted.
rem
--------------------------------------------------------------------------.
DEFINE batch_mode=N
rem
--------------------------------------------------------------------------.
rem Pre-define option flags.
rem
--------------------------------------------------------------------------.
DEFINE Table_name=''
DEFINE Output_File=''
rem
--------------------------------------------------------------------------.
rem Determine default output file name (based on ORACLE username) and
construct
rem the prompt text.
rem
--------------------------------------------------------------------------.
COLUMN dflt_out NEW_VALUE dflt_out
COLUMN out_prompt NEW_VALUE out_prompt
SELECT REPLACE(USER,'$','_')||'.cst' dflt_out,
LPAD('['||REPLACE(USER,'$','_')||'.cst]',18) out_prompt FROM
DUAL;
rem
--------------------------------------------------------------------------.
rem If we are in interactive mode, get the options.
rem If the user wants storage information, then we additionally prompt
whether
rem to compress extents.
rem
-------------------------------------------------------------------------.SPOOL gcctmp1.sql
SET ESCAPE \ LINESIZE 256
SELECT 'SET TERMOUT ON',
'ACCEPT Table_name PROMPT '||
'''Enter the table (wild cards accepted) [%%] : ''' cr,
'ACCEPT Output_File PROMPT '||
'''Enter the output file name &out_prompt : ''' cr,
'SET TERMOUT OFF' cr
FROM DUAL
WHERE '&batch_mode' = 'N';
SET ESCAPE OFF
SPOOL OFF
SET LINESIZE 80
START gcctmp1
rem
--------------------------------------------------------------------------.
rem If we are in batch mode, we supply the defaults here.
rem If we are in interactive mode and the user just pressed [Return] at
any
rem prompt, we also supply the default. This code will convert the
reply to
rem upper case if necessary. Invalid values will be substituted by the
rem default.
rem
--------------------------------------------------------------------------.
SPOOL gcctmp2.sql
SELECT DECODE('&Table_name',
'', 'DEFINE Table_name=''%''',
NULL),
DECODE('&Output_File',
'', 'DEFINE Output_File='''||RTRIM('&dflt_out')||'''',
NULL) cr
FROM DUAL; SPOOL OFF
START gcctmp2
rem
rem The main script generation is done here rem For each constraint, a corresponding script is called to create the DDL.
rem This script will produce a file called gc_cons.lst, which is then appended
rem to the final script.
rem
SET TERMOUT ON
PROMPT Working...
SET TERMOUT OFF
SPOOL gcctmp3.sql
PROMPT HOST echo '' > gcctmp.out
SELECT 'START gc_cons'||
DECODE(uc.constraint_type, 'U', 'p', LOWER(uc.constraint_type))||
' '||uc.constraint_name||' '||uc.table_name||' '||uc.constraint_type,
'HOST sed ''s/ *$//g'' < gcctmp'||
DECODE(uc.constraint_type, 'U', 'p', LOWER(uc.constraint_type))||
'.lst >> gcctmp.out' cr
FROM user_constraints uc
WHERE uc.table_name LIKE UPPER('&Table_Name')
AND (uc.constraint_type IN ('P', 'U', 'R')
OR (uc.constraint_type = 'C'
AND (1 < (SELECT COUNT(*)
FROM user_cons_columns ucc
WHERE ucc.constraint_name = uc.constraint_name)
OR
(1 = (SELECT COUNT(*)
FROM user_cons_columns ucc
WHERE ucc.constraint_name = uc.constraint_name)
AND NOT EXISTS
(SELECT ''
FROM user_tab_columns utc,
user_cons_columns ucc
WHERE ucc.constraint_name = uc.constraint_name
AND utc.table_name = ucc.table_name
AND utc.column_name = ucc.column_name
AND utc.nullable = 'N')))))
ORDER BY uc.table_name,
DECODE(uc.constraint_type, 'P', 1, 'U', 2, 'R', 3, 4);
SPOOL OFF
START gcctmp3
rem
--------------------------------------------------------------------------.
rem Remove trailing blanks from output and rename to user's choice
rem
--------------------------------------------------------------------------.
HOST echo "SPOOL `basename &Output_File .sql`.log" > &Output_File HOST echo "SET DEFINE OFF" >> &Output_File HOST echo "SET COMPATIBILITY V6" >> &Output_FileHOST cat gcctmp.out | sed 's/ *$//g' >> &Output_File HOST echo "SPOOL OFF" >> &Output_File
rem
--------------------------------------------------------------------------.
rem Remove temporary files
rem
--------------------------------------------------------------------------.
HOST rm -f gcctmp*
SET TERMOUT ON
rem MODULE REF : gc_consc.sql rem PURPOSE : To generate DDL for a check constraint rem CREATION : 11-OCT-94 by J. Rudman rem rem OVERVIEW : This SQL*Plus script is called by gc_cons.sql to produce rem DDL for one CHECK constraint. rem rem Three parameters are passed: rem 1 - constraint name rem 2 - table name rem 3 - constraint type (always 'C') rem rem Note that the "search condition" is held in a LONG column. rem If it is longer than 512 bytes, we will truncate it. rem Formatting will be a little odd for this one.rem
SET COMPATIBILITY V6 SPOOL gcctmpc1.lst
SELECT 'ALTER TABLE '||LOWER('&2')||' ADD CHECK (',
uc.search_condition,
')'||DECODE(REPLACE(TRANSLATE('&1', '0123456789', '0000000000'),
'0'),
'SYS_C', '',
' CONSTRAINT '||LOWER('&1'))||';' cr
FROM user_constraints uc
WHERE uc.constraint_name = '&1'
AND uc.table_name = '&2';
SPOOL OFF
rem
--------------------------------------------------------------------------.
rem Delete unwanted blank lines
rem
--------------------------------------------------------------------------.
HOST sed '/^ *$/ d' < gcctmpc1.lst > gcctmpc.lst
rem MODULE REF : gc_consp.sql
rem PURPOSE : To generate DDL for a primary key or unique constraint
rem CREATION : 11-OCT-94 by J. Rudman
rem
rem OVERVIEW : This SQL*Plus script is called by gc_cons.sql to produce
rem DDL for one PRIMARY KEY or UNIQUE constraint.
rem
rem Three parameters are passed:
rem 1 - constraint name
rem 2 - table name
rem 3 - constraint type ('P' or 'U')
rem
SET COMPATIBILITY V6 SPOOL gcctmpp.lst
SELECT 'ALTER TABLE '||LOWER('&2')||' ADD '||
DECODE('&3', 'P', 'PRIMARY KEY', 'U', 'UNIQUE', '')||' ('
FROM DUAL;
SELECT DECODE(position, 1, ' ', ' ,')||LOWER(ucc.column_name)
FROM user_cons_columns ucc
WHERE ucc.constraint_name = '&1'
ORDER BY ucc.position;
SELECT ')'||DECODE(REPLACE(TRANSLATE('&1', '0123456789', '0000000000'), '0'),
'SYS_C', '',
' CONSTRAINT '||LOWER('&1'))||';'
FROM DUAL; SPOOL OFF
rem MODULE REF : gc_consr.sql rem PURPOSE : To generate DDL for a foreign key constraint rem CREATION : 11-OCT-94 by J. Rudman rem rem OVERVIEW : This SQL*Plus script is called by gc_cons.sql to produce rem DDL for one FOREIGN KEY constraint. rem rem Three parameters are passed: rem 1 - constraint name rem 2 - table name rem 3 - constraint type (always 'R')
rem
SET COMPATIBILITY V6 SPOOL gcctmpr.lst
SELECT 'ALTER TABLE '||LOWER('&2')||' ADD FOREIGN KEY (' FROM DUAL;
SELECT DECODE(position, 1, ' ', ' ,')||LOWER(ucc.column_name)
FROM user_cons_columns ucc
WHERE ucc.constraint_name = '&1'
ORDER BY ucc.position;
SELECT ') REFERENCES '||
DECODE(uc.r_owner, USER, '', LOWER(uc.r_owner)||'.')||
LOWER(ac.table_name)||' ('
FROM all_constraints ac,
user_constraints uc
WHERE uc.constraint_name = '&1'
AND ac.owner = uc.r_owner
AND ac.constraint_name = uc.r_constraint_name;
SELECT DECODE(position, 1, ' ', ' ,')||LOWER(acc.column_name) FROM all_cons_columns acc,
user_constraints uc
WHERE uc.constraint_name = '&1' AND acc.owner = uc.r_owner AND acc.constraint_name = uc.r_constraint_nameORDER BY acc.position;
SELECT ')'||DECODE(REPLACE(TRANSLATE('&1', '0123456789', '0000000000'), '0'),
'SYS_C', '',
' CONSTRAINT '||LOWER('&1'))||';'
FROM DUAL; SPOOL OFF
rem MODULE REF : gc_grnt.sql rem PURPOSE : Generate script to recreate grants on table(s). rem CREATION : 21-JUN-91 by J. Rudman rem rem OVERVIEW : This SQL*Plus script looks up the data dictionary view rem of the current user's grants made and generates a rem SQL*Plus script which would recreate these grants. rem The user may specify all the tables or just a subset. rem The user may also supply the resulting file name; the rem default is "cre_grnt.sql".
rem
SET COMPATIBILITY V6
SET TERMOUT OFF
COLUMN out_prompt NEW_VALUE out_prompt
COLUMN dflt_out NEW_VALUE dflt_out
SELECT USER||'.grt' dflt_out, LPAD('['||USER||'.grt]',14) out_prompt
FROM DUAL;
SET TERMOUT ON
ACCEPT Table_name PROMPT 'Enter the table (wild cards accepted) [%%] :
'
ACCEPT Output_File PROMPT 'Enter the output file name &out_prompt : '
SET PAGES 0 FEEDBACK OFF RECSEP OFF ARRAYSIZE 10 VERIFY OFF PAUSE OFF
SET TERMOUT OFF
COLUMN out_file NEW_VALUE out_file
SELECT NVL('&Output_File', RTRIM('&dflt_out')) out_file
FROM DUAL
/
SPOOL gcgtmp.out
SELECT 'GRANT '||privilege||' ON '||table_name||' TO '||grantee||
DECODE(grantable,'YES',' WITH GRANT OPTION;',';')
FROM user_tab_privs_made
WHERE table_name LIKE UPPER(NVL('&Table_Name','%'))
/
SPOOL OFF
rem Remove trailing blanks from output and rename to user's choice
HOST echo "SPOOL `basename &out_file .sql`.log" > &out_file
HOST cat gcgtmp.out | sed 's/ *$//g' >> &out_file
HOST echo "SPOOL OFF" >> &out_file
rem Remove temporary file
HOST rm -f gcgtmp.out
SET TERMOUT ON
rem EXIT
rem MODULE REF : gc_idxs.sql rem PURPOSE : To generate a SQL script which can be used to rem recreate indexes based on currently existing ones. rem CREATION : 21-JUN-91 by J. Rudman, Oracle UK rem rem OVERVIEW : This SQL*Plus script looks up the data dictionary view rem of the current user's indexes and generates a temporary rem SQL*Plus script called gcitmp.sql. This is then run rem to create the "cre_idxs.sql" script, which may be used rem as is or edited to adjust the storage parameters. rem The "cre_idxs.sql" script presents the indexes in table- rem name / index-name order, with all unique indexes coming rem before non-unique ones for each table. rem rem Currently not implemented for cluster indexes.rem
SET COMPATIBILITY V6
SET TERMOUT OFF
COLUMN dflt_out NEW_VALUE dflt_out
COLUMN out_prompt NEW_VALUE out_prompt
SELECT USER||'.idx' dflt_out, LPAD('['||USER||'.idx]',18) out_prompt
FROM DUAL;
SET TERMOUT ON
ACCEPT Table_name PROMPT 'Enter the table (wild cards accepted)
[%%] : '
ACCEPT Output_File PROMPT 'Enter the output file name &out_prompt : '
ACCEPT Quiet_Drop PROMPT 'Suppress drop non-existent index message? [Y]
: '
SET PAGES 0 RECSEP OFF ARRAYSIZE 10 VERIFY OFF PAUSE OFF
SET FEEDBACK OFF
SET TERMOUT OFF
DROP VIEW gci_view;
CREATE VIEW gci_view (index_name, space) AS SELECT e.segment_name, SUM(e.bytes)
FROM user_extents e,
user_indexes i
WHERE e.segment_type = 'INDEX'
AND e.segment_name = i.index_name
AND i.table_name LIKE UPPER(NVL('&Table_name','%'))
AND i.table_type = 'TABLE'
AND i.table_owner = USER
GROUP BY e.segment_name
/
COLUMN out_file NEW_VALUE out_file
SELECT NVL('&Output_File',
REPLACE(REPLACE(LTRIM('&dflt_out'),'['),']')) out_file
FROM DUAL
/
COLUMN cr NEWLINE
SPOOL gcitmp.sql
PROMPT SPOOL gcitmp.out
SELECT
'PROMPT PROMPT Index: '||i.index_name,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'PROMPT SET TERMOUT OFF') cr,
'PROMPT DROP INDEX '||LOWER(i.index_name)||';;' cr,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'PROMPT SET TERMOUT ON') cr,
'PROMPT CREATE '
||DECODE(i.uniqueness, 'UNIQUE', 'UNIQUE ', '')
||'INDEX '||LOWER(i.index_name)||' ON '||LOWER(i.table_name) cr,
'PROMPT (' cr,
'SELECT DECODE(column_position,1,'' '','' ,'') ||' ||
' LOWER(column_name)' cr,
'FROM user_ind_columns' cr,
'WHERE index_name = '''||i.index_name||'''' cr,
'ORDER BY column_position' cr,
'/' cr,
'SELECT '')'',' cr,
' ''INITRANS '||TO_CHAR(i.ini_trans)||''' cr,' cr,
' ''MAXTRANS '||TO_CHAR(i.max_trans)||''' cr,' cr,
' ''TABLESPACE '||LOWER(i.tablespace_name)||''' cr,' cr,
' ''STORAGE'' cr,' cr,
' ''('' cr,' cr,
' '' INITIAL '
||TO_CHAR((TRUNC((v.space-1)/10240)+1)*10240)||''' cr,' cr,
' '' NEXT '||TO_CHAR(i.next_extent)||''' cr,' cr,
' '' MINEXTENTS '||TO_CHAR(i.min_extents)||''' cr,' cr,
' '' MAXEXTENTS '||TO_CHAR(i.max_extents)||''' cr,' cr,
' '' PCTINCREASE '||TO_CHAR(i.pct_increase)||''' cr,' cr,
' '')'' cr,' cr,
' ''PCTFREE '||TO_CHAR(i.pct_free)||''' cr,' cr,
' ''/'' cr' cr,
'FROM DUAL;' cr
FROM user_indexes i,
gci_view v
WHERE i.index_name = v.index_name
ORDER BY i.table_name, DECODE(i.uniqueness, 'UNIQUE', '0', '1'), i.index_name
/
PROMPT SPOOL OFF
SPOOL OFF
DROP VIEW gci_view
/
START gcitmp
rem Remove trailing blanks from output and rename to user's choice
HOST echo "SPOOL `basename &out_file .sql`.log" > &out_file HOST echo "SET DEFINE OFF" >> &out_file HOST echo "SET COMPATIBILITY V6" >> &out_fileHOST cat gcitmp.out | sed 's/ *$//g' >> &out_file HOST echo "SPOOL OFF" >> &out_file
rem Remove temporary files
HOST rm -f gcitmp.sql gcitmp.out
rem Restore default SQL*Plus settings, if installation uses them. rem PROMPT _at_?/dbs/glogin
SET TERMOUT ON
rem EXIT
rem MODULE REF : gc_seqs.sql rem PURPOSE : Generate script to recreate sequences. rem CREATION : 23-JUL-91 by J. Rudman, Oracle UK rem rem OVERVIEW : This SQL*Plus script looks up the data dictionary view rem of the current user's sequences and generates a rem SQL*Plus script which defaults to "cre_seqs.sql" script and rem can be used as is or edited to adjust the values. rem Note that the "START WITH" parameter will always be 1. rem Also, the defaults when the INCREMENT BY is < 0 are rem a bit unclear; they certainly differ from the manual!rem
SET COMPATIBILITY V6
SET TERMOUT OFF
COLUMN dflt_out NEW_VALUE dflt_out
COLUMN out_prompt NEW_VALUE out_prompt
SELECT USER||'.seq' dflt_out, LPAD('['||USER||'.seq]',22) out_prompt
FROM DUAL;
SET TERMOUT ON
ACCEPT Seq_Name PROMPT 'Enter the sequence name (wild cards accepted)
[%%] : '
ACCEPT Output_File PROMPT 'Enter the output file name &out_prompt : '
ACCEPT Quiet_Drop PROMPT 'Suppress drop non-existent sequence message?
[Y] : '
SET PAGES 0 FEEDBACK OFF RECSEP OFF ARRAYSIZE 10 VERIFY OFF PAUSE OFF
SET TERMOUT OFF
COLUMN out_file NEW_VALUE out_file
SELECT NVL('&Output_File', RTRIM('&dflt_out')) out_file
FROM DUAL
/
SET TERMOUT OFF
COLUMN cr NEWLINE
SPOOL gcstmp.out
PROMPT SET FEEDBACK ON TERMOUT ON
SELECT 'PROMPT Sequence: '||sequence_name,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'SET TERMOUT OFF') cr,
'DROP SEQUENCE '||sequence_name||';' cr,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'SET TERMOUT ON') cr,
'CREATE SEQUENCE '||sequence_name cr,
' INCREMENT BY '||TO_CHAR(increment_by) cr,
' START WITH 1' cr,
' MINVALUE '||
NVL(TO_CHAR(min_value),
DECODE(SIGN(increment_by),
-1, '-999999999999999999999999999', '1')) cr,
' MAXVALUE '||
NVL(TO_CHAR(max_value),
DECODE(SIGN(increment_by),
-1, '-1', '999999999999999999999999999')) cr,
DECODE(cycle_flag,'Y',' CYCLE',' NOCYCLE') cr,
DECODE(order_flag,'Y',' ORDER',' NOORDER') cr,
DECODE(cache_size,0,' NOCACHE',
' CACHE '||TO_CHAR(cache_size)) cr,
'/' cr,
DECODE(SIGN(increment_by),-1,'PROMPT Caution - double check '||
'your decrementing sequence '||sequence_name,NULL) cr
FROM user_sequences
WHERE sequence_name LIKE UPPER(NVL('&Seq_Name','%')) /
rem PROMPT _at_?/dbs/glogin
SPOOL OFF rem Remove trailing blanks from output and rename to user's choice
HOST echo "SPOOL `basename &out_file .sql`.log" > &out_file HOST echo "SET DEFINE OFF" >> &out_file HOST echo "SET COMPATIBILITY V6" >> &out_fileHOST cat gcstmp.out | sed 's/ *$//g' >> &out_file HOST echo "SPOOL OFF" >> &out_file
rem Remove temporary files
HOST rm -f gcstmp.out
rem Restore default SQL*Plus settings, if installation uses them. rem _at_?/dbs/glogin
SET TERMOUT ON
rem EXIT
rem MODULE REF : gc_tabs.sql rem PURPOSE : To generate a SQL script which can be used to rem recreate empty tables based on currently existing ones. rem CREATION : 17-JUN-91 by J. Rudman, Oracle UK rem rem OVERVIEW : This SQL*Plus script looks up the data dictionary view rem of the current user's tables and generates a temporary rem SQL*Plus script called gcttmp.sql. This is then run rem to create the "cre_tabs.sql" script, which may be used rem as is or edited to adjust the storage parameters. rem The actual name of this output script may be chosen by rem the user at run-time. rem rem The large LINESIZE is to accomodate the comments. rem rem If the "compress extents" option is selected, the INITIAL rem extent size is based on the current size of the table, rem rounded up to the nearest 10Kb. rem rem This script does not currently work for tables which rem are in clusters.
rem
SET PAGES 0 FEEDBACK OFF RECSEP OFF ARRAYSIZE 1 MAXDATA 20000
SET VERIFY OFF LINESIZE 255
SET TERMOUT OFF
COLUMN dflt_out NEW_VALUE dflt_out
COLUMN out_prompt NEW_VALUE out_prompt
SELECT REPLACE(USER,'$','_')||'.tab' dflt_out,
LPAD('['||REPLACE(USER,'$','_')||'.tab]',18) out_prompt FROM
DUAL;
SET TERMOUT ON
ACCEPT Table_name PROMPT 'Enter the table (wild cards accepted)
[%%] : '
ACCEPT Quiet_Drop PROMPT 'Suppress drop non-existent table message? [Y]
: '
ACCEPT Output_File PROMPT 'Enter the output file name &out_prompt : '
ACCEPT Space_Info PROMPT 'Include storage information (Y/N) ? [Y]
: '
SET TERMOUT OFF
COLUMN cr NEWLINE
SPOOL gcttmp.sql
SELECT DECODE(UPPER('&Space_Info'),
'N', 'DEFINE Comp_Exts=N',
'SET TERMOUT ON'),
DECODE(UPPER('&Space_Info'),
'N', NULL,
'ACCEPT Comp_Exts PROMPT '||
'''Compress extents (Y/N) ? [Y] : ''') cr,
DECODE(UPPER('&Space_Info'),
'N', NULL,
'SET TERMOUT OFF') cr
FROM DUAL
/
SPOOL OFF
START gcttmp
HOST rm -f gcttmp.sql
DROP VIEW gct_view;
CREATE VIEW gct_view (table_name, space)
AS SELECT segment_name, SUM(bytes)
FROM user_extents
WHERE segment_type = 'TABLE'
AND segment_name LIKE UPPER(NVL('&Table_name','%'))
GROUP BY segment_name;
COLUMN out_file NEW_VALUE out_file
SELECT NVL('&Output_File', RTRIM('&dflt_out')) out_file
FROM DUAL
/
SPOOL gcttmp.sql
PROMPT COLUMN cname FORMAT a34
PROMPT COLUMN ctype FORMAT a15
PROMPT COLUMN cnull FORMAT a8
PROMPT SPOOL gcttmp.out
SELECT
'PROMPT PROMPT Table: '||ut.table_name,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'PROMPT SET TERMOUT OFF') cr,
'PROMPT DROP TABLE '||LOWER(ut.table_name)||';;' cr,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'PROMPT SET TERMOUT ON') cr,
'PROMPT CREATE TABLE '||LOWER(ut.table_name) cr,
'PROMPT (' cr,
'SELECT DECODE(column_id,1,'' '','' ,'') ||' ||
' LOWER(column_name) cname,' cr,
' data_type ||' cr,
' DECODE(data_type,' cr,
' ''CHAR'', ''(''||TO_CHAR(data_length) ||'')'',' cr,
' ''NUMBER'', DECODE(data_precision,' cr,
' NULL, '''',' cr,
' ''(''||TO_CHAR(data_precision)||' cr,
' DECODE(data_scale,' cr,
' NULL, '''',' cr,
' 0, '''',' cr,
' '',''||' cr,
' TO_CHAR(data_scale))||'
cr,
' '')''' cr,
' )' cr,
' ) ctype,' cr,
' DECODE(nullable,''N'', ''NOT NULL'') cnull' cr,
'FROM user_tab_columns' cr,
'WHERE table_name = '''||ut.table_name||'''' cr,
'ORDER BY table_name, column_id' cr,
'/' cr,
'PROMPT )' cr,
'SELECT ''PCTFREE '||TO_CHAR(ut.pct_free)||''',' cr,
' ''PCTUSED '||TO_CHAR(ut.pct_used)||''' cr,' cr,
' ''INITRANS '||TO_CHAR(ut.ini_trans)||''' cr,' cr,
' ''MAXTRANS '||TO_CHAR(ut.max_trans)||''' cr,' cr,
' ''TABLESPACE '||LOWER(ut.tablespace_name)||''' cr,' cr,
' ''STORAGE'' cr,' cr,
' ''('' cr,' cr,
' '' INITIAL '||
DECODE(UPPER(NVL('&Comp_Exts','Y')),
'Y', TO_CHAR((TRUNC((v.space-1)/10240)+1)*10240),
TO_CHAR(ut.initial_extent))||''' cr,' cr,
' '' NEXT '||TO_CHAR(ut.next_extent)||''' cr,' cr,
' '' MINEXTENTS '||TO_CHAR(ut.min_extents)||''' cr,' cr,
' '' MAXEXTENTS '||TO_CHAR(ut.max_extents)||''' cr,' cr,
' '' PCTINCREASE '||TO_CHAR(ut.pct_increase)||''' cr,' cr,
' '')'' cr' cr,
'FROM DUAL' cr,
'WHERE '''||NVL(UPPER('&Space_Info'),'Y')||''' = ''Y'';' cr,
'PROMPT /' cr,
'SELECT ''COMMENT ON TABLE '||LOWER(ut.table_name)||' IS'',' cr,
' ''''''''||REPLACE(comments, '''''''', '''''''''''')||
'''''''' cr,' cr,
' ''/'' cr' cr,
'FROM user_tab_comments' cr,
'WHERE table_name = '''||ut.table_name||'''' cr,
'AND comments IS NOT NULL' cr,
'/' cr,
'SELECT ''COMMENT ON COLUMN '||LOWER(ut.table_name)||
'.''||LOWER(column_name)||'' IS'',' cr,
' ''''''''||REPLACE(comments, '''''''', '''''''''''')||
'''''''' cr,' cr,
' ''/'' cr' cr,
'FROM user_col_comments' cr,
'WHERE table_name = '''||ut.table_name||'''' cr,
'AND comments IS NOT NULL' cr,
'/' cr
FROM user_tables ut,
gct_view v
WHERE ut.cluster_name IS NULL
AND ut.table_name = v.table_name
/
PROMPT SPOOL OFF
SPOOL OFF
START gcttmp
DROP VIEW gct_view;
rem Remove trailing blanks from output and rename to user's choice
HOST echo "SPOOL `basename &out_file .sql`.log" > &out_file HOST echo "SET DEFINE OFF" >> &out_file HOST echo "SET COMPATIBILITY V6" >> &out_fileHOST cat gcttmp.out | sed 's/ *$//g' >> &out_file HOST echo "SPOOL OFF" >> &out_file
rem Remove temporary files
HOST rm -f gcttmp.sql gcttmp.out
rem Restore default SQL*Plus settings, if installation uses them. rem PROMPT _at_?/dbs/glogin
SET TERMOUT ON
rem EXIT
rem MODULE REF : gc_view.sql rem PURPOSE : To generate a SQL script which can be used to rem recreate views based on currently existing ones. rem CREATION : 06-NOV-92 by J. Rudman, Oracle UKrem
SET COMPATIBILITY V6
SET TERMOUT OFF
COLUMN dflt_out NEW_VALUE dflt_out
COLUMN out_prompt NEW_VALUE out_prompt
SELECT USER||'.vew' dflt_out, LPAD('['||USER||'.vew]',18) out_prompt
FROM DUAL;
SET TERMOUT ON
ACCEPT View_Name PROMPT 'Enter the view name (wild cards accepted)
[%%] : '
ACCEPT Output_File PROMPT 'Enter the output file name &out_prompt : '
ACCEPT Quiet_Drop PROMPT 'Suppress drop non-existent view message? [Y]
: '
SET PAGES 0 FEEDBACK OFF RECSEP OFF ARRAYSIZE 1 VERIFY OFF PAUSE OFF
LONG 4096
SET TERMOUT OFF
COLUMN out_file NEW_VALUE out_file
SELECT NVL('&Output_File', RTRIM('&dflt_out')) out_file
FROM DUAL
/
SET TERMOUT OFF
COLUMN cr NEWLINE
SPOOL gcvtmp.sql
PROMPT PROMPT SET FEEDBACK ON TERMOUT ON
PROMPT SPOOL gcvtmp.out
SELECT
'PROMPT PROMPT View: '||view_name,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'PROMPT SET TERMOUT OFF') cr,
'PROMPT DROP VIEW '||LOWER(view_name)||';;' cr,
DECODE(UPPER('&Quiet_Drop'),
'N', NULL,
'PROMPT SET TERMOUT ON') cr,
'PROMPT CREATE VIEW '||LOWER(view_name) cr,
'PROMPT (' cr,
'SELECT DECODE(column_id,1,'' '','' ,'') || LOWER(column_name)' cr,
'FROM user_tab_columns' cr, 'WHERE table_name = '''||view_name||'''' cr, 'ORDER BY table_name, column_id;' cr, 'PROMPT )' cr, 'PROMPT AS' cr, 'SELECT text' cr, 'FROM user_views' cr, 'WHERE view_name = '''||view_name||''';' cr, 'PROMPT /' cr
FROM user_views
WHERE view_name LIKE UPPER(NVL('&View_Name','%')) /
PROMPT SPOOL OFF
SPOOL OFF
START gcvtmp
rem Remove trailing blanks from output and rename to user's choice
HOST echo "SPOOL `basename &out_file .sql`.log" > &out_file HOST echo "SET DEFINE OFF" >> &out_file HOST echo "SET COMPATIBILITY V6" >> &out_fileHOST cat gcvtmp.out | sed 's/ *$//g' >> &out_file HOST echo "SPOOL OFF" >> &out_file
rem Remove temporary files
HOST rm -f gcvtmp.out gcvtmp.sql
rem Restore default SQL*Plus settings, if installation uses them. SET PAGES 14 FEEDBACK ON RECSEP WRAPPED ARRAYSIZE 20 VERIFY ON PAUSE OFF LONG 80
rem _at_?/dbs/glogin
SET TERMOUT ON
rem EXIT Received on Fri Aug 01 1997 - 00:00:00 CEST