Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 10 December 2013

ORA-02049 time-out: distributed transaction waiting for lock

Today one of the user written to me he faces the below error while trying to insert many records into one table: ORA-02049 time-out: distributed transaction waiting for lock
Finally he has decided to flush the shared pool as a solution to the above issue and asking me using the command:  “Alter system flush shared_pool” is having any other impact on the database.
Cause: The time to wait on a lock in a distributed transaction has been exceeded. This time is specified in the initialization parameter DISTRIBUTED_LOCK_TIMEOUT.
Action: This situation is treated as a deadlock and the statement was rolled back. To set the time-out interval to a longer interval, adjust the initialization parameter DISTRIBUTED_LOCK_TIMEOUT, then shut down and restart the instance.

Normally oracle keeps track of each SQL statement that users execute. It is store parsed in memory so that if a SQL statement has been executed then oracle does not need to reparse it as it is already parsed and stored in shared_pool.
Thus it is not a standard solution to flush shared_pool; you can use it only if you must. It will clear all the data in shared pool along with all your previous snapshot obsolete even your cache sequence number is also flushed.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

Using the following one can implement the functionality in a store procedure.
SQL> CREATE OR REPLACE procedure flush_shared_pool IS
v_cur INTEGER;
v_result INTEGER;
BEGIN
v_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur, ‘ALTER SYSTEM FLUSH SHARED_POOL’, DBMS_SQL.NATIVE);
v_result := DBMS_SQL.EXECUTE(v_cur);
DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/
And in special circumstances one can grant execute access “grant execute on sys.flush_shared_pool to SCOTT;” to this store procedure if there is a need in a development environment.

2 comments:

  1. Internal discovery address: https://webdir.online.lync.com/Autodiscover/autodiscoverservice.svc/Root
    External discovery address: https://webdir.online.lync.com/Autodiscover/autodiscoverservice.svc/Root

    ReplyDelete
  2. Did you know that that you can earn cash by locking selected sections of your blog or website?
    To begin you need to join AdWorkMedia and use their content locking tool.

    ReplyDelete