Forum Stats

  • 3,824,844 Users
  • 2,260,430 Discussions
  • 7,896,328 Comments

Discussions

Change the ORA-1555 message

Franck Pachot
Franck Pachot Member Posts: 912 Bronze Trophy
edited Jan 11, 2016 6:19PM in Database Ideas - Ideas

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.

Franck PachotNiall LitchfieldKim Berg Hansenuser7328662ctriebtop.gunberxBeGinBPeaslandDBAWilliam RobertsonPravin TakpireLothar FlatzZlatko SiroticMartin PreissMartin Lutzuser12218407sysassysdbaChris Huntmbe7Sven W.mtefftApexBineMelody S-Oracle1483608N.B.user7048955Hemant K ChitaleUser_F2Y1CGregVDaniel HillingerAndreas Huber1244776Gerald Venzl-OraclegassenmjMr. CEmad Al-Mousauser3623132opentuningsMohammed Sulaimanfloo_barFamilyGuy81User_9GEWMuser10285Billy VerreynneUser_S1J6PCherif bhuser11970842Jose Carlos Pavón
49 votes

Active · Last Updated

Comments

  • Pravin Takpire
    Pravin Takpire Member Posts: 1,762 Gold Trophy

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

    regards

    Pravin

    Franck Pachot
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

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

    Franck PachotApexBineGbenga Ajakaye
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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.

    Franck Pachot
  • Martin Lutz
    Martin Lutz Member Posts: 28

    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"

    Franck PachotCherif bh
  • GKris
    GKris Member Posts: 11

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

    Thanks

    Phani G Kris

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy

    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.

    berx
  • mtefft
    mtefft Member Posts: 844 Gold Badge

    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.

    Jose Carlos Pavón