ORA-30009: Not enough memory for CONNECT BY operation

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:

nimish@garg> 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 :)

nimish@garg> 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.


Related Post:
- Oracle: Connect By Prior (Hierarchical Query)
- ORA-01436: CONNECT BY loop in user data
- Employee Manager Hierarchy - Recursive Subquery Factoring
- Oracle: Fibonacci Series by SQL
- Graph Shortest Path Solution by SQL
- SQL Interview Question Answers

3 comments:

  1. Thanks for publishing this informative article.
    I'm a long time reader but I've never actually left a comment.
    I've bookmarked your site and shared this on my Facebook.

    Thanks again for a quality article!

    ReplyDelete
  2. Artificial Intelligence Virtual Reality platforms combined with the power of 3D content, digital health applications enable virtual training, patient engagement, education and health coaching

    vr training

    ReplyDelete