Structure - Indexes

Index info by table

select i.index_name
, i.tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from  dba_indexes i
, dba_segments s
where  i.index_name = s.segment_name
and  table_name like '&table'
order  by 2, 1
/
Output:

Enter value for table: LOCATION
old   7: and    table_name like '&table'
new   7: and    table_name like 'LOCATION'

INDEX_NAME                     TABLESPACE_NAME                               Size MB
------------------------------ ---------------------------------------- ------------
LOCATION_PK                    ANDY                                             1203

1 row selected.

Show indexed columns

select  column_name
from  dba_ind_columns
where  index_name = '&index'
order  by column_position
/
Output:

Enter value for index: LOCATION_PK
old   3: where  index_name = '&index'
new   3: where  index_name = 'LOCATION_PK'

COLUMN_NAME
--------------------
LOCATION_ID

1 row selected.