EBS锁表问题排查

1.锁表查询基表sql
SELECT s.Username
,Decode(l.Type, ‘TM’, ‘TABLE LOCK’, ‘TX’, ‘ROW LOCK’, NULL) Lock_Level
,o.Owner
,o.Object_Name
,o.Object_Type
,s.Sid
,s.Serial#
,s.Terminal
,s.Machine
,s.Program
,s.Osuser
,s.LOGON_TIME
,‘ALTER System Kill Session ’ || ‘’’’ || s.Sid ||’,’|| s.Serial# || ‘’’’
FROM V$sessions,V$lock l
,Dba_Objects o
WHERE l.Sid = s.Sid
AND l.Id1 = o.Object_Id(+);
2.数据库多节点基表查询sql
SELECT l.INST_ID
,b.Owner
,b.Object_Name
,l.Session_Id
,l.Locked_Mode
FROM Gv$locked_Object l
,Dba_Objects b
WHERE b.Object_Id = l.Object_Id;’

3.PLSQL查询会话功能

工具按钮 会话查询

4.查询session对应用户脚本
SELECT a.Sid,a.Serial# FROM V$session a;

5.锁表锁住的是程序包PCK PKG查询sql
SELECT s.Username
,s.Sid
,s.Program
,d.Owner || ‘.’ || d.Name Object_Locked
,d.Type
,Decode(s.Process, NULL, s.Machine, s.Machine || ’ PID: ’ || To_Char(s.Process)) “MACHINE/PID”
,‘ALTER SYSTEM KILL SESSION ’ || ‘’’’ || s.Sid || ‘,’ || s.Serial# || ‘’’;’
FROM VKaTeX parse error: Expected 'EOF', got '#' at position 226: … VSS.SERIAL#̲, VSS.AC…LOCKED_OBJECT LO, DBA_OBJECTS DOB, V$SESSIONVSS,V$PROCESS VPS
WHERE LO.OBJECT_ID = DOB.OBJECT_ID
AND LO.SESSION_ID = VSS.SID
AND VSS.PADDR = VPS.ADDR
ORDER BY 2, 3, DOB.OBJECT_NAME;
7.查询此时EBS系统当前登录用户登录的界面 确认是否有多人登录同一个界面的情况
SELECT u.user_name,
p.full_name,
app.application_short_name,
fat.application_name,
fr.responsibility_key,
frt.responsibility_name,
fff.function_name,
fft.user_function_name,
icx.function_type,
icx.first_connect,
icx.last_connect
FROM icx_sessions icx,
fnd_user u,
per_people_f p,
fnd_application app,
fnd_application_tl fat,
fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_form_functions fff,
fnd_form_functions_tl fft
WHERE 1 = 1
AND u.user_id = icx.user_id
AND icx.responsibility_application_id = app.application_id
AND fat.application_id = icx.responsibility_application_id
AND fat.language = ‘ZHS’
AND fr.application_id = icx.responsibility_application_id
AND fr.responsibility_id = icx.responsibility_id
AND frt.language = ‘ZHS’
AND frt.application_id = icx.responsibility_application_id
AND frt.responsibility_id = icx.responsibility_id
AND fff.function_id = icx.function_id
AND fft.function_id = icx.function_id
AND fft.LANGUAGE = ‘ZHS’
AND icx.disabled_flag != ‘Y’
AND icx.pseudo_flag = ‘N’
AND (icx.last_connect +
decode(fnd_profile.value(‘ICX_SESSION_TIMEOUT’),
NULL,
icx.limit_time,
0,
icx.limit_time,
fnd_profile.value(‘ICX_SESSION_TIMEOUT’) / 60) / 24) >
SYSDATE
AND u.employee_id = p.person_id
AND p.effective_end_date >= SYSDATE
AND icx.counter < icx.limit_connects;