0
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;

This will work fine

t2 - 3 millions t4 - 3 millions total i have 6 million record , due some reason my temp space gets filled so what i want is to commit after every 10000 record is inserted .

how to do?

APC
  • 144,005
  • 19
  • 170
  • 281
Aman
  • 226
  • 1
  • 4
  • 17
  • The correct solution is to ask your DBA to increase the TEMP tablespace. Any other approach risks data corruption and related integrity issues. Of course a lot of people take the risk because they don't think those things matter. – APC Aug 18 '17 at 06:24

2 Answers2

0

I would use BULK COLLECT and FORALL.

See Incremental Commit Processing with FORALL and Bulk Processing with BULK COLLECT and FORALL for some examples you can adapt tp your case.

gile
  • 5,580
  • 1
  • 25
  • 31
0

Hope this snippet helps.

--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;
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25
  • 1
    Don't forget to close the cursor. Plus, `EXIT WHEN lv_tab.count = 0`, else you will end up not processing all of the rows your retrieve when there are not an even number of rows. See the "kicking the %NOTFOUND Habit" section here: http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html – Gary_W Aug 18 '17 at 15:32