Oracle Snapshot Too Old Intricacies

If you think you can fix oracle error “ORA-01555: snapshot too old” just by increasing the rollback segments, think again as reading this article may potentially alter your opinion.

The article explains the inner details about this oracle error, indicates the possible course of action and provides details on how to fix or avoid this error. It also explains with an example scenario that the best solution for ORA-01555 error is tuning application logic rather than adding more rollback segments.

ORA-01555: snapshot too old: rollback segment number string with name “string” too small.

Cause: rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments Details: There are number of reasons for the ORA-01555 error. The most common reason is that the rollback segments are too small. Rollback segments are used by the Oracle for reconstructing the read-consistent snapshot of the data. In Oracle during the DML operations for any changes to the data records, a snapshot of the record before the changes were made is copied to a rollback segment.

After transaction completes, its data is not deleted from the rollback segment. It remains there to service the queries and transactions that began executing before it was committed. This may cause problems with long queries because these blocks may be overwritten by other transactions, even though the separate long-running query against those blocks has not completed. So ORA-01555 error may occur whenever a long-running query is executed at the same time as data manipulation transactions.

How to avoid/fix this error :

o Augment the size of all the roll back segments.

o Add more rollback segments.

o Augment the ‘OPTIMAL’ size of the roll back segments.

o Tune the application to “commit” more frequently such that smaller rollback space is used and this condition can be avoided.

o Follow the Oracle recommended ‘proper’ solution: schedule long-running queries at times when online transaction processing is at a minimum.


ORA-01555 Error Scenario: Sample application logic causing ORA-01555 error:

  • Prepare a select
  • fetchrowArray
  • while the row is not null
  • update the row
  • if nrows mod commitsize then commit
  • fetch the next row
  • end while
  • final commit

This application logic was implemented using Perl, DBD, DBI and oracle. It raised the error as shown below:

DBD::Oracle::st fetchrow_array failed: ORA-01555: snapshot too old: rollback segment number 15 with name “_SYSSMU15$” too small (DBD ERROR: OCIStmtFetch) [for Statement ..]”

Analysis and Resolution:

As you can see from the application logic, the rows selected by the outer select query are being modified by the update statement. ie both are acting on the same table . It can be resolved by setting UNDO_RETENTION appropriately large enough BUT it may not be the best solution.The better solution may be separating out select and updates in to different transactions. After rewriting the application logic by separating data selecti and update activities, the user didn’t experience ORA-01555: snapshot too old error any longer.

How to Take Photographs Instead of Snapshots

What is the difference between a snapshot and a photograph? The thought process taken before the shutter button is even pressed.

Snapshots are taken with no thought to composition, lighting, subject matter, and no thought to intentions of what to do with the images once they have been taken.

These are some tried and true ways to being turning your snapshots into photographs:

  • Clearly identify your subject
  • Is it the birthday decorations, the birthday presents, or the birthday child
  • Understand your subject
  • Balloons move, presents can have sparkly and reflective ribbons, children move around
  • Have an idea on how you will set up your composition
  • Where do you want the child to be in relation to the cake when blowing out candles
  • Realize your lighting conditions and how they will affect your exposure
  • Do need to set custom WB? Can you dim the lights for birthday candles? Will you need a tripod if the lights are dimmed? Do you want depth of field? Do you want to show motion?
  • Edit in person – Remove unwanted items out of the foreground and background; don’t plan to remove or fix something with editing software
  • It takes 2 seconds to move a soda can, ask someone to step an inch to the left or move yourself into the position you need to be in. It can take hours to edit a collection of photographs from a single birthday party.
  • Keep an eye on your background
  • Keep backgrounds simple and clutter free so they do not compete with your subject. Be aware of items which may “grow out of heads” or objects which could cause harsh shadows.

With just a few seconds of careful consideration, you can turn your album full of snapshots into coffee table books full of photographs.

Visualization Technique – The Snapshot Technique

When visualizing a desire in your mind, there are among others 2 techniques that I rely on most to get the best results. The first is the action sequence visualization where you visualize yourself in an ongoing movie having and achieving your goals. The second is the snapshot technique which is where you see a series of pictures in your mind, as if you were flicking through a one page photo album. The snapshot technique is a very simple but effective visualization technique because it requires only 30 seconds of your time and only a small amount of focus because with each snapshot comes a change in focus and as your subconscious mind only understands pictures and feelings, this technique is as good as any other.

First of all you need a goal with which to focus on so if you haven’t already, get a sheet of paper and write down your desires. Remember to be honest with yourself about what you want. If you need more money to pay your bills then visualize precisely that, or if you wish to get a promotion at work then focus only on that. Wanting to be a millionaire just for the sake of being rich will be extremely difficult for you to become emotionally connected to because you have no goal, no purpose for it. Therefore, because of the lack of connection to that desire it won’t be long before you give up on it. So look at your life and ask yourself this question, “What do I need to make my life better than it currently is?” Or this question, “What do I need to do in order to live a better life?” List your answers on a piece of paper or notebook.

Next, take one of your goals and concentrate on it. Close your eyes and begin to feel the feeling of having it right now. Make that feeling real. At first it will be difficult to fake it but nevertheless, keep going until the feeling starts to feel real. Then flash images of you having already achieved your goal through your mind one by one as if you are flicking through a photo album. When each picture is suspended there in your mind’s eye, feel the surge of emotions like joy, achievement and happiness, then flick to the next picture which should be closely related to the first. These pictures could be a sequence of pictures leading up to the completion of a goal or they can be pictures showing you living with having completed your goal.

Always remember, to feel the emotions that will set your subconscious mind in motion!