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 design and architecture
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading