ORA-1555: snapshot too old: rollback segment number

Question

Any idea about ORA-1555: snapshot too old: rollback segment number I am getting this error and nothing seems to be wrong. Please state under what conditions in may occur and how it can be avoided?

Answer 1

I suggest you read Tom's answer : http://asktom.oracle.com/pls/asktom/f?p=100:11%3A0%3A%3A%3A%3AP11%5FQUESTION%5FID:1441804355350

"The ORA-1555 happens when people try to save space typically. They'll have small rollback segments that could grow if they needed (and will shrink using OPTIMAL). So, they'll start with say 10 or so 1meg rollback segments. These rollback segments COULD grow to 100meg each if we let them (in this example) however, they will NEVER grow unless you get a big transaction.
"

Answer 2

Typically this occurs when code commits inside a cursor.

eg.

for x in (select ... from ...)
loop
   do something
   commit;
end loop;

See the AskTom link form guigui42 though for other examples.

Answer 3

Frequent commits can be the cause of ORA-1555. It's all about read consistency. The time you start a query oracle records a before image. So the result of your query is not altered by DML that takes place in the mean time (your big transaction). The before image uses the rollback segments to get the values of data that is changed after the before image is taken. By committing in your big transaction you tell oracle the rollback data of that transaction can be overwritten. If your query need data from the rollback segments that is overwritten you get this error. The less you commit the less chance you have that the rollback data you need is overwritten.

One common cause of ORA-1555 is a procedure that does this all in itself : a cursor on a table, loop through the records, and updates/delete the same table and commits every x records.

As guigui told : let the rollback segments grow to contain your whole transaction