Recreate Oracle Spatial indexes

update-spatial-indexes.sql

DECLARE
  TYPE curs IS REF CURSOR;
  c curs;
  q varchar2(200);
  idx VARCHAR(200);
  tab VARCHAR(200);
  col VARCHAR(200);
BEGIN
  q := 'select index_name,table_name,column_name from user_sdo_index_info';
  OPEN c FOR q;
  LOOP
    FETCH c into idx,tab,col;
    EXIT WHEN c%NOTFOUND;
    SYS.dbms_output.put('Supression de l''index ' || idx || ' sur ' || tab);
    execute immediate 'drop index ' || idx;
    SYS.dbms_output.put_line(' ok');
    SYS.dbms_output.put('Création de l''index ' || idx || ' sur ' || tab);
    execute immediate 'create index ' || idx || ' on ' || tab || '(' || col || ') INDEXTYPE IS mdsys.spatial_index';
    SYS.dbms_output.put_line(' ok');
  END LOOP;
  CLOSE c;
END;