DBAtlas · Database operations atlas

Rebuild an index online

OracleMaintenance

Command

-- Regular index:
ALTER INDEX customer_app.idx_customer_email REBUILD ONLINE PARALLEL 4;

-- Moving it to a different tablespace:
ALTER INDEX customer_app.idx_customer_email
  REBUILD ONLINE TABLESPACE customer_idx PARALLEL 4;

-- Reset parallelism after the rebuild (so plans are not affected):
ALTER INDEX customer_app.idx_customer_email NOPARALLEL;

What it does

Rebuilds the index without blocking DML (ONLINE); removes fragmentation and can change the tablespace.

When to use it

Index bloat/fragmentation, moving an index to another tablespace, repairing an UNUSABLE index.

Caution

ONLINE uses extra temporary space and I/O. PARALLEL persists; reset it with NOPARALLEL after the rebuild, otherwise queries run in parallel unintentionally. ONLINE is not supported for bitmap indexes in every version. ONLINE rebuild is available only in Enterprise Edition.

Open in DBAtlas →

Related