CREATE TABLESPCAE Statement aus DB generieren

SQL-Script zum Erstellen der Tablespaces

Das folgende dynamisch generierte SQL-Script erstellt ein File, welches die Kommandos zum Erstellen der Tablespaces einer bestehenden Datenbank enthält.

--
-- 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;

set echo off termout off feed off pages 0
spool create_ts.lis

CREATE TABLESPACE RBS
 DATAFILE '/data/ppb/db1/PPB_rbs_1.dbf' SIZE 1048576000
 DEFAULT STORAGE (INITIAL 2097152 NEXT 2097152 PCTINCREASE 50
 MINEXTENTS 2 MAXEXTENTS 2147483645)
 ONLINE;

CREATE TABLESPACE TEMP
 DATAFILE '/data/ppb/db1/PPB_temp_1.dbf' SIZE 2097152000
 DEFAULT STORAGE (INITIAL 2097152 NEXT 2097152 PCTINCREASE 0
 MINEXTENTS 2 MAXEXTENTS 2147483645)
 ONLINE;

CREATE TABLESPACE USERS
 DATAFILE '/data/ppb/db1/PPB_users_1.dbf' SIZE 10485760
 DEFAULT STORAGE (INITIAL 102400 NEXT 102400 PCTINCREASE 0
 MINEXTENTS 1 MAXEXTENTS 2147483645)
 ONLINE;

CREATE TABLESPACE TAB
 DATAFILE '/data/ppb/db1/PPB_tab_1.dbf' SIZE 2097152000
 DEFAULT STORAGE (INITIAL 65536 NEXT 65536 PCTINCREASE 0
 MINEXTENTS 1 MAXEXTENTS 2147483645)
 ONLINE;

CREATE TABLESPACE CDR
 DATAFILE '/data/ppb/db1/PPB_cdr_1.dbf' SIZE 2097152000
 DEFAULT STORAGE (INITIAL 5242880 NEXT 5242880 PCTINCREASE 0
 MINEXTENTS 1 MAXEXTENTS 2147483645)
 ONLINE;

CREATE TABLESPACE CRE
 DATAFILE '/data/ppb/db1/PPB_cre_1.dbf' SIZE 2097152000
 DEFAULT STORAGE (INITIAL 5242880 NEXT 5242880 PCTINCREASE 0
 MINEXTENTS 1 MAXEXTENTS 2147483645)
 ONLINE;

CREATE TABLESPACE IDX
 DATAFILE '/data/ppb/db1/PPB_idx_1.dbf' SIZE 2097152000
 DEFAULT STORAGE (INITIAL 204800 NEXT 204800 PCTINCREASE 0
 MINEXTENTS 1 MAXEXTENTS 2147483645)
 ONLINE;

CREATE TABLESPACE REP
 DATAFILE '/data/ppb/db1/ppb_rep1.dbf' SIZE 2097152000
 DEFAULT STORAGE (INITIAL 2097152 NEXT 2097152 PCTINCREASE 0
 MINEXTENTS 2 MAXEXTENTS 2147483645)
 ONLINE;

ALTER TABLESPACE CDR
 ADD DATAFILE '/data/ppb/db1/PPB_cdr_2.dbf' SIZE 2097152000;

ALTER TABLESPACE CDR
 ADD DATAFILE '/data/ppb/db1/PPB_cdr_3.dbf' SIZE 2097152000;

ALTER TABLESPACE CDR
 ADD DATAFILE '/data/ppb/db1/PPB_cdr_4.dbf' SIZE 2097152000;

ALTER TABLESPACE CDR
 ADD DATAFILE '/data/ppb/db1/PPB_cdr_5.dbf' SIZE 2097152000;

ALTER TABLESPACE CDR
 ADD DATAFILE '/data/ppb/db1/PPB_cdr_6.dbf' SIZE 2097152000;

ALTER TABLESPACE CDR
 ADD DATAFILE '/data/ppb/db1/PPB_cdr_7.dbf' SIZE 2097152000;

ALTER TABLESPACE CDR
 ADD DATAFILE '/data/ppb/db1/PPB_cdr_8.dbf' SIZE 2097152000;

spool off;