过程: 10000条记录后提交

Question

CREATE OR REPLACE PROCEDURE testing AS
BEGIN

    insert into t3 select * from t2;

    insert into t1 select * from t4;

commit;
EXCEPTION WHEN OTHER THEN
     ROLLBACK;
END;

这将工作得很好。

t2 -300万t4 -300万总共我有600万条记录，由于某种原因，我的临时空间被填满了，所以我想要的是在每插入10000条记录后提交。

怎么办？

Answer 1

我会使用BULK COLLECT和FORALL。

参见Incremental Commit Processing with FORALL和Bulk Processing with BULK COLLECT and FORALL以获取一些可根据您的情况进行调整的示例。

Answer 2

希望这段代码能帮上忙。

--Test data creation
CREATE TABLE TEST_SO_BULK
AS
SELECT LEVEL COL1,'AVRAJIT'||LEVEL COL2 FROM DUAL
CONNECT BY LEVEL < 100000;


--Create another table to insert

CREATE TABLE TEST_SO1
AS
SELECT * FROM TEST_SO_BULK
WHERE 1=2;


--Bulk collect with limit clause
set serveroutput on;
DECLARE
TYPE lv
IS
  TABLE OF TEST_SO_BULK%ROWTYPE;
  lv_tab lv;
  CURSOR lvsql IS
  SELECT * FROM TEST_SO_BULK;
BEGIN
  OPEN lvsql;
  LOOP
  FETCH lvsql BULK COLLECT INTO lv_tab LIMIT 10000;
  dbms_output.put_line(lv_tab.COUNT);
  FORALL i IN lv_tab.FIRST..lv_tab.LAST
  INSERT INTO TEST_SO1 VALUES
  (
  lv_tab(i).col1,
  lv_tab(i).col2
  );
  EXIT WHEN lvsql%NOTFOUND;
  END LOOP; 
END;

--Check data count
select count(1) from test_so1;