EBS锁表问题排查

我不是huge

于 2021-10-25 09:40:49 发布

阅读量569

点赞数

文章标签： dba 数据库 database

本文链接：https://blog.csdn.net/fanfanfanyan/article/details/120946208

版权

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 $s e s s i o n s, 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 V $KaTeX parse error: Expected 'EOF', got '#' at position 226: \dots VSS.SERIAL#̲, VSS.AC\dots$ LOCKED_OBJECT LO, DBA_OBJECTS DOB, V $S E S S I O N V S S, 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;