Find indexes status and assigned columns for a table



It is very common that you want to know / see the available index on a table.
And also you sometime want to know the columns which are assigned to the indexes.
Following views would be helpful in these cases
dba_ind_columns
all_ind_columns

 

Query to Find the indexes with assigned column is 

set pagesize 50000 verify off echo off

col table_name head ‘Table Name’ format a20
col index_name head ‘Index Name’ format a25
col column_name head ‘Column Name’ format a30

break on table_name on index_name

select    table_name, index_name, column_name
from    all_ind_columns
where    table_name like upper(‘&Table_Name’)
order by table_name, index_name, column_position
/

Now you want to know the status of the indexes also,We can use below queries

Normal Index

SELECT owner, index_name, tablespace_name,status
FROM dba_indexes;

If you want to know unusable index, then we  can use below query

SELECT owner, index_name, tablespace_name,status
FROM dba_indexes
WHERE status = ‘UNUSABLE’;

Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS;

If you want to know unusable ind partitions, then we  can use below query

SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS
WHERE status = ‘UNUSABLE’;

Index subpartitions:
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS;

If you want to know unusable ind subpartitions, then we  can use below query

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS
WHERE status = ‘UNUSABLE’;

With 12c, Oracle has introduced the concept of Partial indexes in Partitioned tableThe *_INDEXES view has been modified to include an INDEXING column, which indicates if the index is FULL or PARTIAL.

Check the indexing status of the index.
SELECT index_name,
indexing
FROM dba_indexes
ORDER BY 1;

INDEX_NAME    INDEXING
————————- ——-
EXP_INDEX_N1 PARTIAL

This happened when we have indexing partial tag in the create index statement.
CREATE INDEX EXP_INDEX_NI ON tab_name(col1) Local INDEXING PARTIAL;


Leave a Reply