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