Übersicht über alle Indexes eines Users und den indizierten Spalten

SELECT I.INDEX_NAME
      ,I.INDEX_TYPE
      ,I.PARTITIONED
      ,S.PARTITION_NAME
      ,I.UNIQUENESS
      ,round(s.bytes/1024/1024,3) idx_size_mb
      ,I.TABLE_NAME
      ,C1.COLUMN_NAME AS COLUMN1
      ,C2.COLUMN_NAME AS COLUMN2      
      ,C3.COLUMN_NAME AS COLUMN3
      ,C4.COLUMN_NAME AS COLUMN4
      ,C5.COLUMN_NAME AS COLUMN5
      ,C6.COLUMN_NAME AS COLUMN6
  FROM    USER_INDEXES I
       , (SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE COLUMN_POSITION=1) C1
       , (SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE COLUMN_POSITION=2) C2
       , (SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE COLUMN_POSITION=3) C3
       , (SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE COLUMN_POSITION=4) C4
       , (SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE COLUMN_POSITION=5) C5
       , (SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE COLUMN_POSITION=6) C6
       , user_segments s
WHERE  I.INDEX_NAME=C1.INDEX_NAME
AND    I.INDEX_NAME=C2.INDEX_NAME (+)
AND    I.INDEX_NAME=C3.INDEX_NAME (+)
AND    I.INDEX_NAME=C4.INDEX_NAME (+)
AND    I.INDEX_NAME=C5.INDEX_NAME (+)
AND    I.INDEX_NAME=C6.INDEX_NAME (+)
AND    I.INDEX_NAME=S.SEGMENT_NAME (+)
ORDER BY i.TABLE_NAME,I.INDEX_NAME;