OracleObjects & schema

Unusable indexes and index partitions

Indexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full scans.

Not yet verified. How scripts are tested

ora-unusable-idx.sql

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN owner      FORMAT A204COLUMN index_name HEADING "INDEX"     FORMAT A305COLUMN part_name  HEADING "PARTITION" FORMAT A306COLUMN status     FORMAT A87 8SELECT owner, index_name, NULL AS part_name, status9FROM dba_indexes WHERE status = 'UNUSABLE'10UNION ALL11SELECT index_owner, index_name, partition_name, status12FROM dba_ind_partitions WHERE status = 'UNUSABLE'13UNION ALL14SELECT index_owner, index_name, subpartition_name, status15FROM dba_ind_subpartitions WHERE status = 'UNUSABLE'16ORDER BY 1, 2, 3;

Save it as ora-unusable-idx.sql and run it with SQL> @ora-unusable-idx.

Open in denrepo

Part of these runbooks

After patching

More Oracle scripts: Objects & schema

Generate DDL for a table and an indexUses DBMS_METADATA to write the CREATE statements for a table and one of its indexes into ddl_list.sql in your current directory. Prompts for the…
Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
Recompile invalid objectsRecompile one schema, or everything in the database with Oracle's utlrp script. Run the invalid objects check again afterwards.
Foreign keys that are probably not indexedForeign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or…
Disabled constraints and triggersConstraints and triggers someone switched off, often for a data load, and never switched back on.
Objects changed in the last 24 hoursApplication objects with DDL in the last day. Grants also update LAST_DDL_TIME, so not every row is a code change.