DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02067 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02067: Transaction or Savepoint Rollback Required

ORA-02067 is an Oracle error that occurs in distributed transaction environments when a failure forces Oracle to require a full transaction or savepoint rollback before any further work can proceed. This error typically surfaces when using Database Links (DBLinks) for remote procedure calls or distributed DML operations. Until a ROLLBACK is issued, the current session cannot perform any new transactions.

Top 3 Causes

1. Remote Database Failure During Distributed Transaction

When a network interruption or remote server failure occurs mid-transaction over a database link, Oracle marks the transaction as unreliable and demands a rollback.

-- This scenario can trigger ORA-02067
BEGIN
    -- Remote insert via DB Link
    INSERT INTO orders@remote_db (order_id, amount)
    VALUES (1001, 500.00);

    -- If remote DB goes down here, ORA-02067 is raised
    UPDATE inventory@remote_db SET qty = qty - 1 WHERE item_id = 50;

    COMMIT;
END;
/

-- Immediate fix: rollback first
ROLLBACK;
Enter fullscreen mode Exit fullscreen mode

2. Unhandled Exception in Remote Stored Procedure

When a remote procedure call raises an exception that propagates back unhandled, Oracle flags the entire distributed transaction as invalid.

-- Bad practice: no exception handling
BEGIN
    remote_proc@remote_db_link(p_id => 200);
    COMMIT;
END;
/

-- Good practice: wrap with proper exception handling
BEGIN
    SAVEPOINT before_remote_call;

    remote_proc@remote_db_link(p_id => 200);

    UPDATE local_status SET status = 'DONE' WHERE id = 200;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- Must rollback fully on ORA-02067
        DBMS_OUTPUT.PUT_LINE('Failed: ' || SQLERRM);
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Two-Phase Commit (2PC) Protocol Failure

Oracle's distributed transactions use 2PC. If any remote node fails to respond during the prepare phase, the coordinator node raises ORA-02067 and requires a rollback.

-- Check for in-doubt (stuck) distributed transactions
SELECT LOCAL_TRAN_ID,
       GLOBAL_TRAN_ID,
       STATE,
       ADVICE,
       FAIL_TIME
FROM DBA_2PC_PENDING;

-- Force rollback of a stuck transaction (DBA action)
ROLLBACK FORCE 'local_tran_id_here';

-- Or force commit after verifying data consistency
COMMIT FORCE 'local_tran_id_here';

-- Clean up after resolution
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id_here');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: Always rollback immediately when ORA-02067 occurs
ROLLBACK;

-- Step 2: Check and close broken DB links
SELECT * FROM V$DBLINK;
ALTER SESSION CLOSE DATABASE LINK your_db_link_name;

-- Step 3: Check for in-doubt transactions
SELECT COUNT(*) FROM DBA_2PC_PENDING;

-- Step 4: Retry with proper error handling
DECLARE
BEGIN
    INSERT INTO remote_table@your_db_link (id, val) VALUES (1, 'test');
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always Use Exception Handling in Distributed Code
Every PL/SQL block that uses a database link must include EXCEPTION WHEN OTHERS THEN ROLLBACK. Standardize this pattern across your codebase to prevent partial distributed transactions from leaving sessions in an unrecoverable state.

-- Standard template for all distributed transactions
CREATE OR REPLACE PROCEDURE safe_distributed_proc AS
BEGIN
    -- your distributed work here
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        -- log error
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Monitor DBA_2PC_PENDING Regularly
Set up a scheduled job to alert the DBA team whenever in-doubt transactions exist. Also tune DISTRIBUTED_LOCK_TIMEOUT (default 60 seconds) to control how long Oracle waits before timing out a distributed transaction, reducing the window for ORA-02067 occurrences.

-- Quick health check query
SELECT STATE, COUNT(*) 
FROM DBA_2PC_PENDING 
GROUP BY STATE;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-02050 – Transaction rolled back; some remote DBs may be in-doubt
  • ORA-02055 – Distributed update failed; rollback required
  • ORA-01591 – Lock held by in-doubt distributed transaction
  • ORA-02051 – Transaction already in-doubt

📖 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.

Top comments (0)