Granted, considering that this is Production, prudence would say that one will have tested this several times (as we are currently doing) before actually running the process in Production. And in doing so, one will know exactly how large the files should be, and can in fact extract the ddl (metadata api, data pump, TOAD, etc) from the test database. As a simple exercise, I still want a way to generate slightly customized tablespace ddl. So I ended up writing my own.
Some of the key things I wanted was to be to dynamically adjust the initial sizes of the datafiles. Also, for the sake of portability, I added a runtime parameter for maxsize. I am still torn about LMT being locked into UNIFORM, but since that is all we use here, I am not too worried for us. =) But I do want LMT and ASSM to be default; for 10g, I see no reason to turn these off at all.
Blogspot is going to horribly screw up the formatting for this, and I have not found a way around it. Sorry.
REM Charles Schultz
REM Generates tablespace DDL
REM Assumes the following:
REM - Excluding ('SYSTEM','SYSAUX','USERS','TEMP','TOOLS','UNDOTS')
REM - All LMT
REM - All ASSM
REM - All datafiles AUTOEXTEND, initial size = half of current, or size of initial extent, maxsize defaults to 4000M
REM Retains INITIAL_EXTENT settings for LMT
REM Builds 5 inline materialized views
REM - tablespace_specs: gets the data from dba_tablespaces and dba_data_files, ordering by file_id
REM - first_ddl: generates the initial "CREATE TABLESPACE" statement, along with the first datafile
REM - last_ddl: generates the tablespace options (LMT, ASSM)
REM - mid_ddl: generates statements for any datafiles that are not the first
REM - tablespace_ddl: Combines all ddl, ordered by tablespace name and section
REM - Does not allow for BIGFILES, but this is a trivial change
REM - Does not allow for LMT Autoallocate. Again, not a difficult modification
PROMPT This script will generate tablespace ddl, making the following assumptions:
PROMPT - All tablespaces are Locally Managed (LMT) with UNIFORM sizes
PROMPT - All tablespaces have automatic segment space management (ASSM)
PROMPT - All datafiles autoextend
accept sqlfile default 'create_tablespaces.sql' char prompt 'Enter name for generated sql file [create_tablespaces.sql]: '
accept initial_size default '50' char prompt 'Percent by which to shrink datafile initial sizes, 0 being none : '
accept max_size default '4000' char prompt 'MAXSIZE of autoextend datafiles, in megabytes : '
set head off newpage 0 pages 0 verify off feed off lines 200
with tablespace_specs as
(select row_number() over (partition by tb.tablespace_name order by df.file_id) rn_up,
row_number() over (partition by tb.tablespace_name order by df.file_id desc) rn_down,
from dba_tablespaces tb,dba_data_files df
where tb.TABLESPACE_NAME = df.TABLESPACE_NAME
and tb.tablespace_name not in ('SYSTEM','SYSAUX','USERS','TEMP','TOOLS','UNDOTS')
'create tablespace '||tablespace_name||' datafile '''||file_name||''' size '||file_size||' autoextend on next '||increment_by||' maxsize '||&MAX_SIZE||'M' text
from tablespace_specs where rn_up = 1),
'EXTENT MANAGEMENT LOCAL UNIFORM SIZE '||INITIAL_EXTENT||' SEGMENT SPACE MANAGEMENT AUTO;' text
from tablespace_specs where rn_down = 1),
','''||file_name||''' size '||file_size||' autoextend on next '||increment_by||' maxsize '||&MAX_SIZE||'M' text
from tablespace_specs where rn_up > 1),
(select text,tablespace_name, section_num from first_ddl
select text,tablespace_name, section_num from mid_ddl
select text,tablespace_name, section_num from last_ddl
order by tablespace_name, section_num)
select text from tablespace_ddl