java test30009_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;