isitdevops.com/databases

REM Script to show the structure of all indexes on a specified table
REM to assist in SQL tuning

set pages 500;
set lines 100;
set feed off;
set arraysize 1;
set verify off;

column column_name format A30;
break on INDEX_NAME;

accept tablename prompt ‘Table name: ‘

select
a.index_name,b.column_name,b.column_position
from user_indexes a,user_ind_columns b
where
a.index_name = b.index_name and
a.table_name = ‘&tablename’
order by a.index_name,b.column_position;

set feed on;