Index status check

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and identify any indexes in an Oracle database that are in an UNUSABLE status.
• Why: This job is crucial because it helps to ensure the integrity and performance of the database. Unusable indexes can impede database operations and affect the overall performance negatively, leading to potential system slowdowns or failures.
• Manual checking: You can check the status of indexes manually in the database by issuing the following SQL commands:

SELECT owner, index_name, index_type, status 
FROM all_indexes 
WHERE status = 'UNUSABLE';

Details of the Monitoring Job

This job operates by scanning all indexes in the specified Oracle instance and identifying any that have a status of ‘UNUSABLE’. It is scheduled to run automatically at regular five-minute intervals. The job acknowledges checks automatically and holds a validity period of one hour.

Report Generation

A report template included within this job generates a document titled “Index status” based on the results of the monitoring. The report displays:

• Owner of the index
• Index name
• Index type
• Current status of the index

This allows for a clear presentation of all indexes that are not in a ‘VALID’ status.

Special Considerations

• Exclusions: The job allows for configuration to ignore specific indexes when checking the status. This can be useful in scenarios where certain indexes are intentionally left in an unusable state temporarily.
• Return Status: Parameters within the job allow setting a return status based on the severity identified. Possible values are ALARM – 2, WARNING – 1, or OK – 0, which helps in prioritizing the response actions.

Update and Optimization Strategies

• With the default installation, essential updates and procedures are established to handle post-installation tasks, ensuring the job is aligned with current database contexts.
• In scenarios of upgrade, the job ensures that necessary parameters are in place, updating the procedure to accommodate any change in task configurations or operational prerequisites.

Dependencies and Compatibility

The job depends explicitly on internal database structures and assumes specific permissions (noted as ‘_priv_read_all_indexes’). It requires read access to all indexes and is compatible exclusively with Oracle database instances that support the required SQL functionality and schema visibility.

Automated Handling and Error Management

This job involves sophisticated error handling that captures exceptions, logs them, and updates check values for system monitoring utilities, providing robust operational support and proactive maintenance capabilities.