Answer

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

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

Brian Peasland
By
Published: 25 Mar 2009

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