Oracle ORA-12801 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12801: Error Signaled in Parallel Query Server — Causes, Fixes & Prevention

ORA-12801 is a wrapper error in Oracle that indicates one of the parallel query slave processes encountered a failure during parallel execution. It does not describe the root cause on its own — you must always look at the accompanying child error (e.g., ORA-01555, ORA-04031, ORA-00942) to identify the actual problem. This error commonly surfaces during full table scans, parallel joins, or bulk aggregations on large datasets.

Top 3 Causes

1. Memory Exhaustion in Parallel Slaves (ORA-04031)

When parallel degree is set too high or SGA/PGA is undersized, individual slave processes fail to allocate memory and raise ORA-04031, which bubbles up as ORA-12801.

-- Check current parallel and memory parameters
SELECT name, value
FROM   v$parameter
WHERE  name IN (
    'parallel_max_servers',
    'parallel_degree_policy',
    'pga_aggregate_target',
    'sga_target'
);

-- Limit parallel degree at session level to reduce memory pressure
ALTER SESSION SET parallel_max_servers = 8;

-- Use a hint to control degree at query level
SELECT /*+ PARALLEL(t, 4) */ t.order_id, t.amount
FROM   sales_data t
WHERE  sale_year = 2024;

2. Snapshot Too Old During Long Parallel Execution (ORA-01555)

Parallel queries often run longer than serial ones. If undo data is overwritten before a slave finishes reading, ORA-01555 fires inside the slave and gets reported as ORA-12801.

-- Check current UNDO_RETENTION setting
SELECT name, value
FROM   v$parameter
WHERE  name = 'undo_retention';

-- Increase UNDO_RETENTION (value in seconds)
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;

-- Monitor Snapshot Too Old occurrences
SELECT TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
       maxquerylen,
       ssolderrcnt   -- Snapshot Too Old error count
FROM   v$undostat
ORDER  BY begin_time DESC
FETCH FIRST 12 ROWS ONLY;

3. Missing Privileges or Object Access Issues (ORA-00942)

Parallel slaves run as independent OS processes. Privileges granted through roles are not visible to them. If a slave cannot access a table or view, it raises ORA-00942, which becomes ORA-12801 at the coordinator level.

-- Check if privilege is granted via role (problematic for parallel)
SELECT grantee, granted_role
FROM   dba_role_privs
WHERE  grantee = 'APP_USER';

-- Fix: grant privileges directly instead of through a role
GRANT SELECT ON owner_schema.large_table TO app_user;

-- Temporarily disable parallel to isolate the error
ALTER SESSION DISABLE PARALLEL QUERY;

-- Re-enable after testing
ALTER SESSION ENABLE PARALLEL QUERY;

Quick Fix Solutions

-- 1. Identify active parallel sessions and their errors
SELECT qc_session_id,
       server_group,
       degree,
       state
FROM   v$px_session
ORDER  BY qc_session_id;

-- 2. Check recent ORA-12801 occurrences in the alert log (12c+)
SELECT originating_timestamp,
       message_text
FROM   v$diag_alert_ext
WHERE  message_text LIKE '%ORA-12801%'
ORDER  BY originating_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

-- 3. Disable parallel on a specific table if errors persist
ALTER TABLE large_table NOPARALLEL;

Prevention Tips

Control parallel degree with a policy and Resource Manager

Set PARALLEL_DEGREE_POLICY to AUTO or LIMITED to prevent runaway parallelism. Use PARALLEL_SERVERS_TARGET to cap the total number of active slave processes system-wide.

ALTER SYSTEM SET parallel_degree_policy   = 'AUTO' SCOPE=BOTH;
ALTER SYSTEM SET parallel_servers_target  = 32     SCOPE=BOTH;
ALTER SYSTEM SET parallel_degree_limit    = 8      SCOPE=BOTH;

Monitor undo usage and PGA health proactively

Schedule regular checks on v$undostat and v$pgastat. Ensure your undo tablespace has autoextend enabled and that UNDO_RETENTION is tuned to cover the longest expected parallel query duration. Catching undo pressure early prevents ORA-01555 → ORA-12801 cascades before they reach production users.

Related Errors

Error Code	Description
ORA-01555	Snapshot too old — most common child error with ORA-12801
ORA-04031	Unable to allocate shared memory — parallel slave memory failure
ORA-00942	Table or view does not exist — privilege/object access in slaves
ORA-12805	Parallel query server died unexpectedly
ORA-00604	Error at recursive SQL level — can chain into ORA-12801

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community