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 votes213 views1 answer
Sql count having
SQL
4 votes236 views6 answers
List all tables in postgres
SQL
10 votes216 views3 answers
How to fetch first 5 characters in sql
SQL
2 votes220 views1 answer
Mysql Incorrect column specifier for column
SQL
39 votes230 views5 answers
Sql drop column
SQL
3 votes224 views1 answer
Dbms_scheduler drop_job
SQL
0 vote270 views1 answer
Insert query return id mysql
SQL
1 vote233 views1 answer
Oracle create as select
SQL
0 vote171 views1 answer
Msg 241, level 16, state 1, line 1 conversion failed when converting date and/or time from character string.
SQL
-1 vote196 views1 answer
How to format tables in sqlplus
SQL