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 vote142 views2 answers
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created at' in 'field list' (SQL: insert into `categories` (`category_name`, `user_id`, `created at`) values (sea fish, 1, 2021-09-24 04:17:31))
SQL
0 vote137 views1 answer
T-sql email validation
SQL
1 vote146 views1 answer
Convert varchar to int in sqlite
SQL
2 votes136 views1 answer
Rails execute sql
SQL
1 vote137 views1 answer
Where date = max(date) in sql
SQL
50 votes210 views4 answers
Sql alter type of column
SQL
0 vote147 views1 answer
Wordpress delete post revisions older than date "sql"
SQL
24 votes148 views6 answers
Replace mysql
SQL
2 votes131 views1 answer
Sql server find table name
SQL
2 votes140 views2 answers
HOW TO FIND MEDIAN IN SQL FOR BOTH IDD AND EVEN
SQL