Results 1 to 5 of 5

Thread: Rollback

  1. #1
    Join Date
    Sep 2004
    Posts
    0

    Unanswered: Rollback

    I am updating a large number of records in a 7.3 db. I keep getting rollback errors (out of space) and the amount of records that I process seem to shrink everytime I get a new "rollback" message. Is there a way to clear the rollback buffer to utilize all the roolback space or is there a way to increase the rollback segment while online?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alter Tablespace Rbs Add Datafile ........
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would you be satisfied with this solution: divide your transaction into smaller ones; let's say, you have to update million records. Use a cursor loop and update thousand records in a loop and commit your updates. Loop thousand times (actually, until you run out of available records) and that'll do it.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Use a cursor loop and update thousand records in a loop and commit your updates
    This is a recipe for incurring the dreaded ORA-01555 Snapshot too old error.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    > This is a recipe for incurring the dreaded ORA-01555 Snapshot too old error.

    True, but if this is a one-off update and no further resources are available (they haven't upgraded their Oracle version in almost 10 years, so why should they allocate sufficient disk space?) you might get away with it. Perhaps start with updating 100,000 at a time and only reduce it if that fails.

    ...Unless of course "rollback errors (out of space)" actually refers to ORA-01555.

    Another way of carrying out a big UPDATE is to INSERT the required rows into a new table, then either rename the tables or TRUNCATE the original and INSERT back into it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •