create or replace
FUNCTION REBUILD_SPATIAL_INDEXES RETURN VARCHAR2 AS 
  cursor c_index is select index_name from user_indexes where ITYP_NAME ='SPATIAL_INDEX';
  v_sql VARCHAR2 (30000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  for rec in c_index
        loop
          begin
            select dbms_metadata.get_ddl('INDEX',rec.index_name,user) into v_sql from dual;
            execute immediate 'drop index ' || rec.index_name;   
            dbms_output.put_line('DDL:    ' || v_sql);  
            execute immediate v_sql;
            dbms_output.put_line('SUCCESS:' || rec.index_name);       
          exception
            when others then
            dbms_output.put_line('ERROR:  ' || rec.index_name || '  -  ' || SQLERRM);
           end; 
        end loop;
  return 'DONE';
END REBUILD_SPATIAL_INDEXES;