インデックス情報の確認

テーブルとインデックスの関係だけならuser_indexesだけで十分です。
インデックスのカラムも知りたければこのクエリで確認できます。

set pages 10000
set lines 120
column table_name format a32
column index_name format a30
column column_name format a20
column tablespace_name format a20
select i.table_name,i.index_name,c.column_name,i.tablespace_name
 from dba_indexes i, dba_ind_columns c
 where i.index_name=c.index_name
  and i.owner=c.index_owner
  and i.table_name in ('TBL_USER1','TBL_PAGEACCESS1')
 order by i.table_name,i.index_name,c.COLUMN_POSITION;

実行例は次の通り。

TABLE_NAME        INDEX_NAME        COLUMN_NAME  TABLESPACE_NAME
----------------- ----------------- ------------ ---------------
TBL_PAGEACCESS1   IXT1_PAGEACCESS1  USER_ID      SSS_I_IDX
TBL_PAGEACCESS1   IXT1_PAGEACCESS1  PAGE_ID      SSS_I_IDX
TBL_PAGEACCESS1   IXT2_PAGEACCESS1  USER_ID      SSS_I_IDX
TBL_PAGEACCESS1   IXT3_PAGEACCESS1  PAGE_ID      SSS_I_IDX
TBL_USER1         IXT1_USER1        USER_ID      SSS_I_IDX

追記 2011/02/15
インデックスのowner（スキーマ）を条件に含めないと、別スキーマ同名インデックスが重複するので修正