Knowledge Base : Re-Create Oracle Indizes from dictionary tables

select 
    'drop index ' || ui.index_name || '; create ' ||
    case ui.uniqueness
        when 'UNIQUE' then 'unique'
        else null
    end  
    || ' index ' || ui.index_name || ' on ' || ui.table_name || ' ('|| listagg(uic.column_name, ',') within group (order by uic.column_position) ||') local logging tablespace DWH_DM;'
from user_indexes ui
join user_tables ut on ut.table_name = ui.table_name
join user_ind_columns uic on uic.index_name = ui.index_name and uic.table_name = ut.table_name
where --ui.index_name like 'F%' 
    --and ui.table_owner = 'DWH_DM' 
    and ui.partitioned = 'NO' 
    --and ui.table_name like 'F_%'
    and ut.partitioned = 'YES'
    and ui.index_type = 'NORMAL'
group by 
    ui.index_name,
    ui.uniqueness,
    ui.table_name
order by
    ui.table_name,
    ui.index_name
;