SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM v$locked_object l,
all_objects o,
v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid,
s.serial#;
-- 上面是查,下面是杀死
--alter system kill session 'sid,serial#';
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM v$locked_object l,
all_objects o,
v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid,
s.serial#;
-- 上面是查,下面是杀死
--alter system kill session 'sid,serial#';
例如:alter system kill session '1018,5045';
--------------------------------------------------------------------------------------------------------------------
网上收集,查询Oracle对象lock信息,方便解锁
SELECT /*+ rule */
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
FROM v$session s, v$lock l, dba_objects o
WHERE l.SID = s.SID AND l.id1 = o.object_id(+) AND s.username IS NOT NULL