1.查看锁表情况；
SELECT /*+ RULE */ Ls.Osuser Os_User_Name, Ls.Username User_Name, Decode(Ls.TYPE, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type, o.Object_Name OBJECT, Decode(Ls.Lmode, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', NULL) Lock_Mode, o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2 FROM Sys.Dba_Objects o, (SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1, l.Id2 FROM V$session s, V$lock l WHERE s.Sid = l.Sid) Ls WHERE o.Object_Id = Ls.Id1 AND o.Owner <> 'SYS' ORDER BY o.Owner, o.Object_Name;
2.查看锁表、主机、SID/SERIAL
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, 'alter system kill session '''||l.session_id||','||s.serial#||''';' kill会话 from v$locked_object l, all_objects o, v$session swhere l.object_id = o.object_id and l.session_id = s.sid order by sid, s.serial#;
3.查看导至锁表的sql语句（汇总查询）；
select l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#, l.os_user_name, s.machine, s.terminal, a.sql_text, a.SQL_FULLTEXT, o.object_name from v$sqlarea a, v$session s, v$locked_object l,all_objects owhere l.object_id = o.object_id and l.session_id = s.sid and s.prev_sql_addr = a.addressorder by sid, s.serial#;
4.sid查看sql语句；
select b.sql_text from v$session a, v$sql bwhere a.sid = SID号 --session_id and a.SQL_ADDRESS = b.ADDRESS(+);
5.删除锁表的会话；
----3028:SID,15898:SERIAL#alter system kill session '3028,15898';