CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperplsqldeveloper
Rohit
Rohit

Reputation: 39

How to remove ORA-01562: Failed to extend rollback segment error while updating 100 million rows

A table is having 100 million records and I need to update a column by adding 10% into the salary of each employee. when I execute update statement I am getting this error:

ORA-01562: Failed to extend rollback segment

How can I update this column for the best performance result?

update employee
set salary = salary + (salary*10/100)

OR

declare  
i number(10);
limit number(10) := 100000;
begin
for i in 1 .. limit loop 
update employee 
set salary = salary + (salary*10/100) 
where rownum = i; 
limit := limit + 100000; 
end loop;
end;

Upvotes: 1

Views: 2469

Answers (4)

Avrajit Roy
Avrajit Roy

Reputation: 3303

We can use FORALL also to achieve what is required. Hope this below snippet helps.

DROP TABLE test_so1
/

CREATE TABLE TEST_SO1
  ( COL1 NUMBER, COL2 VARCHAR2(100)
  )
/

--Insert values
INSERT INTO TEST_SO1
SELECT LEVEL,'AVRAJIT'||LEVEL FROM DUAL CONNECT BY LEVEL < 10000
/

--FORALL UPDATE
DECLARE
type TEST_REC
IS
  RECORD
  (
    COL1 NUMBER,
    COL2 VARCHAR2(100),
    col3 VARCHAR2(100));
type TEST_TAB
IS
  TABLE OF TEST_REC;
  LV_TAB TEST_TAB;
  CURSOR LV_CUR
  IS
    SELECT col1,col2,rowid FROM TEST_SO1;
BEGIN
  OPEN LV_CUR;
  LOOP
    FETCH LV_CUR BULK COLLECT INTO LV_TAB LIMIT 1000;
    EXIT
  WHEN LV_TAB.COUNT=0;
    FORALL I IN LV_TAB.FIRST..LV_TAB.LAST
    UPDATE TEST_SO1 SET COL2 = 'shubhojit' WHERE ROWID = lv_tab(i).col3;
    COMMIT;
  END LOOP;
END;
/

Upvotes: 0

I3rutt
I3rutt

Reputation: 582

You can try this approach: link

Information about parallel: link

Upvotes: 0

Hasan Alizada
Hasan Alizada

Reputation: 591

Try this: 

DECLARE
   CURSOR CUR
   IS
      SELECT ROWID, A.*
        FROM YOUR_SALARY_TABLE A;

   TYPE CUR_TYPE IS TABLE OF CUR%ROWTYPE
      INDEX BY PLS_INTEGER;

   L_CUR   CUR_TYPE;

   LIM     NUMBER := 100000; -- Update chunk size
BEGIN
   OPEN CUR;

   LOOP
      FETCH CUR BULK COLLECT INTO L_CUR LIMIT LIM;

      FOR INDX IN 1 .. L_CUR.COUNT
      LOOP
         UPDATE YOUR_SALARY_TABLE S
            SET S.SALARY_COLUMN = S.SALARY_COLUMN * 2 -- Multiplying here
          WHERE ROWID = L_CUR (INDX).ROWID;
      END LOOP;

      COMMIT;

      EXIT WHEN L_CUR.COUNT < LIM;
   END LOOP;

   CLOSE CUR;
END;

Upvotes: 0

Lohit Gupta
Lohit Gupta

Reputation: 1081

Looks like you are using Oracle version 8i or prior, as the rollback segments have been replaced with undo segments from Oracle 9i onwards.

To solve the problem, I would suggest you to check the trace file to see which rollback segment is creating the problem, then create a bigger rollback segment depending upon the update transaction size.

Upvotes: 1

Related Questions