snapshot too old ORA-01555
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: snapshot too old ORA-01555

  1. #1
    Join Date
    Feb 2001
    Posts
    4
    Hi,
    I have a problem , I got a package which inserts 15,00,000 row's in one table and 2500 row's in another table by getting detials from different cursors . This package is failing with ORA-01555 error . I asked the DBA to creat a big rollback segemt RBSBIG1 ( 1GB) . After that i modified the code to use that big rollback segment , but still it is failing with ora 01555 error by refering another rollback segment Like RBS01,RBS02 .. . I don't know though i coded to RBSBIG1 , it fails by showing anther Rollbacksegment which it is not suppose to use . Any body has ever faced this problem . Can some one help me in this , i tried to the max to eliminate this error . Please update this if you need any more info on this issue .

    Raghu

  2. #2
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    check your package if it has any commits.

  3. #3
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Commit frequently, say after evrey 1000 odd rows.

  4. #4
    Join Date
    Feb 2001
    Posts
    4
    [QUOTE][i]Originally posted by dondapati [/i]
    [B]Hi,
    I have a problem , I got a package which inserts 15,00,000 row's in one table and 2500 row's in another table by getting detials from different cursors . This package is failing with ORA-01555 error . I asked the DBA to creat a big rollback segemt RBSBIG1 ( 1GB) . After that i modified the code to use that big rollback segment , but still it is failing with ora 01555 error by refering another rollback segment Like RBS01,RBS02 .. . I don't know though i coded to RBSBIG1 , it fails by showing anther Rollbacksegment which it is not suppose to use . Any body has ever faced this problem . Can some one help me in this , i tried to the max to eliminate this error . Please update this if you need any more info on this issue .

    Raghu [/B][/QUOTE]

    In my package i have 16 procedure and in each procedure i am committing after each 1000 rows , In most of the cases it fails while inserting 2500 row's in the other table after completing 15,00,000 row's .

  5. #5
    Join Date
    Feb 2001
    Posts
    4
    My question is though i am asking the program to use a pecific RBSBIG1 why it is failing with ORA 1555 by showing another rollbacksement (not RBSBIG1) . As per oracle after a commit a new transaction starts , so i have put set transaction after each commit .

    Raghu

  6. #6
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    You might have used the bigger rollback segment initially, but once commit is given and after that if you don't specify which RBS to use, Oracle allocates RBS as per its discretion. So, once you said commit, that transaction is over and then a new transaction starts which is using a new RBS. That is why you are getting the error.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Your transactions (that is, inserts, deletes, updates) are using RBSBIG, but this does not mean that at the same time your package is not using other RB segments.

    If other sessions are changing data you are reading in your cursors, then the original data is stored in rolback segments those other sessions are using. For your select cursors to obtain read consistent view of those changed data blocks, they have to obtain them from the corresponding rollback segments. If those other sessions transactions are commited in the meantime, the original images of the changed blocks in the rollback segments can be overwritten by other transaction and your cursor will be unable to obtain read consistent view of the data and will return ORA-1555.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Feb 2001
    Posts
    389
    While running this package check the locks, check if the there TX (transaction) locks on rollback segs other than the big one.
    Since u are opening lot of cursors may be some of the cursors are taken as seperate transaction and use some other rollback segment.
    Also check the number of concurrent transactions and the corresponding number of rollback segments, increase the number of rollback segments.

    Also since u r using cursors , are u using fetches across commits.That may also cause ora 1555 error.
    What is the db_block_size.

  9. #9
    Join Date
    Feb 2001
    Posts
    40
    Hi raghu,
    Lakshmi srinivasan is correct. So when u give commit Oracle finds another rollback segment and starts writing to that. So it is not enough if u have a single rollback segment bigger. If u have space u can have two rollback segments of the same size. Or else u should specify to which rollback segment oracle has to write the next transaction.

    Karthi
    karthi,
    MDC Systems inc
    Farmington Hills,MI

  10. #10
    Join Date
    Jul 2000
    Posts
    243
    why not lock the table? when you do that no one can insert/update data.

    when you use cursor the system takes all your 1500000 row and starts useing them. if during the time someone updates r deletes lines, you arein the too old ... area.

Posting Permissions

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



Click Here to Expand Forum to Full Width