Blog

Learnomate Technologies > Blog > Oracle DBA > ORA-30009: Not enough memory for CONNECT BY operation

ORA-30009: Not enough memory for CONNECT BY operation

November 10, 2021
Posted by: Ankush Thavali
Category: Oracle DBA

No Comments

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&gt; select count(*) from dual connect by level &lt;= 100000000;
select count(*) from dual connect by level &lt;= 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&gt; select count(*) from
2 (select level from dual connect by level &lt;= 10000),
3 (select level from dual connect by level &lt;= 10000);

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

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

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&gt; select count(*) from dual connect by level &lt;= 100000000;
select count(*) from dual connect by level &lt;= 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&gt; select count(*) from
2 (select level from dual connect by level &lt;= 10000),
3 (select level from dual connect by level &lt;= 10000);

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

Blog

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:

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

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

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:

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

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

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.

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

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.

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