Re: Script from schema
Date: 1996/03/15
Message-ID: <31491924.3BCE_at_randomc.com.usa>
Hans-Peter Schreiter wrote:
>
> Hi,
> does anybody have an easy way to get a script for re-building the
> scheme of a users tables out of the existing scheme?? Is there another
> way than to scratch out the stuff from the export file. I am using
> Oracle Server 7.0.16 on HP-UX.
>
> Any pointers are welcome
> Hans-Peter Schreiter
>
> --
> Hans-Peter Schreiter
> Condat GmbH Telephone: +49.30.39094-149
> Alt-Moabit 91D Fax: +49.30.39094-300
> 10559 Berlin, Germany E-Mail: hps_at_condat.de
The following script create table creating script from Oracle data Dictionary. I got this script from one of my colleage but not tested yet. Use the script at your own risk. You may modify the script or write dynamic SQL Plus script for a whole user's tables.
Joseph.
jsuh_at_randomc.com.usa
REM
REM
REM
REM FUNCTION: SCRIPT FOR CREATING INDIVIDUAL TABLES
REM
REM This script must be run by a user with the DBA role.
REM
REM This script is intended to run with Oracle7.
REM
REM Running this script will in turn create a script to REM build all the tables in the database. This created REM script, create_table.sql, can be run by any user with REM the DBA role or with the 'CREATE ANY TABLE' system REM privilege. REM REM NOTE: The script will NOT include constraints on tables. This REM script will also NOT capture tables created by user 'SYS'.REM
REM Only preliminary testing of this script was performed. Be sure to test
REM it completely before relying on it.
REM set verify off
set feedback off
set termout off
set echo off;
set pagesize 0
set termout on
select 'Creating table build script...' from dual;
set termout off
create table t_temp
(lineno NUMBER, tb_owner VARCHAR2(30), tb_name VARCHAR2(30),
text VARCHAR2(255))
tablespace temp01
/
DECLARE
CURSOR tab_cursor IS select owner,
table_name, pct_free, pct_used, ini_trans, max_trans, tablespace_name, initial_extent, next_extent, min_extents, max_extents, pct_increase from sys.dba_tables where owner != 'SYS' order by owner, table_name; CURSOR col_cursor (c_own VARCHAR2, c_tab VARCHAR2) IS select owner, column_name, data_type, data_length, data_precision, data_scale, nullable from sys.dba_tab_columns where table_name = c_tab and owner=c_own order by column_name; lv_owner sys.dba_tables.owner%TYPE; lv_table_name sys.dba_tables.table_name%TYPE; lv_pct_free sys.dba_tables.pct_free%TYPE; lv_pct_used sys.dba_tables.pct_used%TYPE; lv_ini_trans sys.dba_tables.ini_trans%TYPE; lv_max_trans sys.dba_tables.max_trans%TYPE; lv_tablespace_name sys.dba_tables.tablespace_name%TYPE; lv_initial_extent sys.dba_tables.initial_extent%TYPE; lv_next_extent sys.dba_tables.next_extent%TYPE; lv_min_extents sys.dba_tables.min_extents%TYPE; lv_max_extents sys.dba_tables.max_extents%TYPE; lv_pct_increase sys.dba_tables.pct_increase%TYPE; lv_column_name sys.dba_tab_columns.column_name%TYPE; lv_data_type sys.dba_tab_columns.data_type%TYPE; lv_data_length sys.dba_tab_columns.data_length%TYPE; lv_data_precision sys.dba_tab_columns.data_precision%TYPE; lv_data_scale sys.dba_tab_columns.data_scale%TYPE; lv_nullable sys.dba_tab_columns.nullable%TYPE; lv_first_rec BOOLEAN; lv_lineno number := 0; lv_string VARCHAR2(80);
procedure write_out(p_line INTEGER, p_owner varchar2, p_name
VARCHAR2,
p_string VARCHAR2) is
begin
insert into t_temp (lineno, tb_owner, tb_name, text)
values (p_line,p_owner,p_name,p_string);
end;
BEGIN
OPEN tab_cursor;
LOOP
FETCH tab_cursor INTO lv_owner,
lv_table_name,
lv_pct_free,
lv_pct_used,
lv_ini_trans,
lv_max_trans,
lv_tablespace_name,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase;
EXIT WHEN tab_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := 'CREATE TABLE '|| lv_owner || '.' ||
lower(lv_table_name)||' (';
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_first_rec := TRUE;
lv_string := null;
OPEN col_cursor(lv_owner,lv_table_name);
LOOP
FETCH col_cursor INTO lv_owner,
lv_column_name,
lv_data_type,
lv_data_length,
lv_data_precision,
lv_data_scale,
lv_nullable;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
else
lv_string := ',';
end if;
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type;
if ((lv_data_type = 'CHAR') or (lv_data_type = 'VARCHAR2'))
then
lv_string := lv_string || '(' || lv_data_length || ')';
end if;
if (lv_nullable = 'N') then
lv_string := lv_string || ' NOT NULL';
end if;
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
END LOOP;
CLOSE col_cursor;
lv_string := ')';
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := null;
lv_string := 'PCTFREE ' || to_char(lv_pct_free) ||
' PCTUSED ' || to_char(lv_pct_used);
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITRANS ' || to_char(lv_ini_trans) ||
' MAXTRANS ' || to_char(lv_max_trans);
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TABLESPACE ' || lv_tablespace_name;
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'STORAGE (';
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITIAL ' || to_char(lv_initial_extent) ||
' NEXT ' || to_char(lv_next_extent);
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'MINEXTENTS ' || to_char(lv_min_extents) ||
' MAXEXTENTS ' || to_char(lv_max_extents) ||
' PCTINCREASE ' || to_char(lv_pct_increase) || ')';
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := '/';
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_owner, lv_table_name, lv_string);
END LOOP;
CLOSE tab_cursor;
END;
/
set heading off
spool report_output/create_tables.sql
select text
from T_temp
order by tb_owner, tb_name, lineno;
spool off
drop table t_temp;
exit Received on Fri Mar 15 1996 - 00:00:00 CET