Friday, July 13, 2012

ORA-30009: Not enough memory for CONNECT BY operation

Getting the error ORA-30009 when trying to execute a statement.


create table t
as
select
        rownum as id
        , mod(rownum, 10) + 1 as attr1
        , rpad('x', 100) as filler
from
          dual
connect by
          level <= 1000000
;


          dual
          *
ERROR at line 8:
ORA-30009: Not enough memory for CONNECT BY operation

To fix this increase the pga_aggregate_target value.
SQL> show parameter pga_a

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 128M
SQL> show parameter workarea_s

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

 SQL> alter system set pga_aggregate_target=200M scope=both;

System altered.

SQL> show parameter pga_a

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 200M

create table t
as
select
        rownum as id
        , mod(rownum, 10) + 1 as attr1
        , rpad('x', 100) as filler
from
          dual
connect by
          level <= 1000000
;

Table created.



No comments: