The following script can be used to generate index creation scripts for all tables in the schema
select table_name, index_name,
indxtype||index_name||' on '||table_name||'('||
ltrim(rtrim(max(decode(r1,1,column_name,null))||','||
max(decode(r1,2,column_name,null))||','||
max(decode(r1,3,column_name,null))||','||
max(decode(r1,4,column_name,null))||','||
max(decode(r1,5,column_name,null))||','||
max(decode(r1,6,column_name,null))||','||
max(decode(r1,7,column_name,null))||','||
max(decode(r1,8,column_name,null))||','||
max(decode(r1,9,column_name,null))||','||
max(decode(r1,10,column_name,null))||','||
max(decode(r1,11,column_name,null))||','||
max(decode(r1,12,column_name,null))||','||
max(decode(r1,13,column_name,null))||','||
max(decode(r1,14,column_name,null))||','||
max(decode(r1,15,column_name,null)),','),',')||');'
FROM
(
select a.table_name, a.index_name,
decode(a.uniqueness, 'UNIQUE', 'create unique index ', decode(a.index_type, 'BITMAP', 'create bitmap index ', 'create index ')) indxtype
b.column_name, b.column_position,
row_number() over(partition by a.table_name, a.index_name order by b.column_position) r1
from user_indexes a, user_ind_columns b
where
a.index_name=b.index_name and
a.table_name=b.table_name and
a.index_type IN('NORMAL', 'BITMAP'))
GROUP BY table_name, index_name, uniqueness;