Oracle find unusable indexes

SELECT OWNER, INDEX_NAME, NULL AS PARTITION_NAME, NULL AS SUBPARTITION_NAME, TABLESPACE_NAME
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE'
UNION ALL	-- Index partitions:
SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, NULL AS SUBPARTITION_NAME, TABLESPACE_NAME
FROM DBA_IND_PARTITIONS
WHERE STATUS = 'UNUSABLE'
UNION ALL	-- Index subpartitions:
SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS = 'UNUSABLE';

Source: orafaq.com

Similar codes

0 vote295 views2 answers
FIND OUT THE NAME HIGHEST SALARY SQL
SQL
0 vote333 views1 answer
Update mysql from paypal shopping cart and ipn
SQL
0 vote313 views1 answer
Database schema for mcqs type exam in sql
SQL
0 vote312 views1 answer
Declare or use variable in snowflake
SQL
1 vote270 views1 answer
Ef core 5.0 usesqlserver not found
SQL
0 vote458 views2 answers
Create new table from existing table with data in sql server
SQL
0 vote284 views3 answers
Remove folders from remote git
SQL
1 vote292 views1 answer
Mysql select from outside
SQL
6 votes259 views1 answer
Sqlite foreign key
SQL
0 vote270 views1 answer
Sqlalchemy query return only n results
SQL