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

2 votes196 views1 answer
How to make multiple keys as primary in bdms
SQL
0 vote192 views1 answer
Vbscript clean up ADODB.Recordset
SQL
0 vote171 views1 answer
Importerror no module named mysql.connector raspberry pi
SQL
1 vote177 views3 answers
How to filter repeated same result using sql query
SQL
8 votes178 views3 answers
Alter user root mysql
SQL
0 vote143 views0 answer
SQL
1 vote182 views2 answers
ImportError: No module named mysql.connector
SQL
4 votes194 views2 answers
Sql offset
SQL
0 vote180 views1 answer
Hw to delete a procedure in pl sql
SQL
0 vote187 views1 answer
Mysql master slave setup with keepalived
SQL