Index detail

col OWNER for a15
col INDEX_NAME for a20
col DEGREE for a15
set lin 450

select a.owner, index_name, tablespace_name TBS, num_rows, decode(a.degree,'DEFAULT','1',degree) degree , ini_trans initran, clustering_factor CF, to_char(last_analyzed,'dd-mm-yy hh24:mi') LAST_ANALYZED, to_char(last_ddl_time,'dd-mm-yy hh24:mi') created, partitioned PART
from dba_indexes a, dba_objects b
where lower(a.table_name) =lower('&&TABLE_NAME') and a.owner ='&&OWNER_NAME' and a.index_name = b.object_name and a.owner = b.owner and b.object_type ='INDEX' union
select index_name, partition_name, tablespace_name TBS, num_rows, null,ini_trans, clustering_factor, to_char(last_analyzed,'dd-mm-yy hh24:mi') , null , null
from dba_ind_partitions
where index_name in (
select index_name
from dba_indexes
where table_name ='&&TABLE_NAME' and index_owner='&&OWNER_NAME' and partitioned ='YES' )

 

col column_name for a30
select index_name, column_name, column_position
from dba_ind_columns
where ( index_owner,index_name) in (
select owner,index_name
from dba_indexes
where table_name =upper('&&TABLE_NAME') and owner =upper('&&OWNER_NAME')) order by index_name, column_position