Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    Red face Unanswered: snapshot too old: rollback segment number 3 with name "RBS2" too

    I faced one more problem in which Oracle Database give message after processing a short
    "snapshot too old: rollback segment number 3 with name "RBS2" too
    small"
    That process Insert Data into One Big Table (29.50 GB Size), its run properly before. My RBS Size is 5.5 GB and during process it insert 80-90 thousand record more.

    That can i do, Please reply me as soon as possible.

    Thanx

  2. #2
    Join Date
    Nov 2004
    Location
    Bulgaria
    Posts
    2
    Many possible solutions:
    - load the data using sqlldr (the best for loading large amounts of data
    - insert the data on several chunks that can fit into the rollback segment
    - increase the rollback segment (stupid solution IMHO)

    p.s. "Please reply me as soon as possible." -> I don't think it's a good idea to write things like this, personally it almost stopped me from answering you.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Obviously you did NOT follow the forum guidelines listed below.

    Before you post:
    * Try using the Search button to see if a similar question has already been answered
    * Try reading the relevant parts of the Oracle documentation (see links below)
    * Try performing a Google search

    Problems WRT or ORA-01555 are posted almost weekly
    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.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You get this error when Oracle cannot recreate the set of data your query needs as it existed at the start of the query. The size of the transaction or the target table is not the issue.

    For example, you start a query running that takes 10 minutes. During that time, a user in another session updates some of the rows used by your query. No problem - Oracle can figure out what the previous values were by looking in the rollback blocks, and continues with your query. Then some more rows are updated, the first user commits, someone deletes some others etc etc - still no problem in principle, but eventually there is a point where Oracle goes to look in a rollback block and finds that it has been reused by another session (they are a shared resource after all) and the old values have been overwritten.

    Bearing this in mind, together with the fact that whenever you start a new transaction (i.e. issue the first insert/update/delete/merge statement following a login/commit/rollback) the database assigns you a different rollback segment, it will be clear that what you don't want are large slow batch processes committing every thousand rows, especially if several of them are run at the same time.

    Of course, depressingly, this is the exact system that many developers set out to build. Then they get this error with "rollback" in it so they think they must be running out of something and determine to commit more often and in parallel (as described on several AskTom threads).

    Could any of the above be true of the system where your problem is occurring?
    Last edited by WilliamR; 11-25-04 at 09:48.

Posting Permissions

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