Getting Snapshot too old error while masking the data

  • 0
  • 1
  • Problem
  • Updated 2 years ago
  • Solved
Two of the masking jobs has been failed due to snap shot too old error. Is there any way to avoid this error?
Photo of Rafigue Memon

Rafigue Memon

  • 70 Points

Posted 2 years ago

  • 0
  • 1
Photo of Tim Gorman

Tim Gorman, Field Services

  • 2,794 Points 2k badge 2x thumb
Official Response
The ORA-01555 ("snapshot too old") error is always fun to diagnose and prevent.

It is always a bad sign when a particular issue warrants its own "Master Note" page in the MyOracleSupport portal, as this topic does.  But that just let's you know how many different causes can lead to this error.

Just off-the-cuff, what is the setting of the UNDO_RETENTION parameter in this database?  The default is 900 seconds (15 mins) and that is usually too low, so you might consider setting that value to match or exceed the expected duration of the masking job(s).  For example, if your masking jobs take 2-4 hours, you might want to consider ensuring that UNDO_RETENTION has a value of 14,400 seconds or greater.  The good news is that UNDO_RETENTION is dynamically changeable with very low risk of unexpected side-effects to the Oracle database.

The good news is that setting this parameter and sizing the UNDO tablespace large enough is usually all that is necessary.  The bad old days of fiddling with rollback segments are long in the past.

Please let me know if this helps?

Thanks!

-Tim