ORA-12801: error signaled in parallel query server P004 and ORA-01555: snapshot too old

Question

I am executing a insert into ... select ... from ... where ... SQL and got following error using Oracle:

java.sql.SQLException: ORA-12801: error signaled in parallel query server P004
ORA-01555: snapshot too old: rollback segment number 32 with name "_SYSSMU32_2039035886$" too small

I read the following doc: http://www.dba-oracle.com/t_ora_12801_parallel_query.htm and http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

Saying ORA-12801 is caused by no enough processors to support parallel query. ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter.

But how can I check related parameters to avoid such issue recur?

Answer 1

ORA-12801 is a generic error message and we must check the second message on the error stack to find the real error. From the manual:

ORA-12801: error signaled in parallel query server string

Cause: A parallel query server reached an exception condition.

Action: Check the following error message for the cause, and consult your error manual for the appropriate action.

There are literally thousands of different reasons for an ORA-12801 error, and that error almost never has anything to do with not enough processors. This is an example of how the site you linked to often contains bad or outdated information. Maybe 17 processes was "a lot" 17 years ago but it's not today. Unfortunately, that site is often the first result from Google.

---

For troubleshooting your second error, ORA-01555, check the UNDO retention, which is the amount of time in seconds, like this:

select value from v$parameter where name = 'undo_retention'

The amount of space available for the UNDO tablespace is also relevant:

select round(sum(maxbytes)/1024/1024/1024) gb
from dba_data_files
where tablespace_name like '%UNDO%';

Once again, see the manual for more information on the parameter.