Tablespace DDL Generation (qtsddl.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script generates complete DDL (Data Definition Language) statements for tablespaces using Oracle’s DBMS_METADATA package. It extracts tablespace definitions with storage clauses excluded for cleaner output. Essential for recreating tablespaces or documenting database storage architecture.

The Script

set heading off
set pagesize 0
set long 100000

rem
ttitle 'Get tablespace DDL'
rem

rem
define TABLESPACE_NAME='&TABLESPACE_NAME'
rem

begin
  dbms_metadata.set_transform_param
            ( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
            dbms_metadata.set_transform_param
            ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
            dbms_metadata.set_transform_param
            ( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
end;
/

column x format a80 WORD_WRAPPED
select   dbms_metadata.get_ddl('TABLESPACE',u.TABLESPACE_NAME)  x from
dba_tablespaces u
where
 TABLESPACE_NAME like nvl(upper('&TABLESPACE_NAME'),'%');

rem
undefine TABLESPACE_NAME

Parameters

The script prompts for:

• TABLESPACE_NAME: Tablespace name pattern (optional, defaults to all tablespaces)

Usage

-- Basic usage (will prompt for tablespace name)
@qtsddl.sql

-- Example values when prompted:
-- TABLESPACE_NAME: DATA%
-- TABLESPACE_NAME: (empty for all tablespaces)

Sample Output

                               Get tablespace DDL

CREATE TABLESPACE "DATA01" DATAFILE
  '/u01/oradata/PRODDB/data01_01.dbf' SIZE 1073741824,
  '/u01/oradata/PRODDB/data01_02.dbf' SIZE 1073741824
LOGGING
ONLINE
PERMANENT
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "INDEX01" DATAFILE
  '/u01/oradata/PRODDB/index01_01.dbf' SIZE 536870912,
  '/u01/oradata/PRODDB/index01_02.dbf' SIZE 536870912
LOGGING
ONLINE
PERMANENT
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "TEMP" TEMPFILE
  '/u01/oradata/PRODDB/temp01.dbf' SIZE 268435456,
  '/u01/oradata/PRODDB/temp02.dbf' SIZE 268435456
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

Features

• Clean Output: Storage clauses are excluded for cleaner DDL
• Pattern Matching: Filter tablespaces by name pattern
• Complete Definition: Includes all tablespace properties
• SQL Terminators: Statements end with semicolons for easy execution
• All Types: Supports permanent, temporary, and undo tablespaces