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

8 votes208 views2 answers
Grant all privileges database postgres to user
SQL
1 vote204 views1 answer
Sql into vs insert into
SQL
1 vote183 views1 answer
Jooq convert using gbk
SQL
3 votes228 views1 answer
Postgres update column with value from another table
SQL
4 votes194 views1 answer
Refresh postgres config
SQL
0 vote238 views1 answer
T-SQL Create Trigger
SQL
0 vote203 views1 answer
Mysql return column names when table is empty
SQL
-1 vote249 views3 answers
Error: mysql shutdown unexpectedly.
SQL
0 vote195 views1 answer
Sql column contains special character
SQL
0 vote192 views1 answer
Flush user resource mysql
SQL