Oracle Database / Operations & Incident Response
ORA-01502: locate the unusable index or partition
A failed DML operation can name an unusable index component after maintenance. Inspect both overall and partition-level state.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT owner, index_name, status, partitioned FROM dba_indexes WHERE owner = 'APP_OWNER' AND index_name = 'APP_ORDERS_IX';
SELECT partition_name, status FROM dba_ind_partitions WHERE index_owner = 'APP_OWNER' AND index_name = 'APP_ORDERS_IX';Read the result
A partitioned index may need partition or subpartition inspection even if the overall status is not directly actionable.
Resolve and verify
Identify the maintenance that invalidated the component and plan a supported targeted repair. Verify constraints and DML after the repair.
Operational precautions
Do not rebuild every index or skip unusable indexes without considering constraint enforcement and workload impact.