Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Kill SpID of a waiting event:

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 The '%' || '&eventname' || The '%'
   and b.status = 'ACTIVE';
Copy the code

Alter system kill session syntax

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';
Copy the code

Kill spID corresponding to a sql_id:

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';
Copy the code

Alter system kill session syntax

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';
Copy the code

The type of the killed session:

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';
Copy the code

Blocking session type and Kill Blocking session:

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(*);
Copy the code

Kill Blocking session:

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;
Copy the code

All processes that contain the keyword “LOCAL=NO” are common features of remote processes in the Oracle database. Therefore, you can run the following command to kill all processes

ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill9 -Copy the code

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.

❤️ technical exchange can follow the public number: Lucifer think twice before you do ❤️