Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Bangalore, India
    Posts
    5

    Unanswered: Error - Ora-01555

    Hi all,

    I have enqueued a million messages into an AQ. The enqueuing process ran smoothly without any hiccups. Then I have tried to dequeue those one million messages from the queue. The following error occured while dequeuing:

    At 105978 th record.

    ORA-01555: snapshot too old: rollback segment number 1 with name "RBS0" too
    small
    ORA-06512: at "SYS.DBMS_AQ", line 189
    ORA-06512: at line 46

    What exactly is the problem?
    Where can I find the rollback segment name "RBS0"?
    How can I increase the size of rollback segment or how can use a larger rollback segment?

    All the suggestions are welcome.

    Txs and Regds,
    Santha Ram.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Error - Ora-01555

    Originally posted by Santha Ram
    Hi all,

    I have enqueued a million messages into an AQ. The enqueuing process ran smoothly without any hiccups. Then I have tried to dequeue those one million messages from the queue. The following error occured while dequeuing:

    At 105978 th record.

    ORA-01555: snapshot too old: rollback segment number 1 with name "RBS0" too
    small
    ORA-06512: at "SYS.DBMS_AQ", line 189
    ORA-06512: at line 46

    What exactly is the problem?
    Where can I find the rollback segment name "RBS0"?
    How can I increase the size of rollback segment or how can use a larger rollback segment?

    All the suggestions are welcome.

    Txs and Regds,
    Santha Ram.

    go to
    http://asktom.oracle.com
    and then search on
    ORA-1555

    FWIW - Typically the solution does not involve making the rollback segment larger.

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    in common, the message refers to the situation that rollback informatie about changed data can no longer be stored in a rollback segment.

    For every transaction a rollback segment is assigned to you. Every rollback segment has it's own size and abilities to expand. Now, because oracle has the consistent view on the db, data that is changed but not yet committed is not to be seen by other users. What oracle does, is copy the data being changed (old values) abd store that in a rollback segment. Every user selecting the rows being changed, gets that info from the rollback segment. Changing a lot of data requieres a lot of rollback space. Dequeuing is reading and updating the queue table, so records are changed and the old values are stored in the rollback segment. As said, the rollback segment has it's own abilities to expand, and is most of the times limited, since it might grow very large. So, when u run out of space, there is no room to store any more old values, so the consistent view is no longer guaranteed, so an error occurs.
    Commit every now and then in a long running transaction, prevents this problem (space is released). U can also enlarge the rollback segment (but that is not a good option) or assign a special very large rollback segment to your transaction.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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