-
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
-
check your package if it has any commits.
-
Commit frequently, say after evrey 1000 odd rows.
-
[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 .
-
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
-
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.
-
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?
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|