Re: database re-creation scripts
From: Cam White <info_at_revealnet.com>
Date: 1997/10/27
Message-ID: <01bce2e6$5f6a78e0$5b73b1cd_at_Preveal2>
REM Only preliminary testing of this script was performed. Be sure to test REM it completely before relying on it.
REM set verify off
rem set feedback off
rem set termout off
rem set echo off;
set pagesize 0
/
end;
CLOSE tab_cursor;
END;
/
> build it up on a laptop using Personal Oracle. Somehow I've lost the > original scripts and now am struggling to recover from this. I've spoken to
Date: 1997/10/27
Message-ID: <01bce2e6$5f6a78e0$5b73b1cd_at_Preveal2>
Below you will find a script from RevealNet's Oracle Administration
Knowledge Base.
This product covers almost 2,000 technical topics for DBAs, including 200
scripts.
A demo can be downloaded from http://www.revealnet.com
Best wishes,
Cam White
RevealNet, Inc.
(202) 234-8557
REM
REM tab_rct.sql
REM
REM FUNCTION: SCRIPT FOR CREATING TABLES
REM
REM This script can be run by any user .
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 owner by the user in the database. This created REM script, create_table.sql, can be run by any user with the REM 'CREATE TABLE' system 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
rem set feedback off
rem set termout off
rem set echo off;
set pagesize 0
set termout on
select 'Creating table build script...' from dual;
rem set termout off
create table t_temp
(lineno NUMBER, tb_owner VARCHAR2(30), tb_name VARCHAR2(30),
text VARCHAR2(2000))
/
DECLARE
CURSOR tab_cursor IS select table_name,
pct_free,
pct_used,
ini_trans,
max_trans,
tablespace_name,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
freelists,
freelist_groups
from user_tables
order by table_name;
CURSOR col_cursor (c_tab VARCHAR2) IS select
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
from user_tab_columns
where table_name = c_tab
order by column_id;
lv_table_name user_tables.table_name%TYPE;
lv_pct_free user_tables.pct_free%TYPE;
lv_pct_used user_tables.pct_used%TYPE;
lv_ini_trans user_tables.ini_trans%TYPE;
lv_max_trans user_tables.max_trans%TYPE;
lv_tablespace_name user_tables.tablespace_name%TYPE;
lv_initial_extent user_tables.initial_extent%TYPE;
lv_next_extent user_tables.next_extent%TYPE;
lv_min_extents user_tables.min_extents%TYPE;
lv_max_extents user_tables.max_extents%TYPE;
lv_pct_increase user_tables.pct_increase%TYPE;
lv_column_name user_tab_columns.column_name%TYPE;
lv_data_type user_tab_columns.data_type%TYPE;
lv_data_length user_tab_columns.data_length%TYPE;
lv_data_precision user_tab_columns.data_precision%TYPE;
lv_data_scale user_tab_columns.data_scale%TYPE;
lv_nullable user_tab_columns.nullable%TYPE;
lv_freelists user_tables.freelists%TYPE;
lv_freelist_groups user_tables.freelist_groups%TYPE;
lv_first_rec BOOLEAN;
lv_lineno number := 0;
lv_string VARCHAR2(2000);
nul_cnt number;
procedure write_out(p_line INTEGER, p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into t_temp (lineno, tb_name, text)
values (p_line,p_name,p_string);
end;
BEGIN
OPEN tab_cursor;
LOOP
FETCH tab_cursor INTO 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,
lv_freelists,
lv_freelist_groups;
EXIT WHEN tab_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := 'DROP TABLE '|| lower(lv_table_name)||';';
write_out(lv_lineno, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_first_rec := TRUE;
lv_string := 'CREATE TABLE '|| lower(lv_table_name)||' (';
write_out(lv_lineno, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := null;
OPEN col_cursor(lv_table_name);
nul_cnt:=0;
LOOP
FETCH col_cursor INTO 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;
if ((lv_data_type = 'NUMBER') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||','||
nvl(lv_data_scale,0)||')';
elsif ((lv_data_type = 'FLOAT') and (lv_data_precision>0)) then
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type ||'('||lv_data_precision||')';
else
lv_string := lv_string || lower(lv_column_name) ||
' ' || lv_data_type;
end if;
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
nul_cnt:=nul_cnt+1;
lv_string := lv_string || ' constraint
ck_'||lv_table_name||'_'||nul_cnt||' NOT NULL';
end if;
write_out(lv_lineno, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
END LOOP;
CLOSE col_cursor;
lv_string := ')';
write_out(lv_lineno, 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_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_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TABLESPACE ' || lv_tablespace_name;
write_out(lv_lineno, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'STORAGE (';
write_out(lv_lineno, 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_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'FREELISTS ' || to_char(lv_freelists) ||
' FREELIST GROUPS ' || to_char(lv_max_trans);
write_out(lv_lineno, 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_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := '/';
write_out(lv_lineno, lv_table_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_table_name, lv_string);
END LOOP;
CLOSE tab_cursor;
END;
/
set heading off
spool rep_out\crt_tabs.sql
select text
from T_temp
order by tb_name, lineno;
spool off
drop table t_temp;
set verify on
rem set feedback on
rem set termout on
set echo on
set pagesize 22
dechaine <dechaine_at_MCIONE.com> wrote in article <01bce152$cc54ab00$a4232299_at_frank>...
> Good day everyone, > > I am trying to re-create my database's table creation scripts so that Ican
> build it up on a laptop using Personal Oracle. Somehow I've lost the > original scripts and now am struggling to recover from this. I've spoken to
> several people who say that it is possible to recreate these scripts via > sql's but I am not proficient enough to figure it out myself .. I do not > have developer or designer software so this is not an option. Doesanyone
[Quoted] > out there have any ideas or suggestions? > -- > Frank >Received on Mon Oct 27 1997 - 00:00:00 CET