REM  #*****************************************************
REM  #File Name: get_tables.sql
REM  #
REM  #Purpose:   Script to dump table creation script 
REM  #           for the username (schema) provided as
REM  #           the parameter.
REM  #
REM  #           This is script is useful for cases where
REM  #           Reverse Engineering is required. The resulting
REM  #           SQL is sent to an output file: 
REM  #           
REM  #                   tbl_<SCHEMA_NAME>.lst
REM  #
REM  #*****************************************************

set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool tbl_&&SCHEMA_NAME
set termout off
col x noprint
col y noprint
select  'rem   ****    Create Table DDL for '||chr(10)||
	'rem   ****    '||username||''''||'s tables'||chr(10)||chr(10)
from    dba_users 
where     username      = upper ('&&1')
/
select  table_name y,
	0 x,
	'create table ' ||
	rtrim(table_name) ||
	'('
from    dba_tables
where     owner = upper('&&1')
union
select  tc.table_name y,
	column_id x,
	rtrim(decode(column_id,1,null,','))||
	rtrim(column_name)|| ' ' ||
	rtrim(data_type) ||
	rtrim(decode(data_type,'DATE',null,'LONG',null,
	       'NUMBER',decode(to_char(data_precision),null,null,'('),
	       '(')) ||
	rtrim(decode(data_type,
	       'DATE',null,
	       'CHAR',data_length,
	       'VARCHAR2',data_length,
	       'NUMBER',decode(to_char(data_precision),null,null,
		 to_char(data_precision) || ',' || to_char(data_scale)),
	       'LONG',null,
	       '******ERROR')) ||
	rtrim(decode(data_type,'DATE',null,'LONG',null,
	       'NUMBER',decode(to_char(data_precision),null,null,')'),
	       ')')) || ' ' ||
	rtrim(decode(nullable,'N','NOT NULL',null))
from    dba_tab_columns tc,
	dba_objects o
where   o.owner = tc.owner
and     o.object_name = tc.table_name
and     o.object_type = 'TABLE'
and     o.owner = upper('&&1')
union
select  table_name y,
	999999 x,
	')'  || chr(10)
	||' STORAGE('                           || chr(10)
	||' INITIAL '    || initial_extent      || chr(10)
	||' NEXT '       || next_extent         || chr(10)
	||' MINEXTENTS ' || min_extents         || chr(10)
	||' MAXEXTENTS ' || max_extents         || chr(10) 
	||' PCTINCREASE '|| pct_increase        || ')' ||chr(10) 
	||' INITRANS '   || ini_trans         || chr(10)
	||' MAXTRANS '   || max_trans         || chr(10)
	||' PCTFREE '    || pct_free          || chr(10)
	||' PCTUSED '    || pct_used          || chr(10)
	||' PARALLEL (DEGREE ' || DEGREE || ') ' || chr(10)
	||' TABLESPACE ' || rtrim(tablespace_name) ||chr(10)
	||'/'||chr(10)||chr(10)
from    dba_tables
where   owner = upper('&&1')
order by 1,2
/
spool off