Creating a spatial index on an st_geometry attribute fails with underlying DBMS error ORA-04030

Description

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".

Cause

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.

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

Workaround