Saminnet-Search Article Wiki Forum Blog SNS Cloud
Avoid ora-1555

  • Data-Articles
    • Slower CKD stage (3) Wed12,16:41pm

      Having your kidneys work 窶 even a little 窶 can help you feel better and live longer. If you can slow your CKD, you can delay the need for treatment of kidney failure. The types of changes you might make to help your heart or the rest of your body will help your kidneys, too. Here are some things you can do 窶 or avoid 窶 to protect your kidneys: r Blood Sugar In The Target Range.…

      Read More...

Avoid ora-1555

This message appears as a result of an Oracle read consistency mechanism. While your query begins to run, the data may be simultaneously changed by other people accessing the data. Oracle cannot access the original copy of the data from when the query started, and the changes cannot be undone by Oracle as they are made. Both committed versions of blocks and uncommitted versions of blocks are maintained to ensure that queries can access the data as it exists in the database at the time of the query. This is referred to as 窶彡onsistent read窶 blocks and is maintained by Oracle Automatic Undo Management (AUM).

For example, you may begin your SQL query at 1:00 PM, yet at the same hour, another user may be making changes to the data from another computer. If this occurs, you may encounter error ORA-01555 because the results outputted by Oracle must contain data as it appeared at 1:00PMツbefore changes were made by the other user.

ORA-01555 relates to insufficient rollback segments orツundo_retentionsツparameter values that are not large enough. The modified data by performed commits and rollbacks causes rollback data to be overwritten when the rollback segments are smaller in size and number of the changes being performed at the time.

To resolve this issue, either increase the parameter of UNDO_RETENTION if you are inツAUMツmode or use larger rollback segments. The latter solution will allow your rollback data for completed transactions to be kept longer.

You may also run into this error when cursors are not being in programs after FETCH and UPDATE statements. Make sure you are closing cursors when you no longer need them.ツThe error can also appear if a FETCH statement is run after a COMMIT statement is issued. If this occurs, you will begin to overwrite earlier records because the number of rollback records created since the last CLOSE will fill the rollback segments.

In summary, follow these practicesツto avoid seeing error ORA-01555 in the future:

  • Do not run discrete queries and sensitive queries simultaneously unless the data is mutually exclusive.
  • If possible, schedule queries during off-peak hours to ensure consistent read blocks do not need to rollback changes.
  • Use large optimal values for rollback segments.
  • Use a large database block size to maximize rollback segment transaction table slots.
  • Reduce transaction slot reuse by performing less commits, especially in PL/SQL queries.
  • Avoid committing inside a cursor loop.
  • Do not fetch between commits, especially if the data queried by the cursor is being changed in the current session.
  • Optimize queries to read fewer data and take less time to reduce the risk of consistent get rollback failure.
  • Increase the size of your UNDOツtablespace, and set the UNDOツtablespaceツin GUARANTEE mode.
  • When exporting tables, export with CONSISTENT = no parameter.

Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email