we have around 35 rollback segments with 10 mb each & 1 rollback segment with 250mb. but still in some reports we are getting error ora-2555.

While running session tfgld3203m000 gives this error refers table tfgld106 contains 1 crore records.

required solution on urgent basis.

Swapnil

Swapnil,

Do not cross post. Putting your message/question in one forum should do the trick.

Please use the Search engine to look for known solutions before asking your question on the board.

Refer to the links for more info -

error 2555 (very urgent) (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=5943&highlight=2555)

rollback segment size (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7622&highlight=2555)

Rollback in Oracle (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=9490&highlight=2555)

Error Meesage when running PST (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=354&highlight=2555)

dear Rao


gone through all the cases & tried out also. search in knowledge base also. still problem not solved.


Swapnil

There are typically three areas that can cause this to happen:
1) Too few and/or too small rollback segments
2) Rows fetched from the cursor across commits
3) Delayed block cleanout

Chances are that one or both of the first two may apply here. Table TFGLD106 tends to be very big. We have some very large rollback segments sized at 1 Gig each that we have had to use in the past to be the only ones available when running some finance batch jobs. We have also gone to Baan support over some of these sessions because of reason 2, that being the way they were coded. Check with Baan to see if there might be a newer version of the session you're having issues with. I can't tell you what the best sizing would be because every system is different. I have encountered an excellent book that will give you some very clear methods to determine the best size for your rollback segments. It is Oracle Performance Tuning 101 by Gaja Vaidyantha and is published through Oracle Press.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

If the error snapshot too old continues.

It´s posible execute the sessions without the rollback of ORACLE, if you customize the session with a little change in the sql sentence, the session will use a temporary file instead the rollback segment.

Could you please post this change to the SQL sentence?

If you write at the end of the select sentence "as prepared set", the execution of the select will generate a temporary file. Be careful because may be a big big file.

But... this has nothing to do with rollback! Quoted from the library:

Prepared set.

With this option, the entire set is retrieved before the first record is returned. The set is temporarily stored. This option is useful when a process simultaneously selects and maintains (or deletes or adds) records. In this case, changes must not be visible in the selected records. The prepared set option forces a consistent read. The syntax is as follows:
SELECT ... [from][where] AS PREPARED SET

Also, remembering from the time we where rewriting the Euro conversion software: using prepared set was only the first step in overcoming the rollback problems that occurred when converting the tfgld106 and tfgld41x tables. The second, and most crucial, step was to have a commit.transaction() every x number of lines, where we made the x a parameter in the tccri000.cctr field! So, if there is only a single commit.transaction() at the end of the query, the prepared set does not help at all!

That all depends on the query. If the query that is getting an error 2555 is the one that you use "As prepaired set with" then it would be impossible for it to get a 2555 with...

That's be cause "as prepaired set" causes a qp file to be written to the os, which is then used to process the info...

So you are no longer using the rollback segments in Oracle with that query..

Of course, if you use this on a large table or a large join, your sesssion will take 200 hours and your hard drive might fill up..

Best to use a timer in Baan 5 and up, set the timer and have it break the transaction ever X hours (like 1/2).

Dave

One correction. As prepared set does not completely bypass the rollback! As prepared set is only used to read a consistent set of data, but it does no updates! And the rollback is only used during updates and inserts, not while reading! Thus, if you do not split a transaction in manageable chunks, the error WILL reappear, even when using "as prepared set"!

"As prepared set" creates a text file as the result set of your query.

2555 happens because

<long query>
<update> <commit>
<update> <commit>
<oops i trampled over my rollback segment that i was using for read consistancy>

Now
<long query with "As Prepared Set">
<read from text file>
<update> <commit>
<update> <commit>
No problems with rollback in this instance..

Lots of commits won't help at all.

I read up on the rollback in the Oracle documentation again. I made one mistake, namely that a consistent read does use rollback space. Sorry about that! :(

Looking at the first example Dave gives in the previous post, and comparing this with the Oracle documentation, the rollback will already fill up during the initial read, and the processing of the records is never started, meaning the first <update><commit> is never reached!

As for the number of commits, here I still have to disagree. Looking at the Oracle side of the coin (using a trace), the "as prepared set" causes the actual start of the transaction to be moved to the inside of the "selectdo" loop, therefore creating smaller transactions. At each commit, the rollback buffer is cleared, and ready for re-use. If you want to test it, try executing the following code fragment. The first query will give an error 2555, the second not! (Obviously depending on the number of records in the tfgld410 table ;) )
long counter

| This will generate error 2555
db.retry.point()
select tfgld410.*
from tfgld410 for update
as prepared set
selectdo
db.update(ttfgld410, db.retry)
endselect
commit.transaction()

| This will not generate error 2555
counter = 0
db.retry.point()
select tfgld410.*
from tfgld410 for update
as prepared set
selectdo
db.update(ttfgld410, db.retry)
counter = counter + 1
if counter > 10000 then
commit.transaction()
counter = 0
endif
endselect
commit.transaction()
Looking at the second example Dave gives in the previous post, he is right in stating that it does not generate errors, since he has more than one commit! But the statement that "Lots of commits won't help at all" is incorrect!

Hi Nico,

I'm still a little confused.

Based on the following statement in the Baan documentation for "As Prepared set" :

--
With this option, the entire set is retrieved before the first record is returned.
--

My understanding is that it stores that entire set in a file called qp<something>. and then uses that file for read consistancy..

The outer query should be finished at this time!

So assuming that the only process on my system is this query,
I shouldn't really run into rollback segment issues regardless of how much I commit...

Oops! :o You're right!

What I meant to write was, if there was no "as prepared set", the query would already fail during the initial read!

Oh well, nobody's perfect... but I've been making a little too many mistakes in this thread :o, so I'm bailing out of this discussion!

:) I hate when i do that! at least i'm not going crazy like I though!!

Dave