ORA-30009: Not enough memory for CONNECT BY operation

 Symptom:

SQL> insert into t (value) select rpad('a',10,'a') from dual connect by level <= 10000000;

Error starting at line : 1 in command -insert into t (value) select rpad('a',10,'a') from dual connect by level <= 10000000

Error report -

ORA-30009: Not enough memory for CONNECT BY operation

 Solution: Increase the sort area size

SQL> alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size=1000000000;

Session altered.

SQL> insert into t (value) select rpad('a',10,'a') from dual connect by level <= 10000000;

10,000,000 rows inserted.

SQL> select pga_used_mem,pga_alloc_mem,pga_max_mem from v$process 

  2* where addr=(select paddr from v$session where sid=userenv('SID'));

PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM

------------ ------------- -----------

     6678135       7287743   654783423