dba:oracle:howtos:tablespace_ddl
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | Last revisionBoth sides next revision | ||
dba:oracle:tablespace_ddl [2022/02/11 11:12] – ↷ Page moved from oracle:tablespace_ddl to dba:oracle:tablespace_ddl 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' | ||
- | |||
- | |||
- | ====== Script Code ====== | ||
- | < | ||
- | REM create_tablespace_ddl.sql | ||
- | REM Charles Schultz | ||
- | REM 25-Jun-2008 | ||
- | REM | ||
- | REM Generates tablespace DDL | ||
- | REM Assumes the following: | ||
- | REM - Excluding (' | ||
- | 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: | ||
- | REM - first_ddl: | ||
- | REM - last_ddl: | ||
- | REM - mid_ddl: | ||
- | REM - tablespace_ddl: | ||
- | 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 ' | ||
- | accept initial_size default ' | ||
- | accept max_size default ' | ||
- | |||
- | set head off newpage 0 pages 0 verify off feed off lines 200 | ||
- | spool & | ||
- | with tablespace_specs as | ||
- | (select row_number() over (partition by tb.tablespace_name order by df.file_id) rn_up, | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | from dba_tablespaces tb, | ||
- | where tb.TABLESPACE_NAME = df.TABLESPACE_NAME | ||
- | and tb.tablespace_name not in (' | ||
- | ), | ||
- | first_ddl as | ||
- | (select tablespace_name, | ||
- | 1 section_num, | ||
- | ' | ||
- | from tablespace_specs where rn_up = 1), | ||
- | last_ddl as | ||
- | (select tablespace_name, | ||
- | 3 section_num, | ||
- | ' | ||
- | from tablespace_specs where rn_down = 1), | ||
- | mid_ddl as | ||
- | (select tablespace_name, | ||
- | 2 section_num, | ||
- | ',''' | ||
- | from tablespace_specs where rn_up > 1), | ||
- | tablespace_ddl as | ||
- | (select text, | ||
- | union | ||
- | | ||
- | union | ||
- | | ||
- | order by tablespace_name, | ||
- | select text from tablespace_ddl | ||
- | / | ||
- | spool off | ||
- | </ | ||
- | |||
- | |||
- | ====== Links ====== | ||
- | Thanks to [[http:// | ||
dba/oracle/howtos/tablespace_ddl.txt · Last modified: 2023/01/31 08:28 by dodger