Create Spatial Index Throws ORA-01000: maximum open cursors exceeded (Doc ID 1613678.1)

Last updated on FEBRUARY 21, 2014

Applies to:

Oracle Spatial and Graph - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms

While creating spatial index failed with following errors:

SQL> DECLARE
  2 icount INTEGER := NULL;
  3 begin
  4 FOR icount IN 1 .. 500 LOOP
  5 execute immediate 'CREATE TABLE POINTS_' || icount ||'( FID NUMBER(10), GEOM MDSYS.SDO_GEOMETRY)';
  6 END LOOP;
  7 end;
  8 /
variable name_1 varchar2(30);
DECLARE
  icount INTEGER := NULL;
begin
  FOR icount IN 1 .. 500 LOOP
  :name_1 := 'POINTS_'|| icount;
  insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values (:name_1, 'GEOM', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 0, 1000, 5),SDO_DIM_ELEMENT('Y', 0, 1000, 5)), null);
  END LOOP;
end;
/
DECLARE
  icount INTEGER := NULL;
begin
  FOR icount IN 1 .. 500 LOOP
  execute immediate 'CREATE INDEX POINTS_S'|| icount || ' ON POINTS_' || icount ||' (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (''SDO_INDX_DIMS=2 TABLESPACE=USERS LAYER_GTYPE=POINT'')';
  END LOOP;
end;
/

PL/SQL procedure successfully completed.

SQL> SQL> 2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.

SQL> 2 3 4 5 6 7 8 DECLARE
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in spatial index: [mdrcrtxfergm]
ORA-13249: Stmt-Execute Failure: DROP TABLE "M1_17EEF$$"
ORA-29400: data cartridge error
ORA-01000: maximum open cursors exceeded
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
ORA-06512: at line 5

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms