Hello

set verify off
set feedback off
set echo off long 5000
set pagesize 0 head off lines 1000
set termout on trimspool on
set serveroutput on size 1000000

Prompt
Prompt This script generates another script that will include all the CREATE
Prompt TABLE statements for those in the database (except for the SYS user).
Prompt (NB ensure screen buffer set to 1000 to avoid truncating long lines)
Prompt The dbms_output package must be available. Script runs for user accessible tables
Prompt only (change USER_/ALL_ to DBA_ if necessary)
Promp

clear columns
Accept Table_name Prompt 'Enter Table name to reverse engineer: '
Accept owner Prompt 'Enter owmer of Tables to reverse engineer: '
Accept add_owner Prompt 'Enter Y to prefix schema/user name to tablename, N if not: '
Accept storage Prompt 'Enter Y to add storage clause, N if not: '

rem
rem ***************************************************************************
rem ***** Set the following usesegs flag to Y to use the total number of bytes
rem ***** that the segment is currently using (instead of the table's
rem ***** originally-specified INITIAL_EXTENT value) as the value to use for
rem ***** the INITIAL_EXTENT in the script file that this script creates, so
rem ***** that the table will be recreated in a single extent. (The NEXT_EXTENT
rem ***** will be limited to no more than the INITIAL_EXTENT value.)
rem ***** (Set to 'N' to use the originally-specified INITIAL_EXTENT value).
rem ***************************************************************************
rem
def usesegs='N'

Set termout off
Spool RE.tab

declare
cursor tab_cursor is select
upper(owner),
upper(table_name),
pct_free,
pct_used,
ini_trans,
max_trans,
tablespace_name,
initial_extent,
next_extent,
min_extents,
max_extents,
freelists,
freelist_groups,
pct_increase
from all_tables
where owner != 'SYS'
AND owner LIKE UPPER('&&owner')
AND table_name LIKE UPPER('&&Table_name')
order by owner, table_name;
cursor segments_cursor (s_own VARCHAR2, s_tab VARCHAR2) is select
bytes
from USER_segments
where segment_name = s_tab and --owner = s_own and
segment_type = 'TABLE';
cursor col_cursor (c_own VARCHAR2, c_tab VARCHAR2) is select
owner,
upper(column_name),
upper(data_type),
data_length,
data_precision,
data_scale,
nullable,
default_length,
data_default,
column_id
from all_tab_columns
where table_name = c_tab and owner = c_own
order by column_id;
cursor pk_cursor (c_own VARCHAR2, c_tab VARCHAR2) is select
owner,
upper(constraint_name) constr_name,
DECODE(constraint_type,'C','CHECK','P','PRIMARY KEY','R','FOREIGN KEY',constraint_type) constr_type,
search_condition search_cond,
R_owner,
R_constraint_name
from all_constraints
where table_name = c_tab and owner = c_own
order by constraint_name;
cursor pkcol_cursor (c_own VARCHAR2, c_tab VARCHAR2, c_pk varchar2) is select
column_name
from all_cons_COLUMNS
where table_name = c_tab and owner = c_own and constraint_name = c_pk
order by position;

cursor CU_pk_tabl_name (c_own VARCHAR2, c_PK VARCHAR2) is select
table_name
from all_constraints
where constraint_name = c_pk and owner = c_own;


L_storage CHAR(1) DEFAULT '&&storage';
L_add_owner CHAR(1) DEFAULT '&&add_owner';
lv_constr_name all_constraints.constraint_name%TYPE;
lv_R_constr_name all_constraints.R_constraint_name%TYPE;
lv_R_owner all_constraints.R_owner%TYPE;
lv_constr_type VARCHAR2(100);
lv_search_cond VARCHAR2(32000);
lv_owner all_tables.owner%TYPE;
lv_table_name all_tables.table_name%TYPE;
lv_r_tabl_name all_tables.table_name%TYPE;
lv_pct_free all_tables.pct_free%TYPE;
lv_pct_used all_tables.pct_used%TYPE;
lv_ini_trans all_tables.ini_trans%TYPE;
lv_max_trans all_tables.max_trans%TYPE;
lv_tablespace_name all_tables.tablespace_name%TYPE;
lv_initial_extent all_tables.initial_extent%TYPE;
lv_next_extent all_tables.next_extent%TYPE;
lv_min_extents all_tables.min_extents%TYPE;
lv_max_extents all_tables.max_extents%TYPE;
lv_freelists all_tables.freelists%TYPE;
lv_freelist_groups all_tables.freelist_groups%TYPE;
lv_pct_increase all_tables.pct_increase%TYPE;
segment_bytes USER_segments.bytes%TYPE;
lv_column_name all_tab_columns.column_name%TYPE;
lv_data_type all_tab_columns.data_type%TYPE;
lv_data_length all_tab_columns.data_length%TYPE;
lv_data_precision all_tab_columns.data_precision%TYPE;
lv_data_scale all_tab_columns.data_scale%TYPE;
lv_nullable all_tab_columns.nullable%TYPE;
lv_default_length all_tab_columns.default_length%TYPE;
lv_data_default all_tab_columns.data_default%TYPE;
lv_column_id all_tab_columns.column_id%TYPE;
lv_lineno number := 0;
initial_extent_size varchar2(16);
next_extent_size varchar2(16);
a_lin varchar2(80);

function wri(x_lin in varchar2, x_str in varchar2,
x_force in number) return varchar2 is
L_force NUMBER;
begin
--L_force:=1;
--DBMS_OUTPUT.PUT_LINE('Line #'||lv_lineno);
if length(x_lin) + length(x_str) > 80
then
lv_lineno := lv_lineno + 1;
-- insert into tabl_temp values (lv_lineno, x_lin);
DBMS_OUTPUT.PUT_LINE(x_lin);
if x_force = 0
then
return x_str;
else
lv_lineno := lv_lineno + 1;
-- insert into tabl_temp values (lv_lineno, x_str);
DBMS_OUTPUT.PUT_LINE(x_str);
return '';
end if;
else
if x_force = 0
then
return x_lin||x_str;
else
lv_lineno := lv_lineno + 1;
-- insert into tabl_temp values (
-- lv_lineno, x_lin||x_str);
DBMS_OUTPUT.PUT_LINE(x_lin||x_str);
return '';
end if;
end if;
end wri;
--
PROCEDURE PR_get_constr_col (lv_owner VARCHAR2, lv_table_name VARCHAR2, lv_constr_name VARCHAR2)
IS
BEGIN
open PKcol_cursor(lv_owner, lv_table_name, lv_constr_name);
loop
fetch pkcol_cursor into
lv_column_name;
exit when pkcol_cursor%notfound;
a_lin := wri(a_lin,' '||RPAD(' ',LENGTH(lv_constr_type)+1)||lv_column_name,1);
end loop;
close pkcol_cursor;
END PR_get_constr_col;
--
begin
a_lin := '';
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_freelists,
lv_freelist_groups,
lv_pct_increase;
exit when tab_cursor%notfound;

if '&&usesegs' = 'Y' then
open segments_cursor (lv_owner, lv_table_name);
fetch segments_cursor into segment_bytes;
if segments_cursor%found then
lv_initial_extent := segment_bytes;
if lv_next_extent > lv_initial_extent then
lv_next_extent := lv_initial_extent;
end if;
end if;
close segments_cursor;
end if;
a_lin := wri(a_lin, 'create table ', 0);
IF L_add_owner = 'Y' THEN
a_lin := wri(a_lin, lv_owner || '.' || lv_table_name, 0);
ELSE
a_lin := wri(a_lin, lv_table_name, 0);
END IF;
a_lin := wri(a_lin, ' (', 1);
if (to_char(lv_ini_trans) = '0') then
lv_ini_trans := 1;
end if;
if (to_char(lv_max_trans) = '0') then
lv_max_trans := 1;
end if;
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,
lv_default_length,
lv_data_default,
lv_column_id;
exit when col_cursor%notfound;
if lv_column_id <> 1 then
a_lin := wri(a_lin, ' ,', 0);
else
a_lin := wri(a_lin, ' ', 0);
end if;
a_lin := wri(a_lin,
LOWER(lv_column_name), 0);
a_lin := wri(a_lin, ' ' || lv_data_type, 0);
if lv_data_type = 'CHAR' or lv_data_type = 'VARCHAR2' or
lv_data_type = 'RAW'
then
a_lin := wri(a_lin,
'(' || lv_data_length || ')', 0);
end if;
if (lv_data_type = 'NUMBER' and
nvl(lv_data_precision, 0) != 0) or
lv_data_type = 'FLOAT'
then
if nvl(lv_data_scale, 0) = 0 then
a_lin := wri(a_lin,
'(' || lv_data_precision || ')',
0);
else
a_lin := wri(a_lin,
'(' || lv_data_precision ||
',' || lv_data_scale || ')', 0);
end if;
end if;
if lv_default_length != 0 then
if lv_default_length < 80 then
a_lin := wri(a_lin, ' DEFAULT ', 0);
a_lin := wri(a_lin, lv_data_default, 0);
else
dbms_output.put_line(
'Skipping default clause on ' ||
'column ' || lv_column_name);
dbms_output.put_line(
' on table ' || lv_table_name);
dbms_output.put_line(
' since length is ' ||
to_char(lv_default_length));
end if;
end if;
if lv_nullable = 'N' then
a_lin := wri(a_lin, ' NOT NULL', 1);
ELSE
a_lin := wri(a_lin, ' NULL', 1);
end if;
end loop;
close col_cursor;
a_lin := wri(a_lin, ')', 1);
a_lin := wri(a_lin, ' PCTFREE ' || to_char(lv_pct_free), 1);
a_lin := wri(a_lin, ' PCTUSED ' || to_char(lv_pct_used), 1);
a_lin := wri(a_lin, ' INITRANS ' || to_char(lv_ini_trans), 1);
a_lin := wri(a_lin, ' MAXTRANS ' || to_char(lv_max_trans), 1);
--
IF L_storage = 'Y' THEN
a_lin := wri(a_lin, ' TABLESPACE ' || lv_tablespace_name, 1);
a_lin := wri(a_lin, ' STORAGE (', 1);
/* Calculate extent sizes in Mbytes or Kbytes, if possible */
if mod(lv_initial_extent, 1048576) = 0 then
initial_extent_size :=
to_char(lv_initial_extent / 1048576) || 'M';
elsif mod(lv_initial_extent, 1024) = 0 then
initial_extent_size :=
to_char(lv_initial_extent / 1024) || 'K';
else
initial_extent_size := to_char(lv_initial_extent);
end if;
if mod(lv_next_extent, 1048576) = 0 then
next_extent_size :=
to_char(lv_next_extent / 1048576) || 'M';
elsif mod(lv_next_extent, 1024) = 0 then
next_extent_size :=
to_char(lv_next_extent / 1024) || 'K';
else
next_extent_size := to_char(lv_next_extent);
end if;
a_lin := wri(a_lin, ' INITIAL ' || initial_extent_size, 1);
a_lin := wri(a_lin, ' NEXT ' || next_extent_size, 1);
a_lin := wri(a_lin, ' MINEXTENTS ' || to_char(lv_min_extents),
1);
a_lin := wri(a_lin, ' MAXEXTENTS ' || to_char(lv_max_extents),
1);
a_lin := wri(a_lin, ' PCTINCREASE ' || to_char(lv_pct_increase),
1);
a_lin := wri(a_lin, ' FREELISTS ' || to_char(lv_freelists), 0);
a_lin := wri(a_lin, ' FREELIST GROUPS ' ||
to_char(lv_freelist_groups), 1);
a_lin := wri(a_lin, ' )', 1);
END IF;
a_lin := wri(a_lin, ' ;', 1); end loop;
close tab_cursor;

exception
when others then
rollback;
raise_application_error(-20000,
'Unexpected error on ' || lv_table_name ||
', ' || lv_column_name || ': ' ||
to_char(SQLCODE) || ' - Aborting...');
end;
/

Spool Off
undef table_name
undef owner

Prompt
Prompt You now have a script called RE.Tab to use as required
Prompt

set verify on
set feedback on
set pagesize 30 head on lines 100
set termout on trimspool on
set serveroutput off

regards
hrishy