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
Related Q&A from Brian Peasland
In this expert answer, Brian Peasland explains how to call a stored procedure inside user-defined functions in Oracle Database. Continue Reading
One reader asks how he can make a copy of his Oracle Database but not have any data in it, for test purposes. Continue Reading
Learn how to add disk to ASM diskgroup with two-way mirroring and how to specify a failgroup in this tip from Oracle expert Brian Peasland. Continue Reading