Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Question Unanswered: Help with Rollback Segment!

    Hello,
    I'm trying to execute a vendor supported program that purges a table. There's alot of data being deleted and I recieve the following error - ORA-01555. I have figured out a way to go around this but it seems to be not working.
    When executed it picks up whatever rollback is available
    Any suggestions on how I can do this.
    I cannot modify the vendor code unless my company spends $$.
    This is the command I executed.
    SQL>connect user/userpassword
    SQL> set transaction use rollback segment rbig
    SQL> !trimrun AHN010150 $DB_LOGIN -p 1050

    thanks,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    goto http://asktom.oracle.com
    and do a keyword search on ORA-01555
    Read, learn, apply
    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
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you use the host command to start an executable that logs onto oracle, it will have a different session number and your alter will not effect it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > set transaction use rollback segment rbig
    At best this is a bandaid which may or may not eliminate the ORA-01555 errors.
    1) The very first COMMIT issued after the above command is issued, means the session could start using another RBS.
    2) There is NOTHING to prvent other transaction(s) from also using the "RBIG" space.
    Unless the session reporting the ORA-01555 error is doing a COMMIT inside of a long running SELECT loop, it is typically the victim & not the true cause of the 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
    Jul 2003
    Posts
    2,296
    i suggest committing somewhere within your loop.
    possibly every few thousand records or so.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i suggest committing somewhere within your loop.
    >possibly every few thousand records or so.
    NO! NO! NO!
    COMMIT inside the loop causes ORA-01555.
    It makes a bad situation worse.
    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.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by anacedent
    >i suggest committing somewhere within your loop.
    >possibly every few thousand records or so.
    NO! NO! NO!
    COMMIT inside the loop causes ORA-01555.
    It makes a bad situation worse.
    what about when you process in groups?
    that is what I meant.

    loop through groups
    loop updates
    end loop
    commit
    end loop through groups

    ??
    Last edited by The_Duck; 08-24-04 at 15:06.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Mar 2004
    Posts
    10
    Thanks, for the replies.
    I can't modify the sql, because a 3rd party compeny owns the code. If they are to change it, it will cost the compeny I work for $$.
    I am trying to figure out another method of doing this.
    I saw one method which is to create dummy transactions, that way you force the big sql program to take the larger rollback segment.
    I'm not sure if our DBAs will go that route.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I can't modify the sql, because a 3rd party compeny owns the code
    AFAIK, you have only two viable alternatives.
    1) make the the RBS larger
    2) Eliminate/reduce other INSERT/UPDATE/DELETE activity while this runs.
    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.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    One trick that I have used is the following:

    If the long running process is a batch process that is run at night by it's self, you can on-line your rbig rollback segment and disable all the others before you start the application. This will force the application to use the big rollback segment. When the application is done, re-enable all the rollback segments and your database will go back to normal usage.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Sep 2003
    Posts
    237
    Perhaps you could try this. Create a large numer of rollback segments. If you have 50 segments, and transactions per rollback segment is 5, you have 250 chunks which need to be written before the first one is overwritten. ie you have room for 250 transactions. When a large transaction starts, to have consistent reads, any transaction starting after it, cannot have rollback space overwritten before the large transaction ends. The thing which loses chunks is setting OPTIMAL value for rollcack segments. SO DO NOT SET OPTIMAL value or set it to a very high value. Then you can avoid losing chunks. HTH
    mota

Posting Permissions

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