Updating table containing millions of records

Hi,

I tried to update a table with 34 million records in single update,
but it has thrown snapshot too old error.

Then I tried it with following procedure to make commit frequently
so that I will not get snapshot error,

Quote:

---

DECLARE
CURSOR c1 IS SELECT ROWID FROM VEHICLE;
BEGIN
--DBMS_OUTPUT.PUT_LINE(P_DUMMY_OUTPUT);
FOR i IN c1 LOOP
UPDATE /*+ parallel 8 */VEHICLE v SET OPTION_CD=(SELECT OPTION_CD FROM TRY_OPTION_CD t1
WHERE v.YEAR_ID=t1.YEAR_ID) WHERE v.ROWID=i.ROWID;
COMMIT;
END LOOP;
END;

---

but this procedure also gave the same error.

Why it is giving this error eventhough I'm commiting frequently?
what I've to modify in above procedure to run this update successfully?

Thanks..

Quote:

---

Why it is giving this error eventhough I'm commiting frequently?

---

Because you HAVE the old snapshot - the cursor c1 that selects from table being updated.

Quote:

---

what I've to modify in above procedure to run this update successfully?

---

Code:

---

loop
  update ... where ... and rownum<=10000;
  exit when sql%rowcount<10000;
  commit;
end loop;
commit;

---

In the where clause you must put the condition to restrict the update to not yet updated rows.

or configure your undo well enough and do it in one statement again

If this is a one-time update you could do a create table as select nologging with the new format then swap the tables at the end.