Differences

This shows you the differences between two versions of the page.

--- dba:oracle:howtos:tablespace_ddl [2023/01/31 08:28] – removed - external edit (Unknown date) 127.0.0.1
+++ dba:oracle:howtos:tablespace_ddl [2023/01/31 08:28] (current) – ↷ Page moved from dba:oracle:tablespace_ddl to dba:oracle:howtos:tablespace_ddl dodger
@@ Line 1: / Line 1: @@
+====== [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,
+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,
+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,
+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]]