Users' questions

How to resolve ORA-02049?

by Rhyley Bryan

How to resolve ORA-02049?

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.

How to resolve ORA-02049 timeout distributed transaction waiting for lock?

ORA-02049: TIMEOUT: DISTRIBUTED TRANSACTION WAITING FOR LOCK

  1. RELATED. A BLOCKING SESSION.
  2. DIAGNOSE.
  3. TESTCASE.
  4. As a database administrator you can increase the distributed_lock_timeout parameter to a high value.
  5. REFERENCES.

What is distributed_ lock_ timeout?

DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for distributed transactions to wait for locked resources. See Also: Oracle Database Concepts and Oracle Database Administrator’s Guide for more information on data concurrency.

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

ORA-00060: Deadlock detected. More info in file c:\oracle\product\10.2….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 JTA timeout?

WebLogic will automatically roll back transactions that don’t complete in a certain number of seconds. To change the setting, open the WebLogic Server Console, go to the JTA page for the domain ATG is installed in, and change the value in the Timeout Seconds field. ATG recommends setting the timeout to 120 seconds.

What is XA transaction timeout?

XA Transaction Timeout. The number of seconds to set as the transaction branch timeout. If set, this value is passed as the transaction timeout value in the XAResource. setTransactionTimeout() call on the XA resource manager, typically the JDBC driver.

How can we avoid deadlock in Oracle?

LOCK IN SHARE MODE ), try using a lower isolation level such as READ COMMITTED . When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock.

How deadlock is fixed in Oracle?

Resolving Deadlock

  1. Ask the session getting deadlock error ORA-00060 to issue either COMMIT or ROLLBACK.
  2. Ask the waiting session to kill the SQL / transaction.
  3. Look inside alert log / trace file for the sessions involved in deadlock and inform application team to improve the code.

How do I increase my JTA timeout?

To change this setting, connect to WebLogic Server Administration Console. On the Domain > Configuration > JTA >Timeout Seconds tab, modify the Java Transaction API (JTA) timeout to be a minimum of 600.

What is JTA in WebLogic?

On the Domain—>Configuration—>JTA tab in the Administration Console, you can configure Java Transaction API (JTA) attributes to suit your environment. Once you configure WebLogic JTA and any transaction participants, the system can perform transactions using the JTA API and the WebLogic JTA extensions.

How do I know if XA transactions are enabled?

Right-click Local DTC and then select Properties. Click the Security tab on the Local DTC Properties dialog box. Select the Enable XA Transactions check box, and then click OK.

What is XA JDBC?

The JDBC XA distributed transaction management classes enable you to use the IBM® Toolbox for Java™ JDBC driver within a distributed transaction. Using the XA classes to enable the IBM Toolbox for Java JDBC driver allows it to participate in transactions that span multiple data sources.

What causes ora-02049 error in Oracle Database?

This being, the package can load if the shared pool is flushed or the SHARED_POOL_SIZE is increased. Ihis Oracle Forum thread offers further information regarding the ORA-02049 error. Here, ORA-02049 is said to sometimes be caused by a transaction that waited for a locked object for so long that it times out.

How to get ora-02049, distributed transaction waiting for lock?

Getting ORA-02049: time-out: distributed transaction waiting for lock. The query is generated by the ATG application and uses the update statement for record update. I have no idea what “ATG” is or does.

What causes remote batch job to get ora-02049?

Acccording to the developers they are doing commit and rollback for each record processed. The use of a DB Link opens you up to distributed rules of operation even if you only READ from it. what could cause such problem as they get the errors sometimes but not always…