If database performance falls off significantly, there is a chance that one or more key indexes have become invalid and need to be rebuilt.
Data Loss Prevention
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';
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.