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

    users session

    ora_learner36

      Dear All,

       

      Greetings!!

       

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

       

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

       

       

      OS:RHEL 6.1

      EBS:12.1.3

      DB:11.2.0.3

       

      regards.

        • 1. Re: users session
          Pavan Kumar B

          Hi,

          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
            ora_learner36

            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

              Hi..

               

              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

               

              SELECT

                     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"

                FROM

                     fnd_user                    fu,

                     fnd_responsibility_tl       frt,

                     fnd_concurrent_requests     fcr,

                     fnd_concurrent_programs_tl  fcpt,

                     fnd_lookup_values           flv1,

                     fnd_lookup_values           flv2

              WHERE

                     1=1

                 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
                ora_learner36

                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_sid_value.jpg

                  Get request_id.

                  Get_request_id.jpg

                  Get username.

                  Get_usename_from_request_id.jpg

                  • 7. Re: users session
                    ora_learner36

                    Thanks a lot pavan for your help.