檢視鎖沖突的sid和serial#。
col object_name for a30
col machine for a20
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid;
object_name machine sid serial# 1 A test64 145 21218
2 A test64 138 5717 根據sid查該sql語句,和session狀态等。 SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$sqltext c
WHERE a.sid = 145 and a.sql_address=c.address(+)
ORDER BY c.piece
USERNAME MACHINE PROGRAM SID SERIAL# STATUS PIECE SQL_TEXT
1 SCOTT test64 [email protected] (TNS V1-V3) 145 21218 ACTIVE 0 delete from a
USERNAME MACHINE PROGRAM SID SERIAL# STATUS PIECE SQL_TEXT
1 SCOTT test64 [email protected] (TNS V1-V3) 138 5717 INACTIVE
測試時發現,status為inactive的正是需要kill掉的程序。因為他沒有commit或rollback解除鎖。
殺
alter system kill session 'sid,serial#';
alter system kill session '138,5717'; 執行後,138,5717的程序就被kill掉,資料進行了rollback。
===========================================================================
查出自己的sid
檢視目前session sid
SELECT sid, serial#
FROM v$session
WHERE audsid = sys_Context('USERENV', 'SESSIONID');
或者
select sys_context('userenv', 'sid') from dual