ORA-30009: CONNECT BY 操作内存不足

来源：互联网发布：网络营销软件站编辑：程序博客网时间：2024/06/05 08:24

SQL> insert into t1 select 'test','test','test',rownum,rownum,'test',sysdate,sysdate,'test','test','','','',rownum,'test',rownum from dual connect by rownum<=3200000;
insert into t1 select 'test','test','test',rownum,rownum,'test',sysdate,sysdate,'test','test','','','',rownum,'test',rownum from dual connect by rownum<=3200000
*
ERROR at line 1:

可以使用xmltable：

测试：

SQL> select 'test',rownum,sysdate from xmltable('1 to 20');

'TES ROWNUM SYSDATE
---- ---------- -------------------
test 1 2016-10-25 10:17:58
test 2 2016-10-25 10:17:58
test 3 2016-10-25 10:17:58
test 4 2016-10-25 10:17:58
test 5 2016-10-25 10:17:58
test 6 2016-10-25 10:17:58
test 7 2016-10-25 10:17:58
test 8 2016-10-25 10:17:58
test 9 2016-10-25 10:17:58
test 10 2016-10-25 10:17:58
test 11 2016-10-25 10:17:58
test 12 2016-10-25 10:17:58
test 13 2016-10-25 10:17:58
test 14 2016-10-25 10:17:58
test 15 2016-10-25 10:17:58
test 16 2016-10-25 10:17:58
test 17 2016-10-25 10:17:58
test 18 2016-10-25 10:17:58
test 19 2016-10-25 10:17:58
test 20 2016-10-25 10:17:58

20 rows selected.

Elapsed: 00:00:00.33

然后使用xmltable进行insert...select操作：

SQL> insert into t1 select 'test','test','test',rownum,rownum,'test',sysdate,sysdate,'test','test','','','',rownum,'test',rownum from xmltable('1 to 3200000');

3200000 rows created.

Elapsed: 00:00:43.07
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

0 0