Real Time MQ job Error: ORA-01555: snapshot too old

[Kirtikumar]

Hi,

We have a RT job that is using MQ. It parses the XML message and it then runs few select queries on few tables. All queries here are sparse.

All these tables are small except one. One of the table holds information that gets update in real time mode.

So far things were running fine - except now we have started getting error as:

Code: Select all

lkp_location,0: The OCI function OCIStmtFetch returned status -1. Error code: 12,801, Error message: ORA-12801: error signaled in parallel query server P075
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small. (CC_OraStatement::fetch, file CC_OraStatement.cpp, line 1,390)

We have increased the UNDO space. But the question here is - if we are just reading the table as "Read commited", why we should be getting this error.

Is there any alternative to this?

[Kirtikumar]

Just to add - I have checked all the posts and googles re this and the only solution is increase the UNDO space.

But if I am just doing read from the table, why do I have to worry about the UNDO space.

[chulett]

Have a chat with your DBA. It needs to maintain a 'snapshot' of the data's initial values over the period of time your select happens and it uses UNDO space for that. Other processing on the system is eating it up and thus it cannot maintain your snapshot.

So as you noted, one solution is to add more UNDO space especially if it is 'under configured'. Another is to run your process at a time when there is less activity on the database.