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.