set serveroutput on size 500000
set linesize 400
set pagesize 0
undefine owner
set verify off
set heading off
set feedback off
spool create_indexes.sql
declare
cursor c_index(p_owner varchar2) is
  select index_name, partitioned, table_name, tablespace_name, uniqueness
  from   dba_indexes
  where  owner = p_owner
  order by table_name;

cursor c_column(p_owner varchar2, p_index varchar2) is
  select column_position, column_name
  from   dba_ind_columns
  where  index_name  = p_index
  and    index_owner = p_owner
  order by column_position;
  
cursor c_partition(p_owner varchar2, p_index varchar2) is
  select partition_position, partition_name, tablespace_name
  from   dba_ind_partitions
  where  index_name   = p_index
  and    index_owner  = p_owner
  order by partition_position;  

begin

for c_index_rec in c_index('&&owner') loop

  dbms_output.put('create');
  if c_index_rec.uniqueness = 'UNIQUE' then
     dbms_output.put(' unique');
  end if;
dbms_output.put(' index '||c_index_rec.index_name);
dbms_output.put('   ON '||c_index_rec.table_name);

  -- ******************
  -- Column definitions
  -- ******************
  for c_column_rec in c_column('&&owner',c_index_rec.index_name) loop
    if c_column_rec.column_position = 1 then
      dbms_output.put('('||c_column_rec.column_name);
    else
      dbms_output.put(','||c_column_rec.column_name);
    end if;
  end loop;  -- column loop
  dbms_output.put_line(')');

  -- ************
  -- Partitioned? 
  -- ************
  if c_index_rec.partitioned = 'NO' then
     dbms_output.put_line('tablespace '||c_index_rec.tablespace_name);
  else
  -- **********
  -- Partitions
  -- **********
     dbms_output.put_line('LOCAL');
     for c_partition_rec in c_partition('&&owner', c_index_rec.index_name) loop
         if c_partition_rec.partition_position = 1 then
            dbms_output.put('(PARTITION '||c_partition_rec.partition_name);
         else
            dbms_output.put(',PARTITION '||c_partition_rec.partition_name);
         end if;
         dbms_output.put_line(' tablespace '||c_partition_rec.tablespace_name);
     end loop;
     dbms_output.put_line(')');
  end if;

  dbms_output.put_line('/');
end loop;  -- index loop
end;
/
spool off
