Index DDL Generation (qindexddl.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

Generates CREATE INDEX DDL statements using Oracle’s DBMS_METADATA package. The script allows filtering by owner, index name, table name, and tablespace, and produces clean DDL output suitable for recreating indexes in other environments.

The Script

rem  qindexddl.sql
rem
ttitle 'Index DDL Generation'
rem
set heading off
set pagesize 0
set long 100000
rem
define Owner='&OWNER'
define TABLESPACE_NAME='&TABLESPACE_NAME'
define TABLE_NAME='&TABLE_NAME'
define INDEX_NAME='&INDEX_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;
/
rem
column x format a80 WORD_WRAPPED
rem
select dbms_metadata.get_ddl('INDEX',u.index_name,u.owner) x from
dba_indexes u
where
  owner like nvl(upper('&owner'),'%')
and  index_NAME like nvl(upper('&index_NAME'),'%')
and TABLE_NAME like nvl(upper('&TABLE_NAME'),'%')
and  TABLESPACE_NAME like nvl(upper('&TABLESPACE_NAME'),'%')
/
rem
undefine  Owner
undefine TABLESPACE_NAME
undefine TABLE_NAME
undefine INDEX_NAME

Usage

-- Generate DDL for all indexes
@qindexddl.sql
-- Enter: [press enter for all owners]
-- Enter: [press enter for all tablespaces]
-- Enter: [press enter for all tables]
-- Enter: [press enter for all indexes]

-- Generate DDL for specific schema
@qindexddl.sql
-- Enter: SCOTT (for specific owner)
-- Enter: [press enter for all tablespaces]
-- Enter: [press enter for all tables]
-- Enter: [press enter for all indexes]

-- Generate DDL for specific table's indexes
@qindexddl.sql
-- Enter: HR (for specific owner)
-- Enter: [press enter for all tablespaces]
-- Enter: EMPLOYEES (for specific table)
-- Enter: [press enter for all indexes]

-- Generate DDL for specific index
@qindexddl.sql
-- Enter: HR (for specific owner)
-- Enter: [press enter for all tablespaces]
-- Enter: [press enter for all tables]
-- Enter: IDX_EMP_DEPT (for specific index)

Parameters

The script prompts for:

• OWNER: Schema name (% for all schemas)
• TABLESPACE_NAME: Tablespace name (% for all tablespaces)
• TABLE_NAME: Table name (% for all tables)
• INDEX_NAME: Index name (% for all indexes)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on DBA_INDEXES
EXECUTE on DBMS_METADATA

Sample Output

CREATE INDEX "HR"."IDX_EMPLOYEES_DEPT" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "INDEXES" ;

CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "INDEXES" ;

CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "INDEXES" ;

CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "INDEXES" ;

DBMS_METADATA Transform Parameters

The script configures several transform parameters:

CONSTRAINTS_AS_ALTER = TRUE

• Generates constraints as separate ALTER statements
• Separates index creation from constraint definition

STORAGE = FALSE

• Excludes storage clause details
• Produces cleaner, more portable DDL

SQLTERMINATOR = TRUE

• Adds semicolons to end of statements
• Makes output ready for execution

Common Use Cases

Database Migration

-- Extract index DDL for migration
@qindexddl.sql
-- Enter: schema_name
-- Save output for deployment in target environment

Environment Refresh

-- Generate DDL for environment synchronization
@qindexddl.sql
-- Compare indexes between environments
-- Apply missing indexes to target

Index Recreation

-- Generate DDL before dropping indexes
@qindexddl.sql
-- Save DDL for recreation after maintenance
-- Ensure consistent index structure

Documentation

-- Document current index structures
@qindexddl.sql
-- Maintain index inventory
-- Track index changes over time

Performance Tuning

-- Extract DDL for index analysis
@qindexddl.sql
-- Modify index structures for testing
-- Compare performance with different configurations

Output Customization

Additional Transform Parameters

-- Include storage details
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', true);

-- Exclude tablespace clause
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', false);

-- Include segment attributes
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', true);

Format Settings

-- Adjust line width for output
set linesize 120

-- Set longer page size for large output
set pagesize 1000

-- Adjust long setting for very large DDL
set long 1000000

Filtering Examples

By Tablespace

-- Get indexes in specific tablespace
@qindexddl.sql
-- Enter: [all owners]
-- Enter: INDEX_TBS (specific tablespace)

By Index Pattern

-- Get indexes matching pattern
@qindexddl.sql
-- Enter: [all owners]
-- Enter: [all tablespaces]
-- Enter: [all tables]
-- Enter: IDX_% (indexes starting with IDX_)

By Table Pattern

-- Get indexes for tables matching pattern
@qindexddl.sql
-- Enter: [all owners]
-- Enter: [all tablespaces]
-- Enter: EMP% (tables starting with EMP)

Post-Processing

Save to File

-- Spool output to file
spool index_ddl.sql
@qindexddl.sql
spool off

Modify Output

-- Edit generated DDL as needed
-- Change tablespace assignments
-- Modify index attributes
-- Add comments or headers

Limitations

Memory Requirements

• Large DDL statements require sufficient LONG setting
• Complex indexes may need increased memory allocation

Transform Limitations

• Some advanced index features may not be fully captured
• Function-based indexes require careful review
• Domain indexes may need special handling

Performance Impact

• Variable: Depends on number of indexes
• DBMS_METADATA Overhead: Metadata extraction can be resource-intensive
• Large Schemas: May take significant time for schemas with many indexes

Related Scripts

• dindex - Index analysis and statistics
• dobject - General object information
• Table DDL generation scripts for complete schema recreation