oracle锁表查询

SELECT SESS.SID,  
SESS.SERIAL#,  
LO.ORACLE_USERNAME,  
LO.OS_USER_NAME,  
AO.OBJECT_NAME 被锁对象名, 
LO.LOCKED_MODE 锁模式, 
sess.LOCKWAIT,
sess.LOGON_TIME 登录数据库时间,
'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ','||SESS.SERIAL#||'''' FREESQL
FROM V$LOCKED_OBJECT LO,  DBA_OBJECTS AO,  V$SESSION SESS 
WHERE AO.OBJECT_ID = LO.OBJECT_ID 
AND LO.SESSION_ID = SESS.SID 
ORDER BY sid, sess.serial#;

-- 查锁表情况
select * from v$locked_object t;

-- 查哪些表被锁
select b.OWNER, b.OBJECT_NAME, a.SESSION_ID, a.LOCKED_MODE from v$locked_object a, dba_objects b where a.OBJECT_ID = b.OBJECT_ID;
-- 查锁表session
select b.USERNAME, b.SID, b.USER#, b.LOGON_TIME from v$locked_object a, v$session b where a.SESSION_ID = b.SID order by b.LOGON_TIME;
-- 查锁表Sql
select b.USERNAME, b.USER#, b.SID, b.LOCKWAIT, c.* from v$locked_object a, v$session b, v$sql c 
where a.SESSION_ID = b.SID and b.SQL_ID = c.SQL_ID order by b.LOGON_TIME;

ALTER SYSTEM KILL SESSION '863,54919';
ALTER SYSTEM KILL SESSION '1436,8653';

-- 推荐
select a.object_name,b.session_id,c.serial#,c.program,c.username,c.command,c.machine,c.lockwait 
from all_objects a,v$locked_object b,v$session c where a.object_id=b.object_id and c.sid=b.session_id;