I encountered this problem this morning and came up with this script, hopes it helps someone!// Lars Nordvall Triona AB/* Use this script to create S###_IX2 indexes on spatialindex tables in ArcSDE 10.1 */
/* Workaround for NIM084235 http://support.esri.com/en/bugs/nimbus/TklNMDg0MjM1 */
set serveroutput ON;
declare
cnt number(38);
sqlStr varchar2(255);
spatialIndexTable varchar2(32);
indexName varchar2(32);
type cref is ref cursor;
c1 cref;
begin
cnt := 0;
sqlstr := 'select table_name from user_tables where table_name like ''S%_IDX%'' order by 1';
DBMS_OUTPUT.PUT_LINE(sqlStr );
open c1 for sqlstr;
LOOP
fetch c1 into spatialIndexTable;
EXIT WHEN c1%NOTFOUND;
cnt := cnt + 1;
indexName := substr(spatialIndexTable,0,length(spatialIndexTable)-1) || '2';
sqlStr := 'create index ' || indexName || ' on ' || spatialIndexTable || '(SP_ID)';
DBMS_OUTPUT.PUT_LINE( sqlstr );
EXECUTE IMMEDIATE sqlStr;
end LOOP;
close c1;
DBMS_OUTPUT.PUT_LINE('Index creation finished (' || to_char(cnt) || ' indexes created)');
end;
/
