5 Replies Latest reply on Apr 7, 2015 2:12 PM by ;) ApPsMasTi ;)

    Concurrent Logins SQL

    user599292

      Is there anyway via SQL to get a count of how many users are currently logged into your EBS system? We need to do some checking for licensing purposes, so the plan was to run the same query once every 30 minutes for a week during peak working hours. We are operating R12 of EBS. I seem to remember a similar query doing the rounds on R11 but I cant find similar for R12.

        • 2. Re: Concurrent Logins SQL
          ;) ApPsMasTi ;)

          please see

           

           

          select u.user_name, r.responsibility_name, f.user_function_name, s.function_type,

          to_char(s.first_connect, 'DD-MON HH24:MI:SS') "First Connect",

          to_char(s.last_connect, 'DD-MON HH24:MI:SS') "Last Connect", n.node_name

          from icx_sessions s, fnd_user u, fnd_responsibility_vl r, fnd_form_functions_vl f, fnd_nodes n

          where u.user_id(+) = s.user_id

          AND f.function_id(+) = s.function_id

          AND r.responsibility_id(+) = s.responsibility_id

          AND n.node_id(+) = s.node_id

          AND s.disabled_flag != 'Y'

          and s.pseudo_flag = 'N'

          and (s.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,s.limit_time, 0,s.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate

          and s.counter < s.limit_connects

          /

           

           

          Reference:

          Note 1301645.1 How to Determine How Many Users (outside of concurrent manager jobs -- forms and web users) are Logged into E-Business System at any One Time and the High Water Mark of Users on a Particular Day?

           

          APpsMasTi
          sharing is Caring

          • 3. Re: Concurrent Logins SQL
            Kj - Kiran Jadhav

            You can check below query as well which will provide more information on session details :

             

            SET show off

            SET VER OFF

            SET head on;

            SET timi on;

             

             

            UNDEFINE apps_user;

            ACCEPT apps_user char prompt 'Enter specific USERNAME  or else just ENTER  for all users ...: ';

             

             

            SET linesize 300;

            SET pagesize 200;

             

             

            COL sid_serial for a13;

            COL user_name for A10;

            COL module for a22;

            COL Responsibility for a29;

            COL function for a30;

            COL F_Type for a10;

            COL ap_pid for a6;

            COL db_pid for a6;

             

             

            BREAK on USER_NAME on db_pid on ap_pid on sid_serial

             

             

            SELECT   *

                FROM (SELECT usr.user_name user_name, v.spid db_pid, ses.process ap_pid,

                             ses.SID || ',' || ses.serial# sid_serial, ses.module,

                             rsp.responsibility_name responsibility,

                             fuc.function_name FUNCTION, i.function_type f_type,

                             TO_CHAR (i.last_connect, 'dd.mm hh24:mi') f_start

                        FROM apps.icx_sessions i,

                             apps.fnd_logins l,

                             apps.fnd_appl_sessions a,

                             apps.fnd_user usr,

                             apps.fnd_responsibility_tl rsp,

                             apps.fnd_form_functions fuc,

                             gv$process v,

                             gv$session ses

                       WHERE i.disabled_flag = 'N'

                         AND i.login_id = l.login_id

                         AND l.end_time IS NULL

                         AND i.user_id = usr.user_id

                         AND l.login_id = a.login_id

                         AND a.audsid = ses.audsid

                         AND l.pid = v.pid

                         AND l.serial# = v.serial#

                         AND i.responsibility_application_id = rsp.application_id(+)

                         AND i.responsibility_id = rsp.responsibility_id(+)

                         AND i.function_id = fuc.function_id(+)

                         AND i.responsibility_id NOT IN (

                                                   SELECT t1.responsibility_id

                                                     FROM apps.fnd_login_responsibilities t1

                                                    WHERE t1.login_id = l.login_id)

                         AND usr.user_name LIKE '&apps_user%'

                      UNION

                      SELECT usr.user_name, v.spid, ses.process,

                             ses.SID || ',' || ses.serial# sid_serial, ses.module,

                             rsp.responsibility_name, NULL, NULL, NULL form_start_time

                        FROM apps.fnd_logins l,

                             apps.fnd_login_responsibilities r,

                             apps.fnd_user usr,

                             apps.fnd_responsibility_tl rsp,

                             gv$process v,

                             gv$session ses

                       WHERE l.end_time IS NULL

                         AND l.user_id = usr.user_id

                         AND l.pid = v.pid

                         AND l.serial# = v.serial#

                         AND v.addr = ses.paddr

                         AND l.login_id = r.login_id(+)

                         AND r.end_time IS NULL

                         AND r.responsibility_id = rsp.responsibility_id(+)

                         AND r.resp_appl_id = rsp.application_id(+)

                         AND r.audsid = ses.audsid

                         AND usr.user_name LIKE '&apps_user%'

                      UNION

                      SELECT usr.user_name, v.spid, ses.process,

                             ses.SID || ',' || ses.serial# sid_serial, ses.module, NULL,

                             frm.user_form_name, ff.TYPE,

                             TO_CHAR (f.start_time, 'dd.mm hh24:mi')

                        FROM apps.fnd_logins l,

                             apps.fnd_login_resp_forms f,

                             apps.fnd_user usr,

                             apps.fnd_form_tl frm,

                             apps.fnd_form_functions ff,

                             gv$process v,

                             gv$session ses

                       WHERE l.end_time IS NULL

                         AND l.user_id = usr.user_id

                         AND l.pid = v.pid

                         AND l.serial# = v.serial#

                         AND v.addr = ses.paddr

                         AND l.login_id = f.login_id(+)

                         AND f.end_time IS NULL

                         AND f.form_id = frm.form_id(+)

                         AND f.form_appl_id = frm.application_id(+)

                         AND f.audsid = ses.audsid

                         AND ff.form_id = frm.form_id

                         AND usr.user_name LIKE '&apps_user%'

                      UNION

                      SELECT fu.user_name, cr.oracle_process_id, cr.os_process_id,

                             vs.SID || ',' || vs.serial# sid_serial,

                             (SELECT cp.concurrent_program_name

                                FROM apps.fnd_concurrent_programs cp

                               WHERE cp.application_id = cr.program_application_id

                                 AND cp.concurrent_program_id = cr.concurrent_program_id),

                             (SELECT responsibility_name

                                FROM apps.fnd_responsibility_vl

                               WHERE responsibility_id = cr.responsibility_id

                                 AND application_id = cr.responsibility_application_id),

                             (SELECT execution_file_name

                                FROM apps.fnd_executables fe,

                                     apps.fnd_concurrent_programs fcp

                               WHERE fcp.concurrent_program_id = cr.concurrent_program_id

                                 AND fcp.application_id = cr.program_application_id

                                 AND fe.executable_id = fcp.executable_id

                                 AND fe.application_id = fcp.executable_application_id),

                             'REQUEST',

                             TO_CHAR (NVL (cr.actual_start_date, cr.requested_start_date),

                                      'DD.MM HH24:MI'

                                     ) start_time

                        FROM apps.fnd_concurrent_requests cr,

                             v$process vp,

                             v$session vs,

                             apps.fnd_user fu

                       WHERE cr.phase_code <> 'I'

                         AND (cr.phase_code < 'C' OR cr.phase_code > 'C')

            /*

            (cr.phase_code < 'I' OR cr.phase_code > 'I')

            AND (cr.phase_code < 'C' OR cr.phase_code > 'C')

            */

                         AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')

                         AND cr.oracle_process_id = vp.spid(+)

                         AND cr.oracle_session_id = vs.audsid(+)

                         AND fu.user_id = cr.requested_by

                         AND vs.SID IS NOT NULL

                         AND fu.user_name LIKE '&apps_user%')

            ORDER BY user_name, db_pid, ap_pid, sid_serial;

             

             

            -Kj

            • 4. Re: Concurrent Logins SQL
              user599292

              which fields in the above  query show "how many users are currently logged in", and the "high watermakr of users on a particular day".

              • 5. Re: Concurrent Logins SQL
                ;) ApPsMasTi ;)

                first column USERNAME gives user currently logged in users

                 

                for high water mark

                 

                please see

                License high water mark in alert log (Doc ID 1034330.6)

                 

                 

                ApPsMaStI

                sharing is Caring