ORA-30009: Not enough memory for CONNECT BY operation

When using connect by to generate large amount testing dataset, it can easier hit error: "ORA-30009: Not enough memory for CONNECT BY operation".

SQL> create table t1 as
  2  select level as id,rpad('a',10,'a') as value
  3  from dual
  4  connect by level <=1000000;
Table created.

SQL> create table t1 as
  2  select level as id, rpad('a',20,'a')  as value
  3  from dual
  4  connect by level <=10000000;
from dual
     *
ERROR at line 3:
ORA-30009: Not enough memory for CONNECT BY operation

Below is the workaround to bypass the issue:

SQL> create table t1 as
  2  select rownum as id, rpad('a',20,'a') as value from
  3  (select level from dual connect by level <=10000),
  4  (select level from dual connect by level <=10000);
Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
 100000000

SQL> select bytes from dba_segments where owner='DONGHUA' and segment_name='T1';
     BYTES
----------
3690987520