kill某個等待事件對應的spid:
set linesize 260 pagesize 10000
select 'kill -9 ' || a.spid
from v$process a, v$session b
where a.addr = b.paddr
and a.background is null
and b.type = 'USER'
and b.event like '%' || '&eventname' || '%'
and b.status = 'ACTIVE';
對應的alter system kill session的文法:
set linesize 260 pagesize 1000
col machine for a50
col kill_session for a60;
select machine,
'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
status
from v$session
where type='USER' and event like '%event_name%' and status = 'ACTIVE';
kill某個sql_id對應的spid:
set linesize 260 pagesize 10000
select 'kill -9 ' || a.spid
from v$process a, v$session b
where a.addr = b.paddr
and a.background is null
and b.type = 'USER'
and b.sql_id = '&sql_id'
and b.status = 'ACTIVE';
set linesize 260 pagesize 10000
col machine for a60
select machine,
'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;',
status
from v$session
where sql_id = '&sql_id' and type='USER' and status='ACTIVE';
被kill會話的類型:
set linesize 260 pagesize 10000
select b.osuser,b.machine,b.program,b.sql_id,b.PREV_SQL_ID,a.spid,to_char(LAST_CALL_ET) as seconds,b.BLOCKING_SESSION,b.BLOCKING_INSTANCE
from v$process a, v$session b
where a.addr = b.paddr
and a.inst_id=b.inst_id
and a.background is null
and b.type = 'USER'
and b.event='&event_name'
and b.status = 'ACTIVE';
blocking會話類型和kill blocking會話:
set linesize 260 pagesize 10000
col machine for a50
col kill_session for a60
SELECT
blocking_instance,
blocking_session,
BLOCKING_SESSION_STATUS,
FINAL_BLOCKING_INSTANCE,
FINAL_BLOCKING_SESSION,
COUNT(*)
FROM
v$session
WHERE
upper(event) LIKE '%&cursor%'
GROUP BY
blocking_instance,
blocking_session,
BLOCKING_SESSION_STATUS,
FINAL_BLOCKING_INSTANCE,
FINAL_BLOCKING_SESSION
order by blocking_instance,count(*);
kill blocking會話:
select
inst_id,
machine,
'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
status
from gv$session a
where a.type='USER' and (a.inst_id,a.sid) in
(
select
BLOCKING_INSTANCE,
BLOCKING_SESSION
from v$session
where upper(event) like '%&cursor%'
)
order by inst_id;
所有含有關鍵字“LOCAL=NO”的程序是Oracle資料庫中遠端連接配接程序的共同特點,是以通過以下指令可以kill掉所有的程序
ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9