set serveroutput on size 500000
set linesize 400
set pagesize 0
undefine owner
set verify off
set heading off
set feedback off
spool create_tables.sql
declare
cursor c_table(p_owner varchar2) is
  select table_name, partitioned, tablespace_name
  from   dba_tables
  where  owner = p_owner
  order by table_name;

cursor c_column(p_owner varchar2, p_table varchar2) is
  select column_name, column_id, nullable, data_type, data_length, data_precision, data_scale, data_default
  from   dba_tab_columns
  where  table_name = p_table
  and    owner      = p_owner
  order by column_id;
  
cursor c_partkey(p_owner varchar2, p_table varchar2) is
  select column_position, column_name
  from   dba_part_key_columns
  where  name       = p_table
  and    owner      = p_owner
  order by column_position;
  
cursor c_partition(p_owner varchar2, p_table varchar2) is
  select partition_position, partition_name, high_value, tablespace_name
  from   dba_tab_partitions
  where  table_name   = p_table
  and    table_owner  = p_owner
  order by partition_position;  

  v_line varchar2(255);

begin

for c_table_rec in c_table('&&owner') loop

  dbms_output.put_line('create table '||c_table_rec.table_name);

  -- ******************
  -- Column definitions
  -- ******************
  for c_column_rec in c_column('&&owner',c_table_rec.table_name) loop
    if c_column_rec.column_id = 1 then
      dbms_output.put('(');
    else
      dbms_output.put(',');
    end if;
    dbms_output.put('"'||c_column_rec.column_name||'" ');
    IF c_column_rec.data_type = 'CHAR' THEN
       dbms_output.put(c_column_rec.data_type||'('||c_column_rec.data_length||')');
    ELSIF c_column_rec.data_type = 'VARCHAR2' THEN
       dbms_output.put(c_column_rec.data_type||'('||c_column_rec.data_length||')');
    ELSIF c_column_rec.data_type = 'NUMBER' THEN
       IF c_column_rec.data_scale is null THEN
          dbms_output.put(c_column_rec.data_type);
       ELSIF c_column_rec.data_precision is null THEN
          dbms_output.put(c_column_rec.data_type||'('||c_column_rec.data_scale||')');
       ELSE
          dbms_output.put(c_column_rec.data_type||'('||c_column_rec.data_scale||','||c_column_rec.data_precision||')');      
       END IF;
    ELSE
       dbms_output.put(c_column_rec.data_type);
    END IF;
    IF c_column_rec.data_default is not null THEN
       dbms_output.put(' default '||c_column_rec.data_default);
    END IF;
    IF c_column_rec.nullable = 'N' THEN
       dbms_output.put_line(' NOT NULL');
    ELSE
       dbms_output.new_line;
    END IF;
  end loop;  -- column loop
  dbms_output.put_line(')');

  -- ************
  -- Partitioned? 
  -- ************
  if c_table_rec.partitioned = 'NO' then
     dbms_output.put_line('tablespace '||c_table_rec.tablespace_name);
  else
  -- **************
  -- Partition Keys
  -- **************
     dbms_output.put_line('PARTITION BY RANGE');
     for c_partkey_rec in c_partkey('&&owner', c_table_rec.table_name) loop
       if c_partkey_rec.column_position = 1 then
          dbms_output.put('('||c_partkey_rec.column_name);
       else
          dbms_output.put(','||c_partkey_rec.column_name);
       end if;
     end loop; 
     dbms_output.put_line(')');
  -- **********
  -- Partitions
  -- **********
     for c_partition_rec in c_partition('&&owner', c_table_rec.table_name) loop
         if c_partition_rec.partition_position = 1 then
            dbms_output.put_line('(PARTITION '||c_partition_rec.partition_name);
         else
            dbms_output.put_line(',PARTITION '||c_partition_rec.partition_name);
         end if;
         dbms_output.put_line('   values less than ('||c_partition_rec.high_value||')');    
         dbms_output.put_line('   tablespace '||c_partition_rec.tablespace_name);
     end loop;
     dbms_output.put_line(')');
  end if;

  dbms_output.put_line('/');
end loop;  -- table loop
end;
/
spool off
