Handling Invalid/Unusable Siebel Index

Indexes are available on the Siebel base tables to improve performance and also to maintain the data integrity. The purpose of adding index is to help the database to search records quickly and efficiently. It’s very essential to make sure that Siebel Indexes are in active and valid state.

Scenario :

When indexes are in unusable state creation/modification (INSERT/UPDATE/DELETE)  of records cannot be done either through Siebel UI or Database. For example if S_ORG_EXT_U1 index is in unusable state, then user cannot create/modify any Account records

Has to be performed at regular intervals to check the status of Index and requires continuous manual effort

Solution :

An automated batch job that captures the UNUSABLE and INVALID state indexes and rebuilds them dynamically.

Enables to find the exact time and the root cause pertaining to index failures in database.

Respective team will receive an automated Email notifications with the status of index.

Syntax :

alter index siebel.S_ORG_EXT_U1 rebuild online; (For Oracle database)

Benefits :

  • Scheduled to run on demand
  • Improves Productivity
  • Reduces manual and person dependency
  • ZERO Errors. Increases credibility

PS : Pre-requisite for doing the above is to have required privilege to read/rebuild index