--
-- Script zum Generieren der Tablespace aus bestehender DB
--
set echo off heading off termout off feed off pages 0 numwidth 10 linesize 80
spool create_ts.sql
select 'set echo off termout off feed off pages 0' || chr(10) ||
       'spool create_ts.lis'
from dual
/
--
-- 1st: For each tablespace, select the first datafile that was
-- created and create this again as first file for this (each) tablespace
--
select 'CREATE TABLESPACE ' || f1.tablespace_name || chr(10) ||
       ' DATAFILE ' || '''' || f1.file_name || '''' || ' SIZE ' || f1.bytes || chr(10) ||
       ' DEFAULT STORAGE (INITIAL ' || t1.initial_extent || 
                        ' NEXT ' || t1.next_extent ||
                        ' PCTINCREASE ' || t1.pct_increase || chr(10) ||
                        ' MINEXTENTS ' || t1.min_extents ||
                        ' MAXEXTENTS ' || t1.max_extents ||
       ') ' || chr(10) || ' ' ||
       decode (t1.status, 'INVALID', 'OFFLINE', t1.status) ||
       ';'
from dba_tablespaces t1, dba_data_files f1
where f1.tablespace_name = t1.tablespace_name
and f1.tablespace_name != 'SYSTEM'
and f1.file_id = (select min(f2.file_id)
                    from dba_data_files f2
                   where f2.tablespace_name = f1.tablespace_name)
order by f1.file_id
/
--
-- 2nd: For each tablespace, create additional datafiles if any there.
--
select 'ALTER TABLESPACE ' || f1.tablespace_name || chr(10) ||
       ' ADD DATAFILE ' || '''' || f1.file_name || '''' || ' SIZE ' || f1.bytes || ';'
from dba_data_files f1
where f1.tablespace_name != 'SYSTEM'
and f1.file_id > (select min(f2.file_id)
                    from dba_data_files f2
where f2.tablespace_name = f1.tablespace_name)
order by f1.file_id
/
select 'spool off;' from dual
/
spool off;