One of the greatest achievements of Oracle database software is its ability to process massive sums of information into a multitude of tables and functions. The downside is that in any system, dealing with huge amounts of data can naturally carry some headaches and frustration along the way. The ORA-12801 would most easily fall into this category, an Oracle error emanating most often in response to errors when processing large jobs.
The Problem
The ORA-12801 is an Oracle error that has some confusing origins, but is described as an error resulting in some form from a parallel query server. The error frequently accompanies another error, often the ORA-01652 (which concerns an inability to extend the temp segment in a tablespace). The ORA-12801 is essentially a general error, a sort of catchall for parallel query issues.
At this juncture, some of you may be wondering yourselves, “What is a parallel query?” First showing up in later versions of Oracle7, a parallel query option lets multiple processes request and receive data and perform operations at the same time. As you would imagine, this can greatly enhance the speed of the system. To achieve this kind of efficiency, there are several conditions for the parallel query that require attention. So what are those conditions and how do they pertain to resolving an ORA-12801 error?
The Solution
An absolutely crucial element in successful parallel query operation is the appropriate use of query slaves. If too few or too many are put to use, the system will receive little advantage from their presence. Generally, a good rule to follow is duplicate the number of disks or computers that the operation is used across, and set that as your number of parallel query slaves. Additionally, your tables should account for the parallel query. Run a full table scan to assess the timing of various settings for the degrees of parallel function to determine the best variable size for your performance.
Due to the nature of the ORA-12801, it’s a good idea to examine the error more closely. Set the event to 10397 to gather up information on the error (including the cause, action and some simple comments). You can also check out trace files in the BACKGROUND_DUMP_TEST and USER_DUMP_TEST for more details, as well as your alert.log. You will typically be given a parallel query server number in the error that will give an idea of how many processes are being run, hopefully reminding your of the aforementioned rule of thumb in keeping your query slave numbers at a level that can accommodate your operation. When accompanied with an error such as ORA-01652, you can typically increase the size of your sort_area_size or TEMP tablespace to allow for the job to run effectively.
Looking forward
Avoiding an ORA-12801 can be complicated, but should you remain cognizant of your parallel query operations and know the basics of investigating your system, you should be okay. Staying on top of parallel queries and ensuring that the proper amount of slave and table degree variables are allocated to the job can go a long way in preventing this (and several other) errors. Furthermore, by referring to your alert.log and dump tests from time to time, you can spot the origins of potential errors or obtain additional information of a pending error that is proving to be enigmatic. If you find that generic system errors such as the ORA-12801 are still tripping you up, it wouldn’t hurt to contact a licensed Oracle consultant to assist you with these problems and optimize your experience with Oracle products.
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.
Ora-12801 parallel query server failed to start is an error that prevents the user from properly running Oracle’s parallel query. This error occurs when the user has attempted to start the instance in the wrong parallel mode. The error can be caused by a combination of different factors, including the presence of incompatible components in the environment, improper parameter settings, or a lack of memory. This document will explain how to troubleshoot and resolve this error.
Prerequisites
Before attempting to fix the ORA-12801 error, make sure the user has the following items:
- Access to the Oracle Error Code Documentation
- Access to Oracle Support
- Understanding of the user’s environment
- Knowledge of the user’s query server
- Knowledge of Oracle parallel query
Identifying the Cause of ORA-12801
The first step in fixing this error is to identify the root cause of the issue. To determine the cause, the user should go through the following steps:
- Review the Oracle error message for clues about the cause of the issue.
- Execute the query again with different system parameters.
- If the same ORA-12801 error appears, continue troubleshooting by examining applicable components within the user’s environment.
- Examine the Oracle documents related to parallel query server issues.
Troubleshooting
Once the user has identified the cause of the ORA-12801 error, they should investigate the following common causes and troubleshooting steps:
Incompatible Components
If incompatible components (such as another query server or version) are present in the user’s environment, the ORA-12801 may occur. To resolve this issue, the user should detect and remove any incompatible components from their environment.
Improper Parameter Settings
Incorrect parameter settings can also cause the ORA-12801 error. The user should investigate the parameters related to the parallel query server, and modify any settings that are incorrect.
Insufficient Memory
In some cases, the ORA-12801 error is caused by a lack of available memory. To resolve this issue, the user should ensure that the system has adequate memory resources allocated for the parallel query server.
FAQ
Q: How can I detect incompatible components in my environment?
A: The user should use Oracle Support resources to detect and remove any incompatible components from the user’s environment.
Q: What should I do if I have insufficient memory?
A: To resolve this issue, the user should ensure that the system has adequate memory resources allocated for the parallel query server.
Q: What parameters related to the parallel query server should I investigate?
A: Parameters related to the parallel query server include the parameter DOP (degree of parallelization), and the parameters SGS_MAX_SERVERS, SGS_GOVERNANCE_LIMIT, and SGS_ENDDATE.
Resources
- Oracle Support Resources
Using SQL Developer 18.4 MacOSX, I’m getting this error
ORA-12801: error signaled in parallel query server P000
ORA-01722:
invalid number
When I try to display the tables list in the connections tab.
I’ve isolated the Query.
When I execute each part of the «union all» one by one, there is no error.
when I execute the all query you got the error.
When I disable the parallel option it works fine (ALTER SESSION disable PARALLEL query).
select * from (
SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, NULL partitioned,
o.sharded,
case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,
NULL iot_type,
o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
FROM SYS.Dba_OBJECTS O
WHERE O.OWNER = :SCHEMA
AND O.OBJECT_TYPE = 'TABLE'
union all
SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME, NULL partitioned,
o.sharded,
case when o.sharded <> 'Y' then o.duplicated else 'N' end duplicated,
NULL iot_type,
SYN.TABLE_OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
FROM SYS.Dba_OBJECTS O, sys.user_synonyms syn
WHERE syn.table_owner = o.owner
and syn.TABLE_NAME = o.object_NAME
and o.object_type = 'TABLE'
and :INCLUDE_SYNS = 1
)
Troubleshooting
Problem
Error
ORA-12801 Error signaled in parallel query server.
This error occurs when adding a calculation as follows:
Variance calculation (A-B). This is successful.
Add % Variance ( (A-B)/A) and the error occurs.
Resolving The Problem
ORA-12801 error signaled in parallel query server name
Cause: A parallel query server encountered an exception as described in the accompanying message(s).
Action: Correct the error described in the accompanying message.
To resolve this issue, use the method to resolve divide-by-zero errors. This will force local processing (not doing A/B or if (B<>0) then (A/B) else (0).
Calculation:(if (B<>0 then (A) else (0)) / (if (B<>0) then (B) else (1))
[{«Product»:{«code»:»SSTQPQ»,»label»:»IBM Cognos Series 7 PowerPlay»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Impromptu»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»Impromptu 5.0;Impromptu 7.1;Impromptu Web Reports 7.0″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}},{«Product»:{«code»:»SSTQPQ»,»label»:»IBM Cognos Series 7 PowerPlay»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Impromptu Web Reports»,»Platform»:[{«code»:»»,»label»:»»}],»Version»:»»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]