ORA-30009：内存不足，无法进行CONNECT BY操作/ ORA-01788：此查询块中需要CONNECT BY子句

2024-05-21 • 问答

Oracle新手-我正在尝试运行插入语句来生成大量数据。

原始查询是这样：

INSERT INTO HR.fastData (ID) 
SELECT 1 + (Level -1) * 1 
FROM dual connect by Level < 100000000;

收到的第一个错误：

ORA-30009：内存不足，无法进行CONNECT BY操作

我关注了the guidance provided here

修改后的查询：

INSERT INTO HR.fastData (ID) 
SELECT 1 + (Level -1) * 1 
FROM
    (select level from dual connect by Level < 10000),(select level from dual connect by Level < 10000);

我收到的下一个错误：

ORA-01788：此查询块中需要CONNECT BY子句

修改后的查询现在看起来像这样：

INSERT INTO HR.fastData (ID) 
SELECT 1 + (Level -1) * 1 
FROM DUAL CONNECT BY 
     (select Level from dual connect by Level < 10000),(select Level from dual connect by level < 10000);

在多次尝试查询的不同变体之后，我无法使其正确执行。我是否正确使用/放置CONNECT BY语句？不胜感激。

谢谢！

wangjing0613 回答：ORA-30009：内存不足，无法进行CONNECT BY操作/ ORA-01788：此查询块中需要CONNECT BY子句

您想要的查询似乎是

SELECT ((lvl1-1)*10) + (lvl2-1) + 1 as ID
  FROM (select level as lvl1 from dual connect by Level <= 10000)
  CROSS JOIN (select level as lvl2 from dual connect by Level <= 10000);

我不能保证您的系统可以一次生成所有这些数字，但是原则上这是可行的。 Here's a db<>fiddle显示了此查询在每个子查询限制为10个级别（总共产生100行）时起作用。

var x number;
exec :x := 10;
SELECT          level FROM dual connect by level <= :x
union all
select 1 * :x + level from dual connect by level <= :x
union all
select 2 * :x + level from dual connect by level <= :x
union all
select 3 * :x + level from dual connect by level <= :x;

connect-by oracle sql sqlplus

本文链接：https://www.f2er.com/2794214.html

ORA-30009：内存不足，无法进行CONNECT BY操作/ ORA-01788：此查询块中需要CONNECT BY子句

wangjing0613 回答：ORA-30009：内存不足，无法进行CONNECT BY操作/ ORA-01788：此查询块中需要CONNECT BY子句

大家都在问