ORA-01555: What can do with that?

[AntonUsanov]

Hello list!
I perform copy PY7333 path code to PD7333 and have got error for two tables:
OCI0000056 - Unable to fetch - SELECT * FROM PY7333.F98761

1156/1396 Wed Jul 30 21:41:34 2003 DBFETCH.C172
OCI0000057 - Error - ORA-01555: snapshot too old: rollback segment number 3 with name "RBS3" too small
The same error's for F98741.
RBS3 has got following parameters (object DDL for RBS3):
CREATE ROLLBACK SEGMENT "RBS3"
TABLESPACE "ROLLBACK_DATA"
STORAGE ( INITIAL 984K NEXT 984K MINEXTENTS 2 MAXEXTENTS 32765)
ALTER ROLLBACK SEGMENT "RBS3" ONLINE

Other RBSs (1-4) have got the same parameters.
What should I do for prevent that error?

 

[AntonUsanov]

How can I know how large roll back segment I need?

 

[vivek_kaushal]

It seams to me that rollback segment is small for the type of transaction you are performing. One workaround is to perform intermediate commits. In Oracle, you can create rollback segments of different sizes ( Big and smalls) depending upon the transaction size. In sql(Oracle) you can also specify which rollback segment to choose by using the "Set transaction" statement. I do not know how to achieve it in JDEdwards. Even if you can create a big rollback segment(segment size* maximum number of extend) then Oracle will choose the rollback segment itself untill you specify the rollback segment.

I hope this is helpful

 

[Alex_Pastuhov]

It doesn't really mean that your RBS is too small. More RBS's can fix it, but the easiest way to avoid it is to run the job during the night when there's nobody in the system (and no jobs are running on the server).

Regards,
Alex.

 

[jean_driscoll]

Are you copying via the R98403 program or just doing an SQL? It might be that those files have binary data in them. Try using the JDE routines and it they don't work, call JDE.

 

[vivek_kaushal]

Hi Alex,
There are no of reasons for ORA-01555 error. 2 main reasons are

1. Rollback segment is small. A transaction is only assigned one rollback segment. So more rollback segment will not fix it BUT bigger rollback segment will fix it.

2. In Oracle when a transaction starts, it ensures that data is consistent from the time when the transaction starts( Let us call it a big transaction). If there are other people who modifys the data after big transaction start time then big transaction will try to find the data from its start time and it will give the error ORA-01555. It can be avoided by running it at night when there is no other transaction modifying the data.

So one should look at the error message, find the root cause and then take action depending upon the root cause.

Hope this is helpful

 

[Alex_Pastuhov]

Yes, what I was saying is that it could be too small, but more likely there's just not enough of them.

This is from Oracle:

1. A few things to keep in mind. The ORA-1555 occurs because we cannot recreate a read consistent picture of the data. This usually is because another (or the same) transaction is making changes and is doing a commit. If the process doesn't commit, we will never overwrite the data in the rollback segment. In other words, we don't extend a rollback segment because someone is selecting the data, we only extend because a transaction has not committed.

The solution to this error is to have larger rollback segments. Doing this is with the 'hope' that you don't overwrite the data. I.e., it is 'less likely' the data will be overwritten with larger rollback segments. If you have optimal set on the rollback segments, the shrink can get rid of the needed information.

2. Solution Description:
The snapshot is too old because there aren't enough Rollback segments. This issue is resolved by making a smaller set of the concurrent request and smaller number of rollback segments with potentially small sizes in a very active database.

Which one is causing it here cannot be ascertained from the information provided, but I would be inclined to assess the likelyhood of this being caused by an inadequate number of RBS's as high...

Regards,
Alex.