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 long–running processes as a series of restart–able 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 slots available.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez
Anuradha’s LinkedIn: https://www.linkedin.com/in/dbaanuradhamudgal/
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
