Hi Expert,

I'm facing below issue how can i fix it permanently please suggest on this .

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
'DPRPRMIupd_ItemLivefl_Cutover.sql Started'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
*  INFO: (02/12/18-20:51:51):     Updating table STSC.UDT_ITEM
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at line 9

Your suggestion will be highly appreciate .
Thanks in advance
MIHIR KAR#Oracle_DB #UNIX beginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doesn't look like a deadlock.  Looks like a blocking lock.

You need to see what is on the remote system that is blocking your local transaction.
Geert GOracle dbaCommented:
this is not a deadlock
it's a distributed transaction which has failed
you'll have an entry in dba_2pc_pending

in 99% of the cases, i need to cancel those
it's usually because of restarting 1 part of the systems in our shop

you'll have to either run a commit force or a rollback force
and then cleanup with purge_lost_db_entry

see here :
MIHIR KAR#Oracle_DB  #UNIX beginnerAuthor Commented:
Thanks @sdstuber @Greet .

This issue is happening with production DB (the query is executed only once in a day) lock was occurred for a small time period, which i'm facing difficulty to trace the exact query at the time of failure .

So please suggest is there any way to find out the remote Query .
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
What kind of access do you have to the other Oracle database involved here?  This error indicates that some action in your production database involves a database link (that is: a connection to a separate database, called a "remote database").  This other database may actually be on the same host machine as your local, production database (where you see the error).  Or, the other database may be on a different server machine that is connected by either a LAN or WAN to your production database.

We have no idea what your local PL\SQL procedure does, nor what kind(s) of SQL calls it makes to the remote database, and we have no idea what kinds of SQL and/or PL\SQL activities are happening in the remote database, so we can't tell you exactly what you can do to try to avoid the distributed lock.  One suggestion would be to try this procedure at a time when the remote database is less busy.
MIHIR KAR#Oracle_DB  #UNIX beginnerAuthor Commented:
Thanks @Mark

We have Read_only access to Oracle DB . But i'm exactly not found any DB link used in this Block .Please help me out on this .

FLAG_COUNT number;


        superuser.PG_IDRP_GLOBAL.p_log_msg('Updating table STSC.UDT_ITEM');

        UPDATE /*+ parallel(itm,8)*/ STSC.UDT_ITEM itm   ---- Transaction failing from this point when i tried to set my flag to '0'
        SET UDC_ITM_LIV_FL = 0,
        UDC_ITM_LIV_DT =TO_DATE('01-JAN-1970','DD-Mon-YYYY');

         superuser.PG_IDRP_GLOBAL.p_log_msg('Table STSC.UDT_ITEM updated for all ITEMS');

        MERGE /*+ parallel(ITM,8) */ INTO STSC.UDT_ITEM ITM
 USING (SELECT /*+ parallel(BKP,8)*/
                        DISTINCT SHC_ITM,MIN(GO_LIVE_DT) GO_LIVE_DT
                        FROM AAM.IDRP_CUTOVER@IDRPPAA0_RO BKP
                        WHERE TRUNC(BKP.GO_LIVE_DT)<=TRUNC(SYSDATE)
                        AND TRUNC(BKP.GO_LIVE_DT)<>TO_DATE('31-DEC-2199','DD-Mon-YYYY')
                        GROUP BY SHC_ITM
                        ) COVR
                    ON (ITM.ITEM =COVR.SHC_ITM
               UPDATE SET
                        ITM.UDC_ITM_LIV_FL =1,


        superuser.PG_IDRP_GLOBAL.p_log_msg('Number of records updated in table STSC.UDT_ITEM ITM for UDC_ITM_LIV_FL=1 are: '|| FLAG_COUNT);

        superuser.PG_IDRP_GLOBAL.p_log_msg('Table STSC.UDT_ITEM has been updated for ITEMS which have been cutover');


         SELECT /*+ parallel(ITM,8)*/ COUNT(*)
          WHERE UDC_ITM_LIV_FL = 0;

         superuser.PG_IDRP_GLOBAL.p_log_msg('Number of records having UDC_ITM_LIV_FL=0 in table STSC.UDT_ITEM ITM are : '|| UDC_ITM_LIV_FL_CNT);

Geert GOracle dbaCommented:
you'll need the sys user to purge the entry
Mark GeerlingsDatabase AdministratorCommented:
This line in that procedure uses a database link:

And, the next two lines there look very bad to me, from a performance perspective:
                         AND TRUNC(BKP.GO_LIVE_DT)<>TO_DATE('31-DEC-2199','DD-Mon-YYYY')

1. The "trunc" on the database column value likely forces a "full-table scan" of that table.
2. Using "trunc" on both sides of the "<=" operator is totally unnecessary.  (If you use "trunc" on the database column, there is no need to also use "trunc" on sysdate.  But, the "trunc" on sysdate doesn't cost you much in terms of performance.  The "trunc" on the database column is something that should always be avoided if you are concerned about performance.)
3. The second condition there: "AND TRUNC(BKP.GO_LIVE_DT)<>TO_DATE('31-DEC-2199','DD-Mon-YYYY')" is totally unnecessary, since the first line will already exclude any records with a  date greater than "trunc(sysdate)".

Also, the first UPDATE in this procedure with no "where" clause looks very bad from a performance perspective, since that will force an update of *EVERY* row in the STSC.UDT_ITEM table.  We have no idea how many rows this table has, but I don't like to see syntax like this in programs.

Basically, this PL\SQL procedure looks to me like it was written by someone with either:
1. Little knowledge of how to do things efficiently in Oracle
2. Little concern for whether the procedure is efficient or not.
MIHIR KAR#Oracle_DB  #UNIX beginnerAuthor Commented:
Thanks @Mark for your valuable suggestion

3) Developers try to Update the flag and live date for items in table STSC.UDT_ITEM which have been cut-over (for the fresh data load) .

~ As i have limited access to change the prod code .. Please suggest on which part i can remove for working it in a better way .
Geert GOracle dbaCommented:
distinct ?
why ?
looks to me like the person believing a distinct is required in every query
                        FROM AAM.IDRP_CUTOVER@IDRPPAA0_RO BKP
                        WHERE TRUNC(BKP.GO_LIVE_DT)<=TRUNC(SYSDATE)
                        AND TRUNC(BKP.GO_LIVE_DT)<>TO_DATE('31-DEC-2199','DD-Mon-YYYY')
                        GROUP BY SHC_ITM

Open in new window

another one ... i assume this line in the where was to limit records until now ... aka to exclude records from the future ???

Open in new window

well, if a record is from 22h00, it will get returned, even if in the future
with sample as (
  select trunc(sysdate)+8/24 dt from dual
  union all
  select trunc(sysdate)+22/24 dt from dual)
select sysdate, dt from sample
where trunc(dt) <= trunc(sysdate); 

SYSDATE               DT                   
--------------------- ---------------------
15/02/2018 13:27:12   15/02/2018 08:00:00  
15/02/2018 13:27:12   15/02/2018 22:00:00  

2 rows selected.

Open in new window

change to :

Open in new window

and if you only want data of yesterday ...

Open in new window

all depends what you really need

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
"Developers try to Update the flag and live date for items in table STSC.UDT_ITEM which have been cut-over"

This part is clear: ""Developers try to Update the flag".  But then you added "and live date".  Does that mean the "UDC_ITM_LIV_DT" column of the table?

And, do you really want that to update *ALL* rows in the table?  Since there is no "where" clause to limit this update, it will update *ALL* rows in this table every time this procedure is executed!  That does not look correct to me.  If it is correct, I would say that is a poor program design.

What does this mean: "items ... have been cut-over"?  I would expect to see a "where" clause then with a condition something like this:
and cut_over_flag = 'Y'
and cut_over_date < sysdate

But, we have no idea how the concept of "cut-over" is indicated in your table(s).  So, we can't tell you what a "where" clause there should look like to support this.

Also, I agree with Geert's comment about including the word "distinct" in the sub-query that references a remote table.  Yes, that word is legal in Oracle queries.  But, no it is usually not required.  It is usually not the only way to get the job done in Oracle,  And it is usually not the best way to limit the results in an Oracle query.  In your query (which selects only two columns) you already have a "group by" on: "SHC_ITM" and the group operator: "MIN" on the other column: "GO_LIVE_DT".  So, there is no value added at all by including the word "distinct" in this query!

I'm glad that I didn't pay for this procedure, or have to support it, and I certainly did not write it!

I'm also skeptical of these optimizer hints in that program: /*+ parallel(itm,8)*/.  Are you sure that with your server hardware and your data volumes, these hints are a good idea?

This PL\SQL procedure still looks to me like it was written by someone with one (or both) of these:
 1. Little knowledge of how to do things efficiently in Oracle.
 2. Little concern for whether the procedure is efficient or not.
slightwv (䄆 Netminder) Commented:
I also think the first UPDATE is unnecessary but I might be missing something.  It is setting everything to a 0 and  01-JAN-1970 before the MERGE.

Then going through everything again looking for a match in the MERGE to set it to a 1 and COVR.GO_LIVE_DT.

Could you not just add a WHEN NOT MATCHED on the MERGE to set the 0 and 01-JAN-1970?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.