REM
REM DBAToolZ NOTE:
REM This script was obtained from DBAToolZ.com
REM It's configured to work with SQL Directory (SQLDIR).
REM SQLDIR is a utility that allows easy organization and
REM execution of SQL*Plus scripts using user-friendly menu.
REM Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM c_tab_rec_plsql_all.sql
REM
REM REVERSE TAB SEGMENT
REM
REM Author:
REM Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM
REM Purpose:
REM
REM Reverse Engineer Table DDL including
REM constraints. Allows tablespace change for
REM INDEXES generated by constraints and the
REM TABLE it self. Doesn't handle partitions.
REM
REM
REM Usage:
REM c_tab_rec_plsql_all.sql
REM
REM Example:
REM c_tab_rec_plsql_all.sql
REM
REM
REM History:
REM 08-01-1998 VMOGILEV Created
REM
REM
set serveroutput on size 100000
set verify off
set feedback off
prompt ==================================================================
prompt = This Script Will Generate Create Table Script =
prompt = and will allow to specify INDEX and DATA tablespace for Tables =
prompt ==================================================================
prompt
prompt
accept shema_owner char prompt "Enter Shema Owner Name [Enter For All]:"
accept tab_name char prompt "Enter Table Name [Enter For All]:"
accept index_tabsp char prompt "Enter Tablespace Name For Indexes [Enter For None]:"
accept data_tabsp char prompt "Enter Tablespace Name For Tables [Enter For None]:"
accept table_suffix char prompt "Enter Suffix For New Tables [Enter For None]:"
spool rec_tables.tmp
declare
cursor tab_name_cursor is
select owner
, table_name
, tablespace_name
, pct_free
, pct_used
, 'Storage (Initial '||initial_extent||' Next '||next_extent||' Pctincrease '||pct_increase||')' storage_par
, 'Parallel (Degree '||ltrim(degree)||' Instances '||ltrim(instances)||') '||decode(rtrim(ltrim(cache)),
'Y','CACHE'
,'NOCACHE') parallel_par
from dba_tables
where owner = nvl(upper('&shema_owner'), owner)
and table_name = nvl(upper('&tab_name'), table_name);
cursor tab_col_cursor (in_owner VARCHAR2, in_table_name VARCHAR2) is
select cl.column_name col_name
, decode(cl.data_type,
'CHAR','CHAR('||cl.data_length||')',
'VARCHAR','VARCHAR('||cl.data_length||')',
'VARCHAR2','VARCHAR2('||cl.data_length||')',
'NUMBER',decode(cl.data_precision,
NULL,'NUMBER'
,'NUMBER('||cl.data_precision||','||cl.data_scale||')')
,cl.data_type) d_type
, decode(cl.nullable,
'Y','NULL',
'N','NOT NULL') nn_or_n
from dba_tab_columns cl
where cl.owner = in_owner
and cl.table_name = in_table_name
order by cl.column_id;
cursor tab_cons_all_cursor (in_owner VARCHAR2, in_table_name VARCHAR2) is
select constraint_name cons_name
from dba_constraints
where owner = in_owner
and table_name = in_table_name
and constraint_type in ('P','R');
cursor tab_cons_cursor (in_constraint_name VARCHAR2) is
select cn.constraint_name||'&table_suffix' cons_name
, cn.constraint_name cons_name_orig
, decode(cn.constraint_type,
'P','Primary Key (',
'R','Foreign Key (') cons_type
, cn.r_owner||'.'||cn_r.table_name||decode(cn.delete_rule,
'CASCADE',' On Delete Cascade') cons_clause
from dba_constraints cn
, dba_constraints cn_r
where cn.constraint_name = in_constraint_name
and cn.r_constraint_name = cn_r.constraint_name(+);
cursor tab_cons_columns_cursor (in_constraint_name VARCHAR2) is
select cc.column_name||decode(cc.position,
max(cc_m.position),' )'
,', ') col_name
from dba_cons_columns cc
, dba_cons_columns cc_m
where cc.constraint_name = in_constraint_name
and cc_m.constraint_name = in_constraint_name
group by cc.constraint_name
, cc.column_name
, cc.position
order by cc.constraint_name, cc.position;
cnt_col NUMBER(10);
cnt_cons NUMBER(10);
real_col NUMBER(10);
real_cons NUMBER(10);
data_tabsp_name VARCHAR2(40);
index_tabsp_name VARCHAR2(40);
type_cons VARCHAR2(1);
ind_pct_free sys.dba_indexes.pct_free%TYPE;
ind_initial_extent sys.dba_indexes.initial_extent%TYPE;
ind_next_extent sys.dba_indexes.next_extent%TYPE;
ind_pct_increase sys.dba_indexes.pct_increase%TYPE;
begin
for tab_name_rec in tab_name_cursor
loop
cnt_col := 0;
dbms_output.put_line('-- ');
dbms_output.put_line('create table '||tab_name_rec.owner||'.'||tab_name_rec.table_name||'&table_suffix (');
for tab_col_rec in tab_col_cursor(tab_name_rec.owner, tab_name_rec.table_name)
loop
cnt_col := cnt_col +1;
select count(column_name) into real_col
from dba_tab_columns
where owner = tab_name_rec.owner
and table_name = tab_name_rec.table_name;
select count(constraint_name) into real_cons
from dba_constraints
where owner = tab_name_rec.owner
and table_name = tab_name_rec.table_name
and constraint_type in ('P','R');
-- dbms_output.put_line('Real_cons ='||real_cons||'');
-- dbms_output.put_line('Real_col ='||real_col||'');
if real_col > cnt_col then
dbms_output.put_line(' '||rpad(tab_col_rec.col_name,40)||rpad(tab_col_rec.d_type,20)||tab_col_rec.nn_or_n||',');
elsif real_col = cnt_col and real_cons = 0 then
dbms_output.put_line(' '||rpad(tab_col_rec.col_name,40)||rpad(tab_col_rec.d_type,20)||tab_col_rec.nn_or_n||')');
elsif real_col = cnt_col and real_cons <> 0 then
dbms_output.put_line(' '||rpad(tab_col_rec.col_name,40)||rpad(tab_col_rec.d_type,20)||tab_col_rec.nn_or_n||',');
end if;
end loop;
cnt_cons := 0;
for tab_cons_all_rec in tab_cons_all_cursor(tab_name_rec.owner, tab_name_rec.table_name)
loop
for tab_cons_rec in tab_cons_cursor(tab_cons_all_rec.cons_name)
loop
dbms_output.put('Constraint '||tab_cons_rec.cons_name||' '||tab_cons_rec.cons_type||' ');
for tab_cons_columns_rec in tab_cons_columns_cursor(tab_cons_rec.cons_name_orig)
loop
dbms_output.put(''||tab_cons_columns_rec.col_name||' ');
end loop;
dbms_output.new_line;
select count(constraint_name) into real_cons
from dba_constraints
where owner = tab_name_rec.owner
and table_name = tab_name_rec.table_name
and constraint_type in ('P','R');
select constraint_type into type_cons
from dba_constraints
where tab_cons_rec.cons_name_orig = constraint_name;
select nvl(upper('&index_tabsp'),'NO_NAME') into index_tabsp_name
from dual;
if index_tabsp_name = 'NO_NAME' and type_cons = 'P' then
select tablespace_name, pct_free,
initial_extent, next_extent,
pct_increase
into index_tabsp_name, ind_pct_free,
ind_initial_extent, ind_next_extent,
ind_pct_increase
from dba_indexes
where tab_cons_rec.cons_name_orig = index_name;
elsif index_tabsp_name <> 'NO_NAME' and type_cons = 'P' then
select pct_free,
initial_extent, next_extent,
pct_increase
into ind_pct_free,
ind_initial_extent, ind_next_extent,
ind_pct_increase
from dba_indexes
where tab_cons_rec.cons_name_orig = index_name;
else
NULL;
end if;
cnt_cons := cnt_cons + 1;
if real_cons > cnt_cons and type_cons = 'P' then
dbms_output.put_line('Using Index Tablespace '||index_tabsp_name||' PctFree '||ind_pct_free||'');
dbms_output.put_line('Storage (Initial '||ind_initial_extent||' Next '||ind_next_extent||' PctIncrease '||ind_pct_increase||'),');
elsif real_cons = cnt_cons and type_cons = 'P' then
dbms_output.put_line('Using Index Tablespace '||index_tabsp_name||' PctFree '||ind_pct_free||'');
dbms_output.put_line('Storage (Initial '||ind_initial_extent||' Next '||ind_next_extent||' PctIncrease '||ind_pct_increase||'))');
elsif real_cons > cnt_cons and type_cons = 'R' then
dbms_output.put_line('Referencing '||tab_cons_rec.cons_clause||',');
elsif real_cons = cnt_cons and type_cons = 'R' then
dbms_output.put_line('Referencing '||tab_cons_rec.cons_clause||')');
end if;
end loop;
end loop;
select nvl(upper('&data_tabsp'),'NO_NAME') into data_tabsp_name
from dual;
if data_tabsp_name = 'NO_NAME' then
dbms_output.put_line('Tablespace '||tab_name_rec.tablespace_name||' Pctused '||tab_name_rec.pct_used||' Pctfree '||tab_name_rec.pct_free||' ');
else
dbms_output.put_line('Tablespace '||data_tabsp_name||' Pctused '||tab_name_rec.pct_used||' Pctfree '||tab_name_rec.pct_free||' ');
end if;
dbms_output.put_line(' '||tab_name_rec.storage_par||' ');
dbms_output.put_line(' '||tab_name_rec.parallel_par||' ');
dbms_output.put_line(' / ');
end loop;
end;
/
spool off
prompt >>>>>>>>> Created file_name = rec_tables.tmp