Friday, February 14, 2014

Fix ORA-30009: Not enough memory for CONNECT BY operation

How to fix ORA-30009:

SQL> create table too_big_tbl as
select ROWNUM n, dbms_random.value(100000, 999999) n2  from dual connect by level <= 100000000; 2
 select ROWNUM n from dual connect by level <= 100000000
                      *
ERROR at line 2:
ORA-30009: Not enough memory for CONNECT BY operation


Check pga_aggregate_target`s value. In my test instance it was not set.     

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
SQL> alter system set pga_aggregate_target = 100 scope = both;

System altered.


Then try again

SQL> create table too_big_tbl as
select ROWNUM n, dbms_random.value(100000, 999999) n2  from dual connect by level <= 100000000; 

Table created.

Done

3 comments:

  1. Hello Dear Ulfet,
    I am getting pleasure when i reading your post. I hope you will continue to write more frequently.

    I have problem when i adding data to table from random generator as above, and i applied your solution but i am still getting this error.
    Any ideas ?

    ReplyDelete
    Replies
    1. Hello Berhan,

      Thank you for your comment. Could you provide whole command (statement) and output? Because with out it, it is hard to imagine.
      Also provide version of Oracle release

      Delete