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