ORA-30009: Not enough memory for CONNECT BY operation

SQL> create table t2 nologging parallel 7  as select rownum x from dual connect by level<=10000000;
create table t2 nologging parallel 7  as select rownum x from dual connect by level<=10000000
                                                              *
 ERROR ¦b¦æ 1:
ORA-30009: Not enough memory for CONNECT BY operation

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

workarea_size_policy     = MANUAL  ==> 手动管理PGA 并非说是“消耗的PGA内存不会自动回收”

手动PGA管理时 内存排序空间受到sort_area_size 参数的影响， 单个operation 使用的排序空间不能超过 sort_area_size

SQL> show parameter sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer     0
sort_area_size                       integer     65536

SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;
CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000
                                                                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation


SQL> alter session set sort_area_size=6553600;

Session altered.

SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;

Table created.


自动PGA管理下 sort_area_size是废弃参数， 单个 operation 使用的排序内存空间不超过  pga_aggregate_target的 5% 或者 隐藏参数  _pga_max_size

No RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size, whichever is smaller.