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'; |
9 comments:
thanks ...
nice SQL but not good for conc...
for conc use this :
select v.request_id,
s.sid,
s.serial#,
p.spid
from fnd_conc_requests_form_v v,
v$process p,
v$session s
where v.request_id like PUT_REQ_NUMBER
and v.oracle_process_id = p.spid(+)
and p.addr = s.paddr(+);
put req number and get the session info sid and pid
Hi,
Thanks for the SQL.
It is very useful!
Aviad
thanks for the SQL. I run it into my test box and there is a lot of user called "GUEST" returns. I sort of know this is just a dummy user for JTF to connect. Should I modify your SQL to ignore any GUEST user? I have a lot GUEST user in my test box. Over 287 and 3 individual user. Is that normal?
Hi,
I usually use this SQL to find specific sessions by Application user name or database session id, I don't use this query without filtering.
This is normal that you have a lot of GUEST rows returned, it's connected to the JDBC Pool that built when Apache Jserver startup (GUEST user is used for connection purpose)
Aviad
Here is my a little modified version of your scipt...formated and supposed to be run as non APPS user.
set show off
SET VER OFF
ALTER SESSION SET CURRENT_SCHEMA=APPS
;
PROMPT
PROMPT shows information about APPS user (no concurent sessions!!) ....
PROMPT
undefine apps_user;
accept apps_user char prompt 'Input starting letters of APPS username : ';
col sid_serial for a13;
col user_name for A10;
col module for a15;
col Responsibility for a15;
col function for a15;
col F_Type for a10;
col app_pid for a6;
col db_pid for a6;
break on USER_NAME on db_pid on app_pid on sid_serial
select * from (
select
usr.user_name user_name
,v.spid db_pid
,ses.process app_pid
,ses.sid||','||ses.serial# sid_serial
-- ,i.first_connect Connection
,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 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 usr.user_name like '&apps_user%'
union
select
usr.user_name
,v.spid
,ses.process
,ses.sid||','||ses.serial# sid_serial
-- ,l.start_time
,ses.module
,rsp.responsibility_name
,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 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
-- ,l.start_time
,ses.module
,null
,frm.user_form_name
,ff.type
,to_char(f.start_time,'dd.mm hh24:mi')
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 f.audsid = ses.audsid
and ff.form_id = frm.form_id
and usr.user_name like '&apps_user%'
)
order by user_name, db_pid, app_pid, sid_serial
;
ALTER SESSION SET CURRENT_SCHEMA=USERXXX
;
Thanks Damir
very nice formated
Regards,
Aviad
And here is enchansed query that shows and requests as well others session details.
Remark:
it use input parameter to reduce result on only named apps user
2) It should be run as apps user or use "set current_schema" part in front.
set show off
SET VER OFF
set head on;
set timi on;
undefine apps_user;
accept apps_user char prompt 'Input starting letters of APPS username (Enter for all...): ';
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
;
hi, this query does not work in the scenario:
log into the application
lauch forms
exit out of the forms session
but dont log out of the application
the query does not retrieve data
Hi,
I see this is a very old thread but please can I ask what each function_type in icx_sessions means (ie. FORM,REST,SERVLET,JSP)?
Thanks, Graham
Post a Comment