7 Replies Latest reply on Feb 17, 2017 7:12 AM by Beauty_and_dBest

    EBS R12.1 Forms Transaction Lock

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.1.3

      OL6

      11gR2

       

       

      What causes transaction interface locks? We encountered error below:

      unnamed.png

      But if I run the lock monitoring script, there is no locked row found

       

      SELECT

          O.OBJECT_NAME,

          S.SID,

          S.SERIAL#,

          P.SPID,

          S.PROGRAM,

          SQ.SQL_FULLTEXT,

          S.LOGON_TIME

      FROM

          V$LOCKED_OBJECT L,

          DBA_OBJECTS O,

          V$SESSION S,

          V$PROCESS P,

          V$SQL SQ

      WHERE

          L.OBJECT_ID = O.OBJECT_ID

          AND L.SESSION_ID = S.SID

          AND S.PADDR = P.ADDR

          AND S.SQL_ADDRESS = SQ.ADDRESS;

       

       

      Please help...

       

       

      Kind reagrds,

      jc

        • 1. Re: EBS R12.1 Forms Transaction Lock
          Shaik

          Hi JC

           

          Check with your functional team can tell us the name of the table of this forms, then you can give in below query

           

          SELECT username U_NAME, owner OBJ_OWNER,

          object_name, object_type, s.osuser,s.sid,

          DECODE(l.block,

            0, 'Not Blocking',

            1, 'Blocking',

            2, 'Global') STATUS,

            DECODE(v.locked_mode,

              0, 'None',

              1, 'Null',

              2, 'Row-S (SS)',

              3, 'Row-X (SX)',

              4, 'Share',

              5, 'S/Row-X (SSX)',

              6, 'Exclusive', TO_CHAR(lmode)

            ) MODE_HELD

          FROM v$locked_object v, dba_objects d,

          v$lock l, v$session s

          WHERE v.object_id = d.object_id

          AND (v.object_id = l.id1)

          AND v.session_id = s.sid

          and object_name like 'MTL_SYS%FVL' ---Table Name

          ORDER BY username, session_id;

           

           

           

          OR

           

          Check this query too

           

          select                       ----This same as above query ..above added alter system kill command

             c.owner,

             c.object_name,

             c.object_type,

             b.sid,

             b.serial#,

             b.status,

             b.osuser,

             b.machine

          from

               gv$locked_object a ,

             gv$session b,

             dba_objects c

          where

             b.sid = a.session_id

          and

             a.object_id = c.object_id;

           

           

          Regards

          Shaik

          • 2. Re: EBS R12.1 Forms Transaction Lock
            Shaik

            Hi JC

             

            Just change gv$ to v$ in above query

             

            Regards

            Shaik

            • 3. Re: EBS R12.1 Forms Transaction Lock
              John_K

              The lock you are describing there is nothing to do with database locks. Records in the interface are "soft locked" during processing by the setting of the lock_flag column. Nothing at all to do with locks at the db level.

              • 4. Re: EBS R12.1 Forms Transaction Lock
                mdtaylor

                Hi jc,

                 

                I cannot tell from your screenshot what type of inventory transaction this is, but the following articles may be helpful, especially the first one.

                 

                Error Code=Oracle error : Stuck Inventory Material Transactions Open Interface (MTI) or Failing Interface Trip Stop (ITS) Request (Doc ID 1601941.1)

                CANNOT INTERFACE INVENTORY LINES AS THEY ARE LOCKED BY ANOTHER PROCESS (Doc ID 567471.1)

                Inventory Interface Failing for Some Deliveries (Doc ID 754528.1)

                 

                Regards,

                Michael

                • 5. Re: EBS R12.1 Forms Transaction Lock
                  Beauty_and_dBest

                  Thanks Shaik,Ora-1033, Mdtaylor and all,

                   

                  Its MTL inventory transactions.

                  So how do I kill or release soft locks?

                   

                   

                  Kind regards,

                  • 6. Re: EBS R12.1 Forms Transaction Lock
                    mdtaylor

                    Hi jc,

                     

                    You have to clear and reprocess the stuck transaction in the interface using the update scripts in the link below:

                     

                    Transaction Processor Error On Transactions Interface And Pending Material Transactions Tables (Doc ID 1220763.1)

                     

                    Regards,

                    Michael

                    • 7. Re: EBS R12.1 Forms Transaction Lock
                      Beauty_and_dBest

                      Thanks ALL,

                       

                       

                      a) As example, using the pending material transactions table, the following script will do that for you:

                      UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP

                      SET TRANSACTION_BATCH_ID = NULL,

                         PROCESS_FLAG = 'Y',

                         LOCK_FLAG = 'N',

                         ERROR_CODE = NULL,

                         TRANSACTION_MODE = 3

                      WHERE ERROR_CODE = 'Transaction Processor Error'

                      AND Transaction_Temp_id = &YourTransactionID;

                       

                      b) For records stuck in the interface you can use the following script:

                      UPDATE MTL_TRANSACTIONS_INTERFACE

                      SET TRANSACTION_BATCH_ID = NULL,

                         PROCESS_FLAG = 1,

                         LOCK_FLAG = 2,

                         ERROR_CODE = NULL,

                         TRANSACTION_MODE = 3

                      WHERE ERROR_CODE = 'Transaction Processor Error'

                      AND Transaction_Interface_id = &YourTransactionID;