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')