11 Replies Latest reply on Nov 11, 2017 2:11 PM by Kanda-Oracle

    Forms lock?

    Beauty_and_dBest

      EBS R12.2.6

      12c

      OL6

       

      How to correct or release a record lock?

      I was updating record thru dataload when the connection got disrupted. Screenshot below blocks me from continuing the update.

      image (1).png

       

       

      I checked the database but I can not find row locks.

       

       

      Please help....

       

       

      Kind regards,

      jc

        • 1. Re: Forms lock?
          Bashar.

          Hi,

           

          Use this query to identify the sessions locking the table and use the kill command for this purpose.

           

          select b.sid, b.serial#, b.status, lockwait, b.username, a.object_name, b.osuser, b.machine, b.program, module, action, logon_time, 'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' kill_command

          from dba_objects a, v$session b, v$locked_object c

          where a.object_id = c.object_id

            and b.sid = c.session_id

            and username = 'APPS'

            and a.object_type = 'TABLE'

            and a.owner = 'APPLSYS'

          order by sid,serial#;

           

          Regards,

          Bashar

          • 2. Re: Forms lock?
            Beauty_and_dBest

            Thanks Bashar

             

             

            Long time no see?

             

            I run the kill session script but still the issue persist

             

            SQL> select b.sid, b.serial#, b.status, lockwait, b.username, a.object_name, b.osuser, b.machine, b.program, module, action, logon_time, 'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' kill_command

              2  from dba_objects a, v$session b, v$locked_object c

              3  where a.object_id = c.object_id

              4  and b.sid = c.session_id

              5  and username = 'APPS'

              6  and a.object_type = 'TABLE'

              7  and a.owner = 'APPLSYS'

              8  order by sid,serial#;

                   196      10709 INACTIVE                  APPS

            FND_CONCURRENT_QUEUES

            appcrp1

            orar12

            FNDSCH@orar12 (TNS V1-V3)

            e:FND:cp:FNDSCH

            /

            09-NOV-17

            alter system kill session '196,10709';

             

                   316        587 INACTIVE                  APPS

            FND_CONCURRENT_REQUESTS

            appcrp1

            orar12

            ICM@orar12 (TNS V1-V3)

            e::cp:30

            /

            09-NOV-17

            alter system kill session '316,587';

             

            SQL> alter system kill session '316,587';

             

            System altered.

             

            SQL>

             

            When I query the record, it still can not be opened

            image (2).png

             

            Kind regards,

            • 3. Re: Forms lock?
              Bashar.

              Yeah.

              I hope I'll be back as an active member just like the old days.

              • 4. Re: Forms lock?
                Beauty_and_dBest

                Thanks Bashar,

                 

                Maybe you have taken so big projects?

                 

                Do I need to commit kill session?

                 

                 

                Kind regards,

                • 5. Re: Forms lock?
                  Bashar.

                  Sort of

                   

                  You need to kill sessions locking tables starting with FND_FLEX%.

                  No need to commit after killing a session.

                   

                  Regards,

                  Bashar

                  • 6. Re: Forms lock?
                    John_K

                    Hi,

                    That is not a lock - that is an issue with the record being modified after you have read it. So you read the record, someone else updated it, and so now the version you read is not the version on the database. It won't show up on the v$locked_objects view.

                    • 7. Re: Forms lock?
                      Beauty_and_dBest

                      Hi Ora, Bashar, and ALL,

                       

                       

                      This is a result of an aborted or disconnected dataloader, due to network failure. The row seems locked or corrupted?

                      How do I correct it or update it or delete it?

                      Do I need to bounce the apps and database?

                       

                       

                      Kind regards,

                      • 8. Re: Forms lock?
                        Kanda-Oracle

                        Hi JC,

                         

                        1. Did you try to to bounce the apps and database to isolate the issue ?

                         

                        2. If the any of the underlying table (used in the form)  is having invalid leading or trailing spaces and control characters; then it can cause below errors when the column is queried in a form.

                         

                          FRM-40654: Record has been updated.

                         

                        How To Run afchrchk.sql Script To Resolve FRM-40654 Error (Doc ID 960115.1)

                         

                        2.1      Note : please go through the note before running on flex tables. and take necessary backup.

                         

                        Thanks

                         

                        N Kandasamy

                        • 9. Re: Forms lock?
                          John_K

                          So after closing the application, reopening it, you get the same issue?

                          • 10. Re: Forms lock?
                            Beauty_and_dBest

                            Thanks Kanda, Ora, and ALL,

                             

                             

                            Yes, I have bounce the apps and database but the issue persist.

                             

                            If I use the script  afchrchk.sql , how do I know which table was affected? Based on the form above?

                             

                             

                            Kind regards,

                            • 11. Re: Forms lock?
                              Kanda-Oracle

                              You may enable DB trace or Record History  to know the tables involved.