set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* script to spool a listing of all drop and create */
/*    statements required to rebuild accounts.      */
/* Contents: Table name, col definitions and        */
/*    storage parameters.                           */
/*                                                  */
/*         @c:\oracle\sql\bld_tbl1.sql              */ 
/*                                                  */
/* NOTE: will build all sys/system tables       */
/*                                                  */
/* ************************************************ */

set termout off;
set echo off;
set feedback off;

col dummy 	noprint format a1;
col dummy2 	noprint format a1;
col table_name 	noprint format a1;
col command 	format a70;

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

 select 'drop table '||owner||'.'||table_name||';' command, 0 dummy,
	table_name, 0 dummy2 from all_tables
	where owner in ('SYS', 'SYSTEM')
UNION
   select 'create table '||owner||'.'||table_name||' (' command, 2 dummy,
	table_name, 0 dummy2 from all_tables
	where owner in ('SYS', 'SYSTEM')
  UNION
     select '  '||decode(column_id,1,null,',')||column_name||'		'||data_type||
 	    decode(UPPER(data_type), 
 	    	'DATE',		NULL,
 	    	'LONG',		NULL,
 		'CHAR',		'('||data_length||')', 
 		'VARCHAR2',	'('||data_length||')', 
 		'VARCHAR',	'('||data_length||')',
 		'NUMBER',       '('||data_length||','||NVL(data_scale,0)||')',
 		' ')||'  '||
            decode(nullable, 'Y', NULL, '	NOT NULL')   command,
        	2 dummy, table_name, column_id dummy2
       from all_tab_columns
      where owner in ('SYS', 'SYSTEM')   AND
            all_tab_columns.table_name in
    		(select table_name 
    		   from all_tables
	           where owner in ('SYS', 'SYSTEM'))
   UNION
     select '   )' command, 3 dummy, table_name, 0 dummy2
       from all_tab_columns
      where owner in ('SYS', 'SYSTEM')     AND
            all_tab_columns.table_name in
    		(select table_name 
    		   from all_tables
	           where owner in ('SYS', 'SYSTEM'))
  UNION
     select '   pctfree  '||pct_free||'     
   pctused  '||pct_used||'     
   initrans '||ini_trans||'     
   maxtrans '||max_trans||'
   storage (initial     '||initial_extent||'
            next        '||next_extent||'
            minextents  '||min_extents||'
            maxextents  '||max_extents||'
            pctincrease '||pct_increase||'
           ) tablespace '||tablespace_name||';' command,
             3 dummy, table_name, 0 dummy2
      from all_tables 
      where owner in ('SYS', 'SYSTEM')
      order by 3,2,4;
                 
spool bld_tbl1.rslt
/
spool off;
set heading on;
set pagesize 100;
set termout on;

commit;










