====== [HOWTO] Obtain Tablespace DDL ======

====== Description ======
This script will query DBMS_METADATA table an obtain necessary data to generate tablespace's DDL.


====== Script Code ======
<code>
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
</code>


====== Links ======
Thanks to [[http://orajourn.blogspot.com.es/2008/06/create-tablespace-ddl.html]]