Checking and Rebuilding Database Indexes to address database performance
search cancel

Checking and Rebuilding Database Indexes to address database performance

book

Article ID: 159617

calendar_today

Updated On: 07-07-2025

Products

Data Loss Prevention Enforce Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

If database performance falls off significantly, there is a chance that one or more key indexes have become invalid and need to be rebuilt.  

Environment

Data Loss Prevention

Resolution

Note: LOB indexes cannot be rebuilt in this manner. A LOB index is an Oracle-managed map for BLOB/CLOB storage, whereas this script targets standard user indexes—ALTER INDEX … REBUILD does not work for LOB indexes and they are skipped. If you attempt to rebuild LOB indexes in this manner; you will receive an error

 

The following SQL script will list all 'protect' owned indexes that do not have the status 'VALID'. If the script returns a value, then continue on to the rebuild scripts.

Log in to SQLPlus as protect, then run this script:

SELECT index_name, status
FROM user_indexes
WHERE status<>'VALID'
AND index_type<>'LOB';

 

Rebuild Scripts:

Note: Step 1: Creating the rebuild scripts can be done while the DLP services are running. Step 2: Running the rebuild scripts should be done while the DLP services are stopped.

Step 1: Creating the rebuild scripts

Dynamically create the rebuild scripts for the invalid indexes:

Log into SQLPlus as protect, then run this script:

set pages 0
spool <valid directory path>\dyn_index_rebuild.sql
SELECT 'ALTER INDEX '||index_name||' REBUILD;'||chr(10)||'SHOW ERRORS; '
     FROM user_indexes
     WHERE status <> 'VALID'
     AND index_type<>'LOB';
spool off;
exit;

This will create an output file in <valid directory path> called dyn_index_rebuild.sql.

 

Step 2: Running the rebuild scripts

Ensure the DLP services are stopped.

While still logged into SQLPlus as protect, run dyn_index_rebuild.sql in the following manner:

SQL> @<valid directory path>\dyn_index_rebuild.sql

This should rebuild the indexes listed. 

Bear in mind that the larger the table and the more complex the index, the longer the rebuild may take.

 

Step 3 (Optional): Create and Rebuild ALL indexes

Note: Rebuilding ALL indexes may take several hours.

To rebuild ALL indexes, regardless of their status, make sure that DLP services are down and run the following script from SQLPlus as protect:

set pages 0
spool <valid directory path>\user_index_rebuild.sql
SELECT 'ALTER INDEX '||index_name||' REBUILD;'||chr(10)||'SHOW ERRORS; '
     FROM user_indexes
     WHERE index_type <> 'LOB';
spool off;

This will create an output file in <valid directory path> called user_index_rebuild.sql.  While still logged into SQLPlus as protect, run user_index_rebuild.sql in the following manner:

SQL> @<valid directory path>\user_index_rebuild.sql

 

After the indexes are rebuilt the DLP services can be restarted. If database performance issue persist then contact support.