Oracle批量杀会话

Oracle查询会话，Oracle批量杀会话，Oracle根据SQL_ID杀会话，Oracle根据SQL_ID杀会话杀进程，按SQL_ID批量杀会话

set linesize 500
set pagesize 500
col sid format 9999
col spid for a5
col serial# format 999999
col event format a23
col p123 format a15
col wait_time format 999
col sql_id for a15
col username format a12
col machine format a12
col program format a12
alter session set cursor_sharing=force;
select /*XJ LEADING(S) FIRST_ROWS */ s.sid,s.serial#,p.spid,s.username,s.machine,s.program,s.event,S.P1 || '/'||S.P2||'/'||S.P3 P123,S.wait_time,NVL(SQL_ID,S.PREV_SQL_ID) SQL_ID from v$session s,v$process p where s.username is not null and s.paddr=p.addr and s.status='ACTIVE' and s.sid<>(select sid from v$mystat where rownum=1) order by p.pid desc;


set linesize 1000 pagesize 1000
select 'kill -9 ' || p.spid
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and sql_id = '31g8jw692q4gz'
 order by p.pid desc;
 
 
set linesize 1000 pagesize 1000
select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
       ''' immediate;'
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and sql_id = '31g8jw692q4gz';

---Oracle批量结束会话、批量解锁会话
select 'kill -9 ' || p.spid
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and sql_id = '31g8jw692q4gz'
 order by p.pid desc;

select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
       ''' immediate;'
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and sql_id = '31g8jw692q4gz';

select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
       ''' immediate;'
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and event = 'enq: TX - row lock contention'
   and s.username = 'MJIS';

select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
       ''' immediate;'
  from v$session s, v$process p
 where s.username = 'PHIS'
   and s.paddr = p.addr
   and s.sid <> (select sid from v$mystat where rownum = 1);