Results 1 to 2 of 2

Thread: ORA-01555:snapshot too old: rollback segment number 2 with name 'R02' too small

  1. #1
    Join Date
    Jan 2003
    Location
    Mumbai - India
    Posts
    1

    Question ORA-01555:snapshot too old: rollback segment number 2 with name 'R02' too small

    I gets an error when I tried to execute the package.The error is
    "ORA-01555: snapshot too old: rollback segment number 2 with name 'R02' too small".
    I think there may be 3 solutions for this.
    1. To tune the query well.
    2. To increase the rollback segments.
    3. To reshdule the jobs.

    As I feel, The 2nd and 3rd options are temporary fixes and we have tried with those options and strll we are getting this error.
    So I think It's better to look into the option 1.

    Before looking into the package,Please suggest me whether the solutions that I had assumed is correct.
    The package contains an PL/SQL procedure which has a select query by accessing different tables with joins and I think it may time longer time to get the reults.

    Please let me know that tuning the query will solve my problem.If not what has to be done.What may be other solution for this.

    Thanks and Regards,
    V.Praveen.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    There are at least two processes involved to get this error.

    Process 1 - may be a large transaction using all spaces in rollback segment.

    Process 2 - a slow running transaction which starts after process 1 started. When it needs to read the rollback segment to get data modified by process 1, it is already overwritten because process 1 has already committed.

    Create a big rollback segment for batch job and use 'set transaction use rollback segment' command before running process 1 to point it to a bigger rollback segment.

Posting Permissions

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