What causes ORA-02049?

A remote session (via DB link) holds a lock on a table the local session needs. DISTRIBUTED_LOCK_TIMEOUT is set too low for the workload. Long-running transaction on the remote side holds locks indefinitely. Deadlock or lock contention in a distributed environment

How do I fix ORA-02049?

Step 1: Identify the blocking session on the remote database and kill it if stale.. Step 2: Increase DISTRIBUTED_LOCK_TIMEOUT: `ALTER SYSTEM SET DISTRIBUTED_LOCK_TIMEOUT = 120;`.. Step 3: Reduce transaction scope — commit more frequently on the remote side to release locks earlier.. Step 4: Avoid long-running distributed transactions by pulling data locally before transforming.. Step 5: Add retry logic to the pipeline for transient lock timeouts.

High severitylocking

Oracle Database Error:
ORA-02049

What does this error mean?

A distributed transaction (involving a database link) exceeded the DISTRIBUTED_LOCK_TIMEOUT limit while waiting for a lock held by another session.

Common causes

1A remote session (via DB link) holds a lock on a table the local session needs
2DISTRIBUTED_LOCK_TIMEOUT is set too low for the workload
3Long-running transaction on the remote side holds locks indefinitely
4Deadlock or lock contention in a distributed environment

How to fix it

1Step 1: Identify the blocking session on the remote database and kill it if stale.
2Step 2: Increase DISTRIBUTED_LOCK_TIMEOUT: `ALTER SYSTEM SET DISTRIBUTED_LOCK_TIMEOUT = 120;`.
3Step 3: Reduce transaction scope — commit more frequently on the remote side to release locks earlier.
4Step 4: Avoid long-running distributed transactions by pulling data locally before transforming.
5Step 5: Add retry logic to the pipeline for transient lock timeouts.

Example log output

ORA-02049: timeout: distributed transaction waiting for lock

Frequently asked questions

How do I find the blocking session in a distributed transaction?

Query v$lock on the local instance for the blocking SID, then connect to the remote database and query v$session for that session's details.

Source · docs.oracle.com/error-help/db/ora-02049