Rem ===============Save this script as gen_table.sql
Rem ===============Save make_table.sql then at SQL*PLUS> @make_table
REM================ It will generate a create_table.sql script
declare

	cursor driver is
	select distinct table_name from
	user_tab_partitions;
	

     cursor part_table( t1 in varchar2)  is  
     select    distinct table_name,
               TABLESPACE_NAME,
               PCT_FREE,
               PCT_USED,
               INI_TRANS,
               MAX_TRANS,  
               INITIAL_EXTENT,
               NEXT_EXTENT,
               PCT_INCREASE  
        from   user_tab_partitions 
        where table_name like t1
	and rownum < 2
        order by table_name;  

     cursor part_col (t_name in varchar2) is  
        select table_name, column_name, data_type, data_length,  
               data_precision, data_scale, nullable  
        from   user_tab_columns  
        where  table_name =  t_name 
        order by column_name;  
    
          tab_name     varchar2(40);  
          tabsp_name varchar2(40);  
          mpct_free  number;  
          mpct_used   number;  
          mini_trans number;  
          mmax_trans number;  
          mini_ext   number;  
          mnext_ext  number;  
          mpct_inc   number;  
     col_name     varchar2(40);  
     ct           number := 0;  
     line_ct      number := 0;  
    
  BEGIN  
     delete from temp_op;
     commit; 

	for t1 in driver loop
    
     for part_table_rec in part_table(t1.table_name) loop  

        tab_name := part_table_rec.table_name;  
	dbms_output.put_line( tab_name || tabsp_name || part_table_rec.PCT_INCREASE);
        tab_name := substr(tab_name,1,length(tab_name)-1)||'N'; 
          tabsp_name := part_table_rec.TABLESPACE_NAME;  
          mpct_free := part_table_rec.PCT_FREE ;  
          mpct_used := part_table_rec.PCT_USED;  
          mini_trans := part_table_rec.INI_TRANS;  
          mmax_trans := part_table_rec.MAX_TRANS;  
          mini_ext := part_table_rec.INITIAL_EXTENT ;  
          mnext_ext := part_table_rec.NEXT_EXTENT ;  
          mpct_inc := part_table_rec.PCT_INCREASE ;  
        insert into temp_op(f1, line)  
        values(line_ct, 'create table '|| tab_name || '(' );  
        line_ct := line_ct + 1;  
    
        ct := 0;  
        for part_col_rec in part_col(part_table_rec.table_name) loop  
           ct := ct + 1;  
           if ct = 1 then  
              insert into  temp_op(f1, line )  
              values (line_ct ,  
                      '   '|| part_col_rec.column_name||'    '||part_col_rec.data_type||decode(part_col_rec.data_type, 'VARCHAR2','('|| to_char(part_col_rec.data_length)||')', 'NUMBER',decode(part_col_rec.data_precision, null,'', '('||to_char(part_col_rec.data_precision)||decode(part_col_rec.data_scale, null, ')', ','||to_char(part_col_rec.data_scale)||')' )), '')|| decode(part_col_rec.nullable, 'Y', '', '   NOT NULL') );  
    
  line_ct := line_ct + 1;  
           else  
              insert into  temp_op(f1, line)  
              values (line_ct, '  ,'|| part_col_rec.column_name||' '||part_col_rec.data_type||decode(part_col_rec.data_type, 'VARCHAR2','('||to_char(part_col_rec.data_length)||')','NUMBER',decode(part_col_rec.data_precision, null,'','('||to_char(part_col_rec.data_precision)||decode(part_col_rec.data_scale, null, ')', ','||to_char(part_col_rec.data_scale)||')' )), '')|| decode(part_col_rec.nullable, 'Y', '', '   NOT NULL') );  
              line_ct := line_ct + 1;  
           end if;  
        end loop;  
        insert into temp_op (f1, line )  
        values(line_ct, ')' );  
        line_ct := line_ct + 1;  
        insert into temp_op (f1, line )  
        values (line_ct, ' PCTFREE ' || mpct_free || ' PCTUSED ' || mpct_used ) ;  
          line_ct := line_ct + 1;  
        insert into temp_op (f1, line )  
        values (line_ct, ' INITRANS ' || mini_trans || ' MAXTRANS ' || mmax_trans);  
        insert into temp_op (f1, line )  
        values (line_ct, ' TABLESPACE '||tabsp_name );  
        line_ct := line_ct + 1;  
        insert into temp_op (f1, line )  
        values(line_ct, ' STORAGE ( INITIAL ' || mini_ext || ' NEXT '  
                  || mnext_ext || ' PCTINCREASE ' || mpct_inc || ');' );  
          line_ct := line_ct + 1;  
        insert into temp_op (f1, line )  
        values(line_ct, 'REM -------------NEXT TABLE ------------ ');  
          line_ct := line_ct + 1;  
        commit;  
     end loop;  

end loop; -- Driver cursor
  END;  
    
/

REM========================Run 
set heading off ;
  set echo off ;
  set term off ;
  set feedback off;
  set pagesize 0;
  drop table temp_op;
  create table temp_op (f1 number,line varchar2(80));
  start gen_table.sql
  spool create_table.sql
  select line from temp_op order by f1 ;
  spool off
/
drop table temp_op;

exit;


--@make_table