Change the ORA-1555 message

Franck Pachot

A very simple idea: please change the message for ORA-1555: snapshot too old: rollback segment number ... with name "..." too small

How many times a user receiving this message just blame the DBA for putting too small rollback segments!

Message should be: query duration beyond undo retention, can't get consistent view.

mtefft

Even better - add the name of the table or index whose block could not be reconstructed!

Pravin Takpire

really, it is required. DBA's only can understand pain.

regards

Pravin

Lothar Flatz

I like the old message for no good reason, just nostalgia. In spite of that I think you are right.

William Robertson

The reference to a named rollback segment assumes manual undo management which has been deprecated since 9i so the message is confusing from the start, never mind that it misleadingly gives developers the impression that they should save undo space with smaller transactions.

Martin Lutz

I'm voting for a change, too. But do you think the suggested new message will keep users from complaining or prevent user-misunderstandings?

Maybe we can collect some more suggestions, similar to the one you provided, and try to improve the message (not requesting this to be the best one):

"Query exceeds consistency limit"

GKris

May be good time to re-visit all such Messages to suit today's usage.

Thanks

Phani G Kris

Gbenga Ajakaye

Even better - add the name of the table or index whose block could not be reconstructed!

This is a great addition and I think would make our job easier.

mtefft

Even better - add the name of the table or index whose block could not be reconstructed!

I have had a bunch of ORA-1555 issues to investigate recently so I wanted to bubble this up again.

Please modify the message to include the name of the table or index whose block could not be reconstructed.

This information is key to any possible analysis or correction. Without it, we have to rely on guesses.

My current issue has 61 steps in the execution plan, using 22 tables. It is a batch process that failed after 4 hours. I try to find possible 'interfering' SQL in a reasonable timeframe in ASH, that might have hit one of the tables used in this query. No smoking gun.

If I knew which of the 22 tables had a block that could not be reconstructed, I can focus my investigation and have a much stronger argument for prioritizing changes needed to prevent recurrence.