–查目前执行时间较长sql
select datediff(ss, last_recv_time, sysdate) ss ,
dbms_lob.substr(sf_get_session_sql(sess_id)), --抓取sql的全部内容
sql_id,
curr_sch,sess_id,trx_id,substr(clnt_ip, 8, 13)
from v$sessions
where state=‘ACTIVE’ order by 1 desc;

–找到阻塞和被阻塞会话id 和阻塞事务id
select a.sess_id, a.sql_text, a.state,a.curr_sch,b.* from v$sessions a, v$trxwait b
where a.TRX_ID = b.wait_for_id
union all
select a.sess_id, a.sql_text, a.state,a.curr_sch,b.* from v$sessions a, v$trxwait b
where a.trx_id = b.id;

–查询阻塞
SELECT
DS.CREATE_TIME “开始阻塞时间”,
SS.SESS_ID “占用锁的会话ID”,
SS.SQL_TEXT “占用锁的SQL”,
SS.CLNT_IP “占用锁的IP”,
L.TID “占用锁的事务ID”,
SS.STATE,
DS.SESS_ID “被阻塞的会话ID”,
DS.SQL_TEXT “被阻塞的SQL”,
DS.TRX_ID “被阻塞的事务ID”,
DS.STATE,
(CASE L.LTYPE WHEN ‘OBJECT’ THEN ‘对象锁’ WHEN ‘TID’ THEN ‘事务锁’ END CASE ) “被阻塞的锁类型”
FROM V$LOCK L
LEFT JOIN V$SESSIONS DS
ON DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON SS.TRX_ID=L.TID
WHERE L.BLOCKED =1;

–批量杀（阻塞源IDLE）
declare
v_cnt int;
begin
for rec in(select b.sess_id
from v$trxwait a
left join v$sessions b on a.wait_for_id = b.TRX_ID WHERE b.state = ‘IDLE’) loop print(‘call sp_close_session(’||rec.sess_id||’);’);
execute immediate ‘call sp_close_session(’||rec.sess_id||’);’;
end loop;
end;
/

–批量杀（阻塞源所有）
declare
v_cnt int;
begin
for rec in(select a.sess_id from v$sessions a, v$trxwait b where a.TRX_ID = b.wait_for_id)
loop print(‘call sp_close_session(’||rec.sess_id||’);’);
execute immediate ‘sp_cancel_session_operation (’’’||rec.SESS_ID||’’’)’;
execute immediate ‘call sp_close_session(’||rec.sess_id||’);’;
end loop;
end;
/

–批量kill 超过100s SQL
declare
v_cnt int;
begin
for rec in(select sess_id from v$sessions where state=‘ACTIVE’
and SQL_TEXT not like ‘%session%’
and datediff(ss, last_recv_time, sysdate) > 100 --删除超过100秒未完成的SQL
and (SQL_TEXT like ‘SELECT%’ or SQL_TEXT like ‘select%’) --比如杀select开头的sql语句，增加 小写的select
and curr_sch not LIKE ‘%SYS%’
and curr_sch <> ‘DPM_MON’
and curr_sch in (‘TSS’)
) loop print(‘call sp_close_session(’||rec.sess_id||’);’);
execute immediate ‘call sp_close_session(’||rec.sess_id||’);’;
end loop;
end;
/

–批量kill IDLE
begin
for a in (select sess_id from v$sessions where state=‘IDLE’ and CURR_SCH NOT LIKE ‘%SYS%’ and curr_sch <> ‘DPM_MON’
) loop
execute immediate ‘sp_cancel_session_operation (’’’||a.SESS_ID||’’’)’;
execute immediate ‘sp_close_session (’’’||a.SESS_ID||’’’)’;
end loop;
end;
/

–批量kill ACTIVE
begin
for a in (select sess_id from v$sessions where state=‘ACTIVE’ and CURR_SCH NOT LIKE ‘%SYS%’ and curr_sch <> ‘DPM_MON’
) loop
execute immediate ‘sp_cancel_session_operation (’’’||a.SESS_ID||’’’)’;
execute immediate ‘sp_close_session (’’’||a.SESS_ID||’’’)’;
end loop;
end;
/

–批量kill 所有
begin
for a in (select sess_id from v$sessions where CURR_SCH NOT LIKE ‘%SYS%’ and curr_sch <> ‘DPM_MON’
) loop
execute immediate ‘sp_cancel_session_operation (’’’||a.SESS_ID||’’’)’;
execute immediate ‘sp_close_session (’’’||a.SESS_ID||’’’)’;
end loop;
end;
/