INDEX STATUS CHECK

 WITH index_list AS (

    SELECT 'SCHEMA1' AS index_owner, 'INDEX1' AS index_name FROM dual

    UNION ALL

    SELECT 'SCHEMA2', 'INDEX2' FROM dual

    UNION ALL

    SELECT 'SCHEMA3', 'INDEX3' FROM dual

)

SELECT il.index_owner,

       il.index_name,

       NVL(aip.status, 'UNUSABLE') AS status,

       COUNT(aip.partition_name) AS unusable_partition_count

FROM index_list il

LEFT JOIN all_ind_partitions aip

    ON il.index_owner = aip.index_owner

    AND il.index_name = aip.index_name

    AND aip.status = 'UNUSABLE'

GROUP BY il.index_owner, il.index_name, NVL(aip.status, 'UNUSABLE') ORDER BY il.index_owner, il.index_name;

SELECT index_owner,

       index_name,

       partition_name,

       status

FROM all_ind_partitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

ORDER BY index_owner, index_name, partition_name;

SELECT 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name || ';' AS rebuild_statement

FROM all_ind_partitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

ORDER BY index_owner, index_name, partition_name;

-- Query for unusable partitions

SELECT index_owner, index_name, 'PARTITION' AS object_type, partition_name, status

FROM all_ind_partitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

UNION ALL

-- Query for unusable subpartitions

SELECT index_owner, index_name, 'SUBPARTITION' AS object_type, subpartition_name, status

FROM all_ind_subpartitions

WHERE (index_owner, index_name) IN (

    ('SCHEMA1', 'INDEX1'),

    ('SCHEMA2', 'INDEX2'),

    ('SCHEMA3', 'INDEX3')

)

AND status = 'UNUSABLE'

ORDER BY index_owner, index_name, object_type;

4. Broader Scope: Check All Unusable Partitions

If this is a health check, you might want to remove the IN clause to see all unusable index partitions in your schema, not just a specific list.

Modified Query:

SQL

SELECT index_owner,
       index_name,
       COUNT(*) as partition_count
FROM all_ind_partitions
WHERE status = 'UNUSABLE'
AND index_owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMA3') -- Optional: scope to specific schemas
GROUP BY index_owner, index_name;

Why this is useful: This gives you a more comprehensive view of the entire environment, which is often a better approach for monitoring and alerting.