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.sql
REM
REM <SQLDIR_GRP>UTIL TAB REVERSE SEGMENT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Creates DDL script to re-create a table
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	c_tab_rec.sql
REM 
REM Example:
REM	c_tab_rec.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set term on
set feedback on
set verify off
set pages 999
set head off
set lines 132
col col_name format a35
col table_name noprint new_value m_table_name
col type format a15
col nn_or_n format a11

break on table_name skip page
ttitle  -
         m_table_name -

accept shema_owner char prompt "Enter Shema Owner Name [Enter For All]:"
accept tab_name char prompt "Enter Table Name [Enter For All]:"

select 'create table '||cl.owner||'.'||cl.table_name||'_new ('				table_name
,	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)                                                           type
,	decode(cl.nullable,
		'Y','NULL',
		'N','NOT NULL')||decode(cl.column_id,
					gr.mx_col,');'
						 ,',' )					nn_or_n
from dba_tab_columns          cl
,    (select max(column_id) mx_col, table_name
      from   dba_tab_columns
      group by table_name)    gr
where cl.owner = nvl(upper('&shema_owner'),cl.owner)
and   cl.table_name = nvl(upper('&tab_name'),cl.table_name)
and   cl.table_name = gr.table_name
order by cl.owner||'.'||cl.table_name
,	 cl.column_id
/