Ways to avoid ORA-01555 snapshot too old error..!!

ByJoel Pérez ( Oracle ACE Director )

Ways to avoid ORA-01555 snapshot too old error..!!

Ways to avoid ORA-01555 snapshot too old error..!!

Being the “Oracle DBA ” when the UNDO topic comes in an interview question. Normally interviewer as an about ORA-01555 snapshot too old error.   We reply about three or four lines as an answer For this question. For strong knowledge, we must have deep knowledge about this. Let’s have look on ORA-01555 snapshot too old error.

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 the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

The ORA-01555 snapshot too old error can be addressed by several remedies:-

  • Re-schedule long-running queries when the system has less DML load.
  • Increasing the size of your rollback segments(undo)size. The ORA-01555 snapshot too old also relates to your setting for automatically undo retention.
  • Don’t fetch between commits.

Ways to avoiding the ORA-01555 error are mention below:

  • Do not run discrete transactions while sensitive queries or transactions are running, unless you are confident that the data sets required are mutually exclusive.
  • Schedule long running queries and transactions out of hours, so that the consistent gets will not to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.
  • Code longrunning processes as a series of restartable steps.
  • Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce the risk of consistent get rollback failure due to extent reallocation.
  • Use a large optimal value on all rollback segments, to delay extent reuse.
  • Don’t fetch across commits,  that is, don’t fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.
  • Use a large database block size to maximize the number of slots in the rollback segment transaction tables, and thus delay slot reuse.
  • Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.
  • If necessary, add extra rollback segments (undo logs) to make more transaction slotsavailable.

Thank you for giving your valuable time to read the above information.

Subscribe to get the notifications of our daily articles in your email..!

We hope the content could be useful for your Oracle DBA Tasks!

This article is brought by Yunhe Enmo (Beijing) Technology Co.,LTD En.enmotech.com

Follow our Company in Linkedin: https://www.linkedin.com/company/1856106/

Joel Pérez’s Linkedin Profile: https://www.linkedin.com/in/sirdbaasjoelperez/

Post Author Bios:

Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialised in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish, Portuguese, English and more. Regular Speaker in worldwide Oracle events like: OTN LAD (Latin America), OTN MENA (Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China, Oracle Code.. . Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004. He was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Senior Cloud Database Architect in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China En.enmotech.com

Joel Pérez’s Linkedin Profile: https://www.linkedin.com/in/sirdbaasjoelperez/

About the author

Joel Pérez ( Oracle ACE Director ) administrator