SQL> select nvl(S.USERNAME,'Internal') username, s.process EBS_PID, l.ctime, s.machine,
L.SID||','||S.SERIAL# Kill,
s.module,
U1.NAME||'.'||substr(T1.NAME,1,20) table_name,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
s.program,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND' and U1.name != 'SYS'
order by l.ctime desc,2,6 ;
EBS_PID is the process ID on Apps node. The query may return a long list of rows and so becomes not very useful.
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND' and U1.name != 'SYS'
order by l.ctime desc,2,6 ;
EBS_PID is the process ID on Apps node. The query may return a long list of rows and so becomes not very useful.
OS command "top" can be used to see if any process runs for too long or uses high CPU/memory. Before kill it on OS level to release the block, Linux line can be used to verify its start time:
$ ps -eo pid,lstart,cmd | grep EBS_PID
For example, a Form session has run for days:
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') status, sid sess_ID, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
Oracle Application Manager (OAM) also lists forms sessions with user name (OAM -> Dashboard -> Site Map -> Monitoring -> Forms Sessions). But if the list is very long, it is very difficult to sort it out. Note if Forms sessions are not populated in OAM, set the profile option "Sign-On:Audit Level" to "FORM" (see Doc ID 1270277.1).
NOTES:
For example, a Form session has run for days:
$ ps -eo pid,lstart,cmd | grep 16303
9557 Thu Oct 15 09:40:59 201x grep --color=auto 16303
16303 Mon Oct 12 09:57:02 201x frmweb server webfile=HTTP-0,0,1,default
A simple query can be used to find R12 Forms' user name (in database 12c and 11g). If the query returns nothing, it does not connect to database any more and it is safe to kill the run-away process on OS level.
SQL> select sid, SERIAL#, process EBS_PID, machine, action, module, CLIENT_IDENTIFIER
FROM v$session where process = '16303' ; -- 16303 is the EBS_PID
Below query can be used to verify if there is a true blocking session or not:
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') status, sid sess_ID, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
Oracle Application Manager (OAM) also lists forms sessions with user name (OAM -> Dashboard -> Site Map -> Monitoring -> Forms Sessions). But if the list is very long, it is very difficult to sort it out. Note if Forms sessions are not populated in OAM, set the profile option "Sign-On:Audit Level" to "FORM" (see Doc ID 1270277.1).
NOTES:
Doc ID 1610624.1 gives a SQL to find Forms user name in 11i :
SELECT (SELECT substr(fu.user_name ,1 ,20)
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id)
user_name, to_char(fl.start_time,'DD-MON-YYYY HH24:MI') login_start_time,
substr(fl.process_spid ,1 ,6) spid,
to_char(fl.pid) pid, vs.machine host,
substr(vs.process, 1 ,8) host_pid,
substr(to_char(rf.audsid) ,1 ,6) audsid,
to_char(vs.sid) sid,
substr(to_char(vs.serial#) ,1 ,8) serial#,
substr(vs.module || ' - ' ||
(SELECT substr(ft.user_form_name, 1 ,40)
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id = rf.form_id
AND ft.LANGUAGE = 'US')
,1 ,40) form
FROM apps.fnd_logins fl, gv$process vp,
apps.fnd_login_resp_forms rf, gv$session vs
WHERE fl.end_time IS NULL
AND fl.start_time > SYSDATE - 31
-- AND fl.login_type = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid = vp.pid
AND fl.login_id = rf.login_id
AND rf.end_time IS NULL
AND rf.audsid = vs.audsid
AND vs.process = '5629' -- enter process ID (EBS_PID) from above
ORDER BY user_name, login_start_time, sid;
SELECT (SELECT substr(fu.user_name ,1 ,20)
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id)
user_name, to_char(fl.start_time,'DD-MON-YYYY HH24:MI') login_start_time,
substr(fl.process_spid ,1 ,6) spid,
to_char(fl.pid) pid, vs.machine host,
substr(vs.process, 1 ,8) host_pid,
substr(to_char(rf.audsid) ,1 ,6) audsid,
to_char(vs.sid) sid,
substr(to_char(vs.serial#) ,1 ,8) serial#,
substr(vs.module || ' - ' ||
(SELECT substr(ft.user_form_name, 1 ,40)
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id = rf.form_id
AND ft.LANGUAGE = 'US')
,1 ,40) form
FROM apps.fnd_logins fl, gv$process vp,
apps.fnd_login_resp_forms rf, gv$session vs
WHERE fl.end_time IS NULL
AND fl.start_time > SYSDATE - 31
-- AND fl.login_type = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid = vp.pid
AND fl.login_id = rf.login_id
AND rf.end_time IS NULL
AND rf.audsid = vs.audsid
AND vs.process = '5629' -- enter process ID (EBS_PID) from above
ORDER BY user_name, login_start_time, sid;
