rollback/commit question

From: Jay Scheiner <jxs_at_walaw.com>
Date: Wed, 02 Feb 2000 00:38:58 GMT
Message-ID: <38977b43.1272342793@news.erols.com>

This is from the perspective of a farily new Oracle user, doing learn as you go.
I have created a database that we will be using for data warehousing. I have successfully import about 6 weeks worth of data into a single table (about 75-100K records/day). I then created a new column and wanted to populate that column based on a current column.

I have a PL/sql procedure something like (cursor defined)
loop
  get cursor rec
  update cursor.column = value
  cnt:=cnt+1;
  if cnt > 500 then

      commit;
      cnt :=0;

  end if
end loop
commit;

I tried running it for the whole table, go a rollback seg size error. So now I am running it for 1 days records at a time, and sometimes it goes through, sometimes I get

snapshot too old, rollback seq # .... w/name RB22 too small

I know that I will need to expand rollbacks, set up tablespaces, etc. BUT after all this, my question is why doesn't this commit every 500 updates keep the rollback from getting too large?

Thanks in advance. Received on Tue Feb 01 2000 - 18:38:58 CST