查询正在锁的表进程:
SELECT /*+ rule */
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;
锁表正在运行的语句:
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.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
Select c.sid,c.serial#,d.name,b.object_name,c.username,c.program,c.osuser
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id and a.inst_id = c.inst_id(+) and a.session_id = c.sid(+)
and c.command = d.action;
杀掉会话:alter system kill session '2095,4745';
当进程杀不掉得时候,需要在服务器级杀掉进程:
select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED';
select b.spid,a.osuser,b.program from v$session a,v$process b where a.paddr=b.addr and a.sid=1335;
kill -9
3149