Oracle Kill Long Running Sessions Job

Hi,

Sometimes you need killing some long running sessions that coming from specific machines and specific Queries.

 

 

If you want to kill some long running sessions that coming from specific machines and specific Queries, then you can create dbms Oracle job for this.

 

Before creating Oracle dbms job, firstly we need to create procedure which will perform this job, then create dbms_scheduler job and use this procedure to kill related sessions.

 

 

I will keep session informations in the kill_sessions_info table, so I have create it like following.

create table sys.kill_sessions_info(killId number,username varchar2(100),stext varchar2(100),stime date,duration date)

 

Then create Procedure like following. This procedure will kill sessions that coming from ‘MEHMETSALIH\DEVECI’ machine and running more than 1200 second and sql_text not like ‘%SALES%’. You can change these conditions according to your needs.

  CREATE OR REPLACE procedure SYS.kill_long_sessions as
BEGIN

FOR i IN (select s.sid||','||s.serial# killId,s.username username,ss.sql_fulltext stext,s.logon_time stime,s.last_call_et durat from 
v$session s, v$sql ss where s.machine='MEHMETSALIH\DEVECI' and s.last_call_et>1200 and
s.sql_id=ss.sql_id and ss.sql_fulltext not like '%SALES%' )

LOOP
declare
l_error VARCHAR2(2000):='';
begin
insert into sys.kill_sessions_info values(i.killId,i.username,i.stext,i.stime,i.durat);
commit;
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||i.killId||''' IMMEDIATE';
EXCEPTION
WHEN OTHERS THEN
l_error:=SQLERRM;

end;
END LOOP;

COMMIT;

END;
/

 

After creating procedure, you can create DBMS_SCHEDULER job like following.

 

 

 

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.KILL_LONG_SESSION'
,start_date => TO_TIMESTAMP_TZ('2018/12/06 16:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'
,end_date => TO_TIMESTAMP_TZ('2090/08/18 00:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'SYS.KILL_LONG_SESSIONS'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYS.KILL_LONG_SESSION');
END;
/

 

 

you can change interval and start time according to your needs.

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *