6 Replies Latest reply: May 24, 2013 11:14 AM by Hussein Sawwan-Oracle RSS

    How To Identify the User for a Forms Session

    1008771
      Hi.

      We are using EBS 11.5.10.2. I am just trying to identify the user from a forms process on our (RHEL 5) server. The forms process is not spinning - I am just trying out how to do this ....
      Following 11i: How To Identify the User for a Forms Session [ID 185762.1]:

      The forms process is 11708:
      ps -ef | grep 11708
      **** 2437 2394 0 10:51 pts/1 00:00:00 grep 11708
      **** 11708 11706 0 07:05 ? 00:00:00 f60webmx webfile=5,0,****

      I then run the query and supply the PID
      column "User Name" format a10
      column "ClPID" format a8

      column "User Name" format a10
      column "ClPID" format a8

      select
      d.user_name "User Name",
      b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "ClPID",
      to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"
      from
      fnd_logins a, v$session b, v$process c, fnd_user d
      where
      b.paddr = c.addr
      and a.pid=c.pid
      and a.spid = b.process
      and d.user_id = a.user_id
      and (d.user_name = 'USER_NAME' OR 1=1)
      and a.SPID = &PID
      /
      SQL> /
      Enter value for pid: 11708
      old 13: and a.SPID = &PID
      new 13: and a.SPID = 11708

      no rows selected


      Why are no rows selected?

      I am connecting as APPS user

      Thanks and regards,
      Sid.
        • 1. Re: How To Identify the User for a Forms Session
          DBA_EBiz_EBS
          Try using the following two sqls. First to capture your sid from the operating system process id of the form and then second to get the actual application username:

          1)
          select vs.sid,
          to_char(vs.serial#) "SER#",
          substr(vs.machine,instr(vs.machine, '\', -1)+1 ) HOSTNAME,
          vs.osuser,
          vs.process,
          vp.spid,
          vs.username,
          vs.status,
          va.name command,
          vw.event "WAITING FOR",
          vi.block_gets+vi.consistent_gets "LGCL RDS",
          to_char(vs.logon_time,'mm-dd hh24:mi') "LOGIN TIME",
          decode(vs.program,null,vs.module,substr(substr(vs.program,instr(vs.program, '\', -1)+1 ),1,20)) "PROGRAM"
          -- substr(substr(vs.program,instr(vs.program, '\', -1)+1 ),1,20) "PROGRAM"
          from v$session vs
          ,v$session_wait vw
          ,v$sess_io vi
          ,v$process vp
          ,sys.audit_actions va
          where vs.sid = vw.sid
          and vs.sid = vi.sid
          and vs.paddr = vp.addr
          and vw.sid = vi.sid
          and va.action = vs.command
          and vs.process = '&&Client_Process'
          order by vs.sid
          ;


          2)

          SELECT
          USR.USER_NAME,
          S.SID,
          S.SERIAL# "SER#",
          L.PROCESS_SPID "OS PID",
          S.PROCESS,
          -- S.OSUSER,
          RSP.RESPONSIBILITY_NAME RESP_NAME,
          FRM.USER_FORM_NAME,
          to_char(NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)), 'mm/dd/yy hh24:mi') "Start Time",
          -- VA.NAME "COMMAND",
          S.STATUS,
          -- W.EVENT "WAITING FOR"
          S.MACHINE
          FROM APPS.FND_RESPONSIBILITY_VL RSP,
          APPS.FND_FORM_VL FRM,
          APPS.FND_USER USR,
          APPS.FND_LOGINS L,
          APPS.FND_LOGIN_RESPONSIBILITIES R,
          APPS.FND_LOGIN_RESP_FORMS F,
          SYS.V_$SESSION S ,
          SYS.V_$SESSION_WAIT W,
          SYS.AUDIT_ACTIONS VA
          WHERE R.LOGIN_ID = F.LOGIN_ID
          AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
          AND L.LOGIN_ID = R.LOGIN_ID
          -- AND L.END_TIME IS NULL
          AND R.END_TIME IS NULL
          AND F.END_TIME IS NULL
          AND L.USER_ID = USR.USER_ID
          AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
          AND R.RESP_APPL_ID = RSP.APPLICATION_ID
          AND F.FORM_ID = FRM.FORM_ID
          AND F.FORM_APPL_ID = FRM.APPLICATION_ID
          AND F.AUDSID = S.AUDSID
          AND S.SID = W.SID
          AND VA.ACTION = S.COMMAND
          and S.SID = &SID
          order by USR.USER_NAME, "Start Time"


          Hope this helps :-)

          Regards,
          • 2. Re: How To Identify the User for a Forms Session
            Srini Chavali-Oracle
            One other easy option is to use Oracle Applications Manager

            Oracle Applications Manager > Forms Monitoring

            HTH
            Srini
            • 3. Re: How To Identify the User for a Forms Session
              Hussein Sawwan-Oracle
              Please run the query in (Process F60webmx with 1OO% CPU : How To Find Session ID From Unix PID ? [ID 1359850.1]).

              Thanks,
              Hussein
              • 4. Re: How To Identify the User for a Forms Session
                Hussein Sawwan-Oracle
                Srini Chavali wrote:
                One other easy option is to use Oracle Applications Manager

                Oracle Applications Manager > Forms Monitoring

                HTH
                Srini
                To use forms monitoring, please refer to (How To Enable Function Forms Monitoring In OAM? [ID 839093.1]).

                Thanks,
                Hussein
                • 5. Re: How To Identify the User for a Forms Session
                  1008771
                  Thanks - the first select returned rows but the second did not - I assume that if no rows are returned for any of these forms processes then that user must have already singed out from the application??

                  SID.
                  • 6. Re: How To Identify the User for a Forms Session
                    Hussein Sawwan-Oracle
                    1005768 wrote:
                    Thanks - the first select returned rows but the second did not - I assume that if no rows are returned for any of these forms processes then that user must have already singed out from the application??

                    SID.
                    If the user has signed out from the application then the forms process will no longer exist and you won't have any PID to pass it to the query then.

                    Thanks,
                    Hussein