作为IT工程师,以下是几种在Oracle数据库中检查进程是否被锁的方法:
SELECT
l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
o.object_type
FROM
v$locked_object l,
dba_objects o,
v$session s
WHERE
l.object_id = o.object_id
AND
l.session_id = s.sid
ORDER BY
sid, s.serial#;
SELECT
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.logon_time,
l.type,
l.lmode,
l.request,
l.block
FROM
v$session s,
v$lock l
WHERE
s.sid = l.sid
AND
s.username IS NOT NULL
ORDER BY
s.sid;
SELECT
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
FROM
v$session
WHERE
blocking_session IS NOT NULL
ORDER BY
blocking_session;
-- 查看阻塞其他会话的会话
SELECT * FROM dba_blockers;
-- 查看正在等待锁的会话
SELECT * FROM dba_waiters;
如果问题已经发生一段时间,可以检查AWR报告中的"Top 5 Timed Events"部分,查看是否有"enqueue"等待事件。
如果确认某个会话被锁定需要解锁,可以使用:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
将sid和serial#替换为查询结果中的实际值。