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 vote296 views1 answer
Mysqldump ignore table
SQL
0 vote298 views1 answer
MySQL get all previous date record
SQL
7 votes310 views3 answers
Convert ansi sql
SQL
0 vote278 views1 answer
T-sql conditional order by multiple columns
SQL
0 vote285 views1 answer
Convert sqlalchemy.util._collections to list of string
SQL
0 vote263 views8 answers
Convert sql query to linq online
SQL
6 votes323 views3 answers
Rename column ORACLE
SQL
0 vote310 views1 answer
Express api ith mysql data
SQL
0 vote363 views1 answer
Selecting specific day in colum sql
SQL
2 votes343 views1 answer
Sql find not in list
SQL