多集群查询锁表SQL
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b where b.object_id = a.object_id;
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
alter system kill session '408,62752'; -- 杀session,对应sid serial#
--查询进程
SELECT object_name,l.OS_USER_NAME, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid;
--解决锁表
declare cursor mycur is
select b.sid,b.serial#
from v$locked_object a,v$session b
where a.session_id = b.sid group by b.sid,b.serial#;
begin
for cur in mycur
loop
execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end loop;
end;
---查看造成锁表的语句
select sql_text from v$sql where hash_value in (
select sql_hash_value from v$session where sid in (select session_id from v$locked_object)
)
--查看那个用户那个进程照成死锁
select b.username,a.OS_USER_NAME,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
---多个节点服务器跟本地锁表
----查询锁表
SELECT O.OBJECT_NAME,
L.OS_USER_NAME,
S.MACHINE,
S.SID,
S.SERIAL#,
S.INST_ID,
S.PROGRAM,
S.TERMINAL,
S.OSUSER,
TO_CHAR(GS.SQL_FULLTEXT)
FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$SQL GS
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
AND GS.SQL_ID(+) = S.SQL_ID;
ALTER SYSTEM KILL SESSION '43,36774,@2';