To resolve ORA-02049, you would need to increase the SHARED_POOL_SIZE value in init. ora . If you find that the shared pool is large enough and the ORA-02049 error continues to exist, you may also want to try flushing the shared pool with ALTER SYSTEM FLUSH SHARED_POOL; to free even more space.

What is distributed transaction waiting for lock?

1 Answer. Because there are too many row in your Table, When you UPDATE table will be lock. oracle default it set to 60 seconds. if your excute time over 60 seconds will be error.

How do you check if there is a deadlock in Oracle?

In summary, the steps necessary to identify and rectify code causing deadlocks are:

1. Locate the error messages in the alert log.
2. Locate the relevant trace file(s).
3. Identify the SQL statements in both the current session and the waiting session(s).

What is distributed lock timeout?

This error occurs when a session is trying to modify a table’s row in a remote database through a. database link that is already locked by another session. The sessions transaction fails on a timeout if it’s waiting greater than an allowed time in seconds.

What is dba_2pc_pending?

The dba_2pc_pending table contains an “advise” column that directs the database to either commit or rollback the pending item.

What is the difference between lock and deadlock in Oracle?

A deadlock occurs when two or more threads of control are blocked, each waiting on a resource held by the other thread. Note that when one locker in a thread of control is blocked waiting on a lock held by another locker in that same thread of the control, the thread is said to be self-deadlocked.

What is DBA_2PC_NEIGHBORS?

DBA_2PC_NEIGHBORS :Lists all incoming and outgoing in-doubt distributed transactions. It also indicates whether the local node is the commit point site in the transaction.

What is distributed transaction in Oracle?

Unlike a transaction on a local database, a distributed transaction involves altering data on multiple databases. Consequently, distributed transaction processing is more complicated, because Oracle must coordinate the committing or rolling back of the changes in a transaction as a self-contained unit.

What is a blocking session in Oracle?

Blocking sessions occur when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change, rolls the change back or the user logs off the system.

What is DB blocking?

Database blocking occurs when a connection to the SQL server locks one or more records, and a second connection to the SQL server requires a conflicting lock type on the record, or records, locked by the first connection. This results in the second connection waiting until the first connection releases its locks.