Skip to content

Generate Rebuild Index Statements (rebuild_index.gen)

What This Script Does

This generator script creates ALTER INDEX REBUILD statements with ONLINE option for non-blocking index rebuilding. It preserves the original tablespace and handles different index types.

The Script

rem  rebuild_index.gen
rem
select 'alter index ' || owner || '.' || index_name || ' rebuild' ||
       decode(tablespace_name, null, '', ' tablespace ' || tablespace_name) ||
       ' online;'
  from sys.dba_indexes
 where owner like upper('&owner')
   and index_name like upper('&index_name')
   and index_type in ('NORMAL','BITMAP')
   and owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
 order by owner, index_name
/

Usage

-- Basic usage
@rebuild_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
-- Plus ALTER INDEX privilege

Sample Output

alter index HR.EMP_EMP_ID_PK rebuild tablespace USERS online;
alter index HR.EMP_NAME_IX rebuild tablespace USERS online;
alter index SCOTT.DEPT_LOC_IX rebuild tablespace USERS online;

Key Features

ONLINE Rebuilding: Allows DML operations during rebuild
Tablespace Preservation: Maintains original tablespace location
Index Type Support: Handles NORMAL and BITMAP indexes
System Schema Protection: Excludes critical system schemas

Common Use Cases

Rebuild All Indexes in Schema

@rebuild_index.gen
-- Enter: HR for owner, % for index_name

Rebuild Fragmented Indexes

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

Drop Index Generator - Drop indexes if rebuild fails
Index Analysis - Analyze index fragmentation