Blog

ORA-30009: Not enough memory for CONNECT BY operation

Cause: The memory size was not sufficient to process all the levels of the hierarchy specified by the CONNECT BY clause.

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.

With this post, I am sharing a very simple tip which I recently used to avoid ORA-30009. Last week one of my colleague faced ORA-30009 and when he googled following two solutions were given in most of sites:

1. Use Pipeline function using loop instead of CONNECT BY.
2. Increase PGA_AGGREGATE_TARGET, because this error came due to shortage of PGA memory.

Both of the above solutions will work, but in many cases ORA-30009 does not require any of above steps and can be fixed by breaking SQL to generate two or more sets of data and then using Cartesian product.

Let's do a quick demo:

ankush@thavali> select count(*) from dual connect by level <= 100000000;
select count(*) from dual connect by level <= 100000000
*
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation

Great! we faced ORA-30009, now let's fix it :

ankush@thavali> select count(*) from
2 (select level from dual connect by level <= 10000),
3 (select level from dual connect by level <= 10000);

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

Done!
I hope you have enjoyed this little trick on ORA-30009. Feel free to comment.

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.