ORA-30009:CONNECTBY操作内存不足教程

ORA-30009: CONNECT BY 操作内存不足，10g开始支持XML，可改为xmltable

SQL> drop table t\_range purge;

SQL> create table t\_range (id number not null PRIMARY KEY, test\_date date) partition by range (test\_date)
(
partition p\_2014\_7 values less than (to\_date('2014-08-01', 'yyyy-mm-dd')),
partition p\_2014\_8 values less than (to\_date('2014-09-01', 'yyyy-mm-dd')),
partition p\_2014\_9 values less than (to\_date('2014-10-01', 'yyyy-mm-dd')),
partition p\_2014\_10 values less than (to\_date('2014-11-01', 'yyyy-mm-dd')),
partition p\_2014\_11 values less than (to\_date('2014-12-01', 'yyyy-mm-dd')),
partition p\_2014\_12 values less than (to\_date('2015-01-01', 'yyyy-mm-dd')),
partition p\_max values less than (MAXVALUE)
) nologging;

SQL> insert /+append / into t\_range select rownum,
to\_date(to\_char(sysdate - 120, 'J') +
trunc(dbms\_random.value(0, 120)),
'J')
from dual
connect by level <= 2000000;
insert /+append / into t\_range select rownum,
*
第 1 行出现错误:
ORA-30009: CONNECT BY 操作内存不足
已用时间: 00: 00: 10.28
SQL> rollback;
回退已完成。

SQL> insert /+append / into t\_range select rownum,
to\_date(to\_char(sysdate - 120, 'J') +
trunc(dbms\_random.value(0, 120)),
'J')
from xmltable('1 to 2000000');
已创建2000000行。
已用时间: 00: 00: 28.76
SQL> commit;