ORA-30009: Not enough memory for CONNECT BY operation

ORA-30009: Not enough memory for CONNECT BY operation error occurs when the memory size was not sufficient to process all the levels of the hierarchy specified by the query. The memory available is insufficient to process all of the levels specified in the query. To run within the memory size, either the memory size should be increased or the levels configured in the query should be reduced. All of the levels specified in the query require more memory than the system has. Hence the error ORA-30009: Not enough memory for CONNECT BY operation is thrown.

The query can have up to 892406 levels in Oracle. If you configure more than 892406 levels, Oracle will be unable to process all of the levels in the query. If the WORKAREA SIZE POLICY is set to AUTO, the PGA AGGREGATE TARGET value should be set to a large enough value to process all of the levels specified in the query. If the WORKAREA SIZE POLICY is set to MANUAL, the SORT AREA SIZE value should be set to a large enough value to handle all of the levels specified in the query. Oracle will display an error ORA-30009: Not enough memory for CONNECT BY operation if the values are configured with insufficient value.



The Problem

If the CONNECT BY levels in the query are configured with a large value, Oracle will be unable to allocate enough memory to process all of the levels specified in the query. The query will execute in an out-of-memory state. The error will be thrown if there is insufficient memory while executing the query. The CONNECT BY query levels should be set to a lower number. The query can have a maximum of 892406 levels. This value may vary depending on the amount of memory available in the system.

select count(*) from dual connect by level <= 100000000;

Error

ORA-30009: Not enough memory for CONNECT BY operation
30009. 0000 -  "Not enough memory for %s operation"
*Cause:    The memory size was not sufficient to process all the levels of the
           hierarchy specified by the query.
*Action:   In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
           a reasonably larger value.
           Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
           reasonably larger value.


Solution 1

If the CONNECT BY level in the query is set to a large value, there is insufficient memory to process all of the levels specified in the query. To process within the memory limit, the level value should be reduced. The maximum level is set to 892406 by default. This will differ depending on the amount of system memory available. To process all levels within the memory limit, the level’s large value should be replaced with a smaller value.

Error

select count(*) from dual connect by level <= 100000000;

Solution

select count(*) from dual connect by level <= 892406;

Output

COUNT(*)
--------
892406


Solution 2

If you want to execute with a large level value, the level should be split into queries, and the product of two query outputs will produce a large amount of values, just like a large level value. The memory size could be divided between two queries, and the product would use less memory than if the large value specified in the level of a query was used.

Error

select count(*) from dual connect by level <= 100000000;

Solution

select count(*) from
  (select level from dual connect by level <= 10000),
  (select level from dual connect by level <= 10000);

Output

COUNT(*)
--------
100000000


Solution 3

The query above will return the results of two level queries with values that are a combination of the values specified in the query. This will not produce the continuous number that the single level query will. The following query can be used to achieve the single continuous number in the level query.

Error

select count(*) from dual connect by level <= 100000000;

Solution

select level1, level2, ((level1-1)*10) + (level2-1) + 1 as ID from
  (select level as level1 from dual connect by level <= 10),
  (select level as level2 from dual connect by level <= 10);

Output

LEVEL1    LEVEL2  ID
--------------------
1	1	1
1	2	2
1	3	3
.................
.................



Leave a Reply