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
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
-
![]()
Migrate an Oracle RAC database to new servers with zero downtime
By: Brian Peasland
-
![]()
REST vs. SOAP: Choosing the best web service
By: Swati Dhingra
-
![]()
How to perform Oracle Flashback Transaction Queries
By: Matthew Morris
-
![]()
Calling stored procedures inside user-defined functions in Oracle Database
By: Brian Peasland
