Monday 22 January 2018

SQL Query to Find Lock Sessions in Oracle Apps R12



SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;

4 comments:

arif said...

Thanks and Regards. Oracle Apps R12 & Fusion Training Videos at affordable cost.
please check oracleappstechnical.com for details.

Anonymous said...

Thanks for Sharing This informative article. It would be helpful if you write a all.
Oracle Fusion HCM Online Training

shaik shah said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Oracle Fusion HCM Online Training

yamuna said...

Good article, thanks for sharing this information.
Oracle Fusion SCM Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *