create tablespace ddl

I recently went looking for ways to recreate tablespaces, as we prepare to migrate our production database to a different character set (necessitated by the vendor, grrr). DBMS_METADATA is really cool, and I like it alot. Data Pump essentially uses DBMS_METADATA (I believe), so I thought one of those cool tools would be the way to go. Close, but not quite. If you do a complete export/import of your database, you end up reorganizing all your data, in essence defragging the whole thing. Which is good (nay, GREAT!). But Data Pump only lets you create tablespaces with the original datafile sizes.

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 create_tablespace_ddl.sql
REM Charles Schultz
REM 25-Jun-2008
REM
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
REM Retains INITIAL_EXTENT settings for LMT
REM
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
REM Limitations
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
PROMPT

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 [50]: '
accept max_size default '4000' char prompt 'MAXSIZE of autoextend datafiles, in megabytes [4000]: '

set head off newpage 0 pages 0 verify off feed off lines 200
spool &SQLFILE
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,
 tb.BIGFILE,
 tb.TABLESPACE_NAME,
 df.FILE_NAME,
 greatest(df.BYTES*((100-greatest(least(&INITIAL_SIZE,100),0))/100),tb.INITIAL_EXTENT) file_size,
 df.INCREMENT_BY*tb.block_size INCREMENT_BY,
 tb.INITIAL_EXTENT
 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')
 ),
first_ddl as
(select tablespace_name,
 1 section_num,
 '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),
last_ddl as
(select tablespace_name,
 3 section_num,
 'EXTENT MANAGEMENT LOCAL UNIFORM SIZE '||INITIAL_EXTENT||' SEGMENT SPACE MANAGEMENT AUTO;' text
 from tablespace_specs where rn_down = 1),
mid_ddl as
(select tablespace_name,
 2 section_num,
 ','''||file_name||''' size '||file_size||' autoextend on next '||increment_by||' maxsize '||&MAX_SIZE||'M' text
 from tablespace_specs where rn_up > 1),
tablespace_ddl as
(select text,tablespace_name, section_num from first_ddl
 union
 select text,tablespace_name, section_num from mid_ddl
 union
 select text,tablespace_name, section_num from last_ddl
 order by tablespace_name, section_num)
select text from tablespace_ddl
/
spool off