7 Replies Latest reply on Aug 17, 2016 12:59 PM by ora_learner36

    users session


      Dear All,




      i would like to find the name of blocked and blocking sessions of application users.

      i came across one navigation in enterprise manager   " Select Blocking Sessions under the Additional Monitoring Links section on the Database Performance page. A list of current blocking sessions appears " . But i didn't find  the mentioned navigation in my  enterprise manager  as i have ebs version 12.1.3  and database


      Please,help your help will be appreciated thanks in advance.



      OS:RHEL 6.1





        • 1. Re: users session
          Pavan Kumar B


          Try with this sql query.


          SELECT DECODE(vl.request,0,'Holder: ','Waiter: ')||vl.sid sess_id, vs.serial#,

          vl.inst_id,vl.id1, vl.id2, vl.lmode, vl.request, vl.type,

          q'[ALTER SYSTEM KILL SESSION ']'||vl.sid||q'[,]'||vs.serial#||q'[';]' "Kill_query"

          FROM gV$LOCK vl

          JOIN gv$session vs ON (vl.sid=vs.sid)

          WHERE (vl.id1, vl.id2, vl.type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0)

          ORDER BY vl.id1,vl.request;

          • 2. Re: users session

            Hi pavan,


            Thanks for replying.


            Is the above given query works with FND_USERS also?


            if not please send the query to find blocked and blocking FND_USERS.


            your help will be appreciated.

            • 3. Re: users session
              Pavan Kumar B



              No. not for fnd_user.

              But you can find usernames. first find the request_id by using sid value from the above output. then find fnd_user name with  request id.

              use the below queries.


              -- find req_id from sid


              select f.request_id, v.spid,s.sid, s.user#,s.username,s.serial#, s.osuser, s.status

              from v$process v, v$session s, applsys.fnd_concurrent_requests f

              where 1=1

              and s.paddr=v.addr

              and f.oracle_process_id=v.spid

              and trunc(f.request_date)=trunc(sysdate)

              and sid=&SID;


              -- find username and conc_prog_details by using req_id



                     fu.user_name                           "User ID",

                     frt.responsibility_name                "Responsibility Used",

                     fcr.request_id                         "Request ID",

                     fcpt.user_concurrent_program_name      "Concurrent Program Name",

                     flv2.meaning                           "Phase",

                     flv1.meaning                           "Status",

                     fcr.request_date                       "Request Date",

                     fcr.requested_start_date               "Request Start Date",

                     fcr.parent_request_id                  "Parent Request ID"


                     fnd_user                    fu,

                     fnd_responsibility_tl       frt,

                     fnd_concurrent_requests     fcr,

                     fnd_concurrent_programs_tl  fcpt,

                     fnd_lookup_values           flv1,

                     fnd_lookup_values           flv2



                 AND flv1.lookup_code           =  fcr.status_code

                 AND flv1.lookup_type           =  'CP_STATUS_CODE'

                 AND flv1.enabled_flag          =  'Y'

                 AND flv1.view_application_id   <> 0

                 AND flv2.lookup_code           =  fcr.phase_code

                 AND flv2.lookup_type           =  'CP_PHASE_CODE'

                 AND flv2.enabled_flag          =  'Y'

                 AND flv2.view_application_id   <> 0

                 AND fu.user_id                 =  fcr.requested_by

                 AND fcr.concurrent_program_id  =  fcpt.concurrent_program_id

                 AND fcr.responsibility_id      =  frt.responsibility_id

                 AND frt.LANGUAGE               =  USERENV ('LANG')

                 AND fcpt.LANGUAGE              =  USERENV ('LANG')

                 AND fcr.request_id = &Request_id ORDER BY fcr.request_date DESC;

              1 person found this helpful
              • 4. Re: users session

                Thanks Pavan.

                If you have snapshot for the above queries outputs,please share ,it will be a great help from your side.




                • 5. Re: users session
                  Pavan Kumar B

                  Get sid.


                  Get request_id.


                  Get username.


                  • 7. Re: users session

                    Thanks a lot pavan for your help.