Skip to content

Generate Drop Index Statements (drop_index.gen)

What This Script Does

This generator script creates DROP INDEX statements for indexes matching the specified criteria. It excludes system-generated indexes, LOB indexes, and indexes in system schemas.

The Script

rem  drop_index.gen
rem
select 'drop index ' || owner || '.' || index_name || ';'
  from sys.dba_indexes
 where owner like upper('&owner')
   and index_name like upper('&index_name')
   and owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
   and index_type != 'LOB'
   and generated = 'N'
 order by owner, index_name
/

Usage

-- Basic usage
@drop_index.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- index_name: Index name pattern (use % for wildcard)

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)
&index_name - Index name pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_INDEXES

Sample Output

drop index HR.EMP_EMAIL_UK;
drop index HR.EMP_NAME_IX;
drop index SCOTT.DEPT_LOC_IX;

Key Features

LOB Index Protection: Excludes LOB indexes which are managed automatically
System Generated Exclusion: Skips system-generated indexes
System Schema Protection: Excludes critical system schemas
Clean Output: Simple DROP INDEX statements ready for execution

Common Use Cases

Drop All Indexes in Schema

@drop_index.gen
-- Enter: SCOTT for owner, % for index_name

Drop Indexes with Specific Pattern

@drop_index.gen
-- Enter: % for owner, %_IX for index_name

Rebuild Index Generator - Generate index rebuild statements
Index Analysis - Analyze index storage and usage