Bug: Creating a spatial index on an st_geometry attribute fails with underlying DBMS error ORA-04030
Creating a spatial index on an st_geometry attribute can fail with Oracle error ORA-04030 when allocating 16332 bytes (pga heap, kgh stack).
"SQL> CREATE INDEX shape_idx ON parcels (shape)
2 INDEXTYPE IS sde.st_spatial_index
3 PARAMETERS ('st_grids=1000 st_srid=1');
CREATE INDEX shape_idx ON parcels (shape)...
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-04030: out of process memory when trying to allocate 16332 bytes (pga heap,kgh stack)
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1092
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1820".
There is an Oracle memory leak when using a reference cursor and the BULK COLLECT option to fetch a result set. Each iteration of the fetch operation leaks additional memory, which can potentially lead to the Oracle ORA-04030 error.
The Oracle administrator can identify the memory usage when a session is creating a spatial index by executing the following query as the SYS or SYSTEM user for the session and executing the CREATE INDEX statement.
SELECT n.name, s.value
FROM v$statname n , v$sesstat s
WHERE s.sid = <session's sid>
AND n.statistic# = s.statistic#
AND n.name IN ('session pga memory','session pga memory max')
When the CREATE INDEX operation completes and the reference cursor has been closed, the memory is released.
If this error is encountered when creating a spatial index or alter an existing spatial index with the rebuild clause, please contact ESRI Technical Support and reference this ESRI Knowledge Base article.