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.