数据库锁大于600秒的session的抓取

最新推荐文章于 2023-07-18 14:42:59 发布

iteye_18365

最新推荐文章于 2023-07-18 14:42:59 发布

阅读量102

点赞数

文章标签：数据库

本文介绍了一个用于监控Oracle数据库中长时间锁定情况的过程。该过程通过查询v$lock、v$session等视图，筛选出锁定时间超过10分钟的会话，并将相关信息记录到moni_lock_block表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure moni_lock is
begin
insert /* +append */
into moni_lock_block
select seq_mlb.nextval, mkb.*
from (select /*+rule*/
do.OWNER || '.' || do.OBJECT_NAME,
vs.LAST_CALL_ET,
vs.USERNAME,
vs.OSUSER,
vs.MACHINE,
vs.PROGRAM,
vs.SID,
'(' || vs.SID || ',' || vs.SERIAL# || ')',
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'),
sysdate,
vsq.SQL_TEXT
from v$lock a,
v$session vs,
v$locked_object vl,
dba_objects do,
v$sqlarea vsq
where exists (select 1
from v$lock b
where a.ID1 = b.ID1
and a.ID2 = b.ID2
and a.SID <> b.SID)
and vs.SQL_ADDRESS = vsq.ADDRESS(+)
and a.SID = vs.SID
and a.SID = vl.SESSION_ID
and vl.OBJECT_ID = do.OBJECT_ID
and a.BLOCK = 1
and vs.LAST_CALL_ET > 600
order by SID) mkb
where mkb.sid is not null;
commit;
end moni_lock;