Apps User Connection Details

This is going to be my first post... so I decided to bring a very useful SQL for apps DBA's.

This SQL shows details about users connected to the system (sid, responsibilty, form name, Oracle pid, Application pid and more..)

The parameters are Apps Username or Oracle Session Id (put % where you don't pass a parameter).

Very useful when you have a heavy session or when you have a DB lock and you want to know who is standing from behind (the applicative user)...

It contains 3 SQL's with unions:
1) 1st sql - OA Framework screens (login screen, iSupport etc...)
2) 2nd sql - Responsibility connection details (for Java Applet)
3) 3rd sql - Forms connection details


select usr.user_name "Apps Username"
,i.first_connect "First Connect Date"
,ses.sid
,ses.serial#
,ses.module
,v.spid "Oracle Server Process"
,ses.process "Application Server Process"
,rsp.responsibility_name "Responsibility Name"
,null "Responsibility Start Time"
,fuc.function_name "Function Name"
,i.function_type "Function Type"
,i.last_connect "Function Start Time"
from icx_sessions i
,fnd_logins l
,fnd_appl_sessions a
,fnd_user usr
,fnd_responsibility_tl rsp
,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 fnd_login_responsibilities t1
where t1.login_id = l.login_id)
and rsp.language(+) = 'US'
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
union
select usr.user_name
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,rsp.responsibility_name
,r.start_time
,null
,null
,null form_start_time
from fnd_logins l
,fnd_login_responsibilities r
,fnd_user usr
,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 rsp.language(+) = 'US'
and r.audsid = ses.audsid
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
union
select usr.user_name
,l.start_time
,ses.sid
,ses.serial#
,ses.module
,v.spid
,ses.process
,null
,null
,frm.user_form_name
,ff.type
,f.start_time
from fnd_logins l
,fnd_login_resp_forms f
,fnd_user usr
,fnd_form_tl frm
,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 frm.language(+) = 'US'
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID';