Differences

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

--- oracle:tablespace_ddl [2018/04/27 08:56] – dodger
+++ dba:oracle:howtos:tablespace_ddl [2023/01/31 08:28] – removed - external edit (Unknown date) 127.0.0.1
@@ 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]]