查看被锁表
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;
查看链接的进程
SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION;
查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode
这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。
SELECT S.SID,
S.SERIAL#,
S.USERNAME,
S.SCHEMANAME,
S.OSUSER,
S.PROCESS,
S.MACHINE,
S.TERMINAL,
S.LOGON_TIME,
L.TYPE
FROM V$SESSION S, V$LOCK L
WHERE S.SID = L.SID
AND S.USERNAME IS NOT NULL
ORDER BY SID;
杀掉进程 sid,serial#
ALTER SYSTEM KILL SESSION'210,11562';