How to solve an ORA-1555 error in the Oracle testing database

Expert Brian Peasland explains to a reader asks why the ORA-1555 error is showing in the Oracle testing database but not in production

We are executing a package in our testing database which is completely in sync with our production database. The package has many procedures, and in production the package gets executed in six hours. But in our test database the package gets stuck in one of the procedures and throws the error ORA-12801: error signaled in parallel query server P005:

ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small

The procedures in test and in production are identical, but we still get the error. We are not able to pinpoint the cause because the same procedure is working fine in production. Can you provide any insight here?

The ORA-1555 error indicates that your UNDO tablespace is too small or the UNDO_RETENTION parameter is not set large enough, or both. Since your package takes six hours to execute, the UNDO_RETENTION parameter should probably be set to at least 21600 (21600 seconds = six hours). You will need a large enough UNDO tablespace to hold this much undo information.

Dig Deeper on Oracle database administration

Data Management
Business Analytics
  • The 3 daily Scrum questions

    The 2020 Scrum Guide removed all references to the three daily Scrum questions, but does that mean you shouldn't ask them anymore?

  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

Data Center
Content Management