Oracle ORA-12838 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12838: Cannot Read/Modify an Object After Modifying It in Parallel

ORA-12838 is an Oracle error that occurs when you attempt to read or modify a database object within the same transaction after it has already been modified using parallel DML operations. Oracle uses multiple parallel slave processes during parallel DML, making it impossible to guarantee read consistency on the affected object within the same open transaction. The solution is straightforward: always issue a COMMIT after parallel DML before accessing the same object again.

Top 3 Causes

1. Selecting from a Table Immediately After Parallel INSERT

The most common cause. After a parallel INSERT completes, querying the same table without committing triggers ORA-12838.

-- Triggers ORA-12838
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(t, 4) */ INTO orders_archive t
SELECT * FROM orders WHERE order_date < SYSDATE - 365;

-- No COMMIT here — this SELECT will fail
SELECT COUNT(*) FROM orders_archive;  -- ORA-12838

-- Fix: Add COMMIT before querying
COMMIT;
SELECT COUNT(*) FROM orders_archive;  -- Works fine

2. Running Multiple Parallel DML Statements in One Transaction

Executing a parallel UPDATE followed by a parallel DELETE (or any combination) in the same transaction without committing between them will raise ORA-12838.

-- Triggers ORA-12838
ALTER SESSION ENABLE PARALLEL DML;

UPDATE /*+ PARALLEL(t, 4) */ sales_data t
SET    processed_flag = 'Y'
WHERE  sale_date < TRUNC(SYSDATE);

-- Second parallel DML without COMMIT → ORA-12838
DELETE /*+ PARALLEL(t, 4) */ FROM sales_data t
WHERE  processed_flag = 'Y';

-- Fix: COMMIT between each parallel DML
UPDATE /*+ PARALLEL(t, 4) */ sales_data t
SET    processed_flag = 'Y'
WHERE  sale_date < TRUNC(SYSDATE);

COMMIT;  -- Required

DELETE /*+ PARALLEL(t, 4) */ FROM sales_data t
WHERE  processed_flag = 'Y';

COMMIT;

3. Parallel DML Inside PL/SQL Without Explicit COMMIT

When using EXECUTE IMMEDIATE to run parallel DML inside a PL/SQL block, developers sometimes forget to commit before subsequent SQL statements access the same table.

-- Triggers ORA-12838
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

    EXECUTE IMMEDIATE '
        INSERT /*+ PARALLEL(t, 4) */ INTO target_table t
        SELECT * FROM source_table
    ';

    -- Missing COMMIT — next query will fail with ORA-12838
    FOR r IN (SELECT COUNT(*) cnt FROM target_table) LOOP
        DBMS_OUTPUT.PUT_LINE('Rows: ' || r.cnt);
    END LOOP;
END;
/

-- Fix: Add COMMIT after parallel DML
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

    EXECUTE IMMEDIATE '
        INSERT /*+ PARALLEL(t, 4) */ INTO target_table t
        SELECT * FROM source_table
    ';

    COMMIT;  -- Explicit commit required

    FOR r IN (SELECT COUNT(*) cnt FROM target_table) LOOP
        DBMS_OUTPUT.PUT_LINE('Rows: ' || r.cnt);
    END LOOP;

    EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Quick Fix Solutions

Scenario	Fix
Parallel DML then SELECT	Add `COMMIT` before SELECT
Multiple parallel DMLs	Add `COMMIT` between each statement
Don't need parallel DML	Run `ALTER SESSION DISABLE PARALLEL DML`
Remove parallel hint	Drop `/+ PARALLEL /` hint for serial execution

-- Disable parallel DML for the session entirely
ALTER SESSION DISABLE PARALLEL DML;

-- Verify current parallel DML mode
SELECT NAME, VALUE
FROM   V$PARAMETER
WHERE  NAME = 'parallel_dml_mode';

Prevention Tips

1. Enforce "Parallel DML → COMMIT" as a Coding Standard

Every batch script or ETL procedure that uses parallel DML must include an explicit COMMIT immediately after each parallel operation. Add this as a mandatory checklist item in code reviews to prevent ORA-12838 from reaching production.

2. Monitor Active Parallel Transactions

Regularly monitor sessions with open parallel DML transactions to catch long-running or stalled operations early.

-- Check sessions with open transactions
SELECT s.SID, s.USERNAME, s.STATUS,
       t.START_TIME, t.STATUS AS TX_STATUS
FROM   V$SESSION s
JOIN   V$TRANSACTION t ON s.TADDR = t.ADDR
WHERE  s.USERNAME IS NOT NULL
ORDER  BY t.START_TIME;

Related Errors

ORA-12801 — Parallel query server signaled an error; often appears alongside ORA-12838.
ORA-12839 — Similar to ORA-12838; occurs when modifying an object in parallel after it was already modified in the same transaction.
ORA-00060 — Deadlock detected; can occur in parallel DML environments with lock contention.

📖 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