select a.sid,a.serial#,b.sql_id,b.piece,b.sql_text
from v$session a join v$sqltext b on a.sql_id=b.sql_id
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24
#kill -9 12345(即第3步查询出的spid)
c:>orakill orcl 12345
1)执行下面SQL,先查看哪些表被锁住了:
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;
2)查看引起锁住的会话
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;
这里会列出SID
3) 查出SID和SERIAL#:
查V$SESSION视图:
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID';
这一步将得到PADDR
4)查V$PROCESS视图:
SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
这一步得到SPID
5)杀掉进程
(1)在数据库中,杀掉ORACLE进程:
ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';
(2)如果在ORACLE中不能杀掉进程,我们只能到操作系统中,使用操作系统命令杀掉进程
KILL -9 “刚才查出的SPID”
在WINDOWS平台,可以是那个orakill