Schlagwortarchiv für: Kill session

In diesem kurzen Blog Post zeigen wir, wie man mittels einer einfachen Prozedur „long running Sessions“ killen kann.

Der Nachteil dieser Lösung ist, dass die komplette Session gekillt wird.

Damit diese Lösung aber so generisch wie möglich ist, werden nur jene Spalten selektiert die ausreichen, um eine Identifikation des USER/SQL sicherzustellen.

Es ist natürlich möglich, auch weitere Spalten hinzuzugeben und die Tabellen Definition dafür anzupassen.

Diese Killer Prozedur eignet sich für Oracle Standard Edition Datenbanken, wo kein Resource Manager funktioniert.

Ab Version 18c hat Oracle eine „CANCEL SQL“ eingeführt, siehe https://oracle-base.com/articles/18c/alter-system-cancel-sql-18c

Wenn Sie eine höhere Version verwenden, kann diese Prozedur einfach umgeschrieben werden, in dem Sie folgendes Statement …

stmt := 'alter system kill session '''|| d.sid ||','||d.serial#||',@'||d.inst_id||''' immediate';

auf…

stmt := 'alter system cancel sql '''|| d.sid ||','||d.serial#||',@'||d.inst_id||''' ';

…ändern.

PLSQL Code

drop table sys.dbmonitor_tab purge ;
create table sys.dbmonitor_tab (datum timestamp, inst_id number, sid number, serial# number, username varchar2(50), sql_id varchar2(30), sql_text varchar2(50)) tablespace users ;

create or replace procedure sys.dbmonitor(timeout IN number) IS 
-- query everything what is NOT a background process, bg processes dont have a username :)
cursor get_data is select s.inst_id, s.sid, s.serial#, s.username, s.sql_id, substr(sql.sql_text,1,50) as txt from gv$session s, gv$sqlarea sql where s.sql_id=sql.sql_id and s.inst_id=sql.inst_id and 
status='ACTIVE' and round(last_call_et / 60,2) > timeout and username is not null ;
d get_data%rowtype;
stmt varchar2(4000) ;
begin
for d in get_data
loop 
-- audit the query
insert into sys.dbmonitor_tab values (systimestamp, d.inst_id, d.sid, d.serial#, d.username, d.sql_id, d.txt) ;
commit ;

stmt := 'alter system kill session '''|| d.sid ||','||d.serial#||',@'||d.inst_id||''' immediate';
-- kill it!
execute immediate stmt ;
end loop;

exception
when others then
NULL ;
end; 
/

Test Szenario

Session 1: (läuft normalerweise sehr lange):

SQL> select count(*) from dba_objects,dba_objects,dba_objects ;

Session 2: (kill session die länger als 1 Minute laufen):

SQL> exec dbmonitor(1)

PL/SQL procedure successfully completed.

SQL> select * from sys.dbmonitor_tab ;

DATUM INST_ID SID SERIAL# USERNAME SQL_ID SQL_TEXT
---------------------------------------- ---------- ---------- ---------- ---------- ------------------------------ --------------------------------------------------
15-MAY-20 01.39.37.429586 PM 1 85 60201 SYS d3s7r4a3xa0su select count(*) from dba_objects,dba_objects,dba_o
15-MAY-20 01.43.00.013776 PM 1 85 59874 SYS d3s7r4a3xa0su select count(*) from dba_objects,dba_objects,dba_o

Session 1 bekommt folgende Meldung angezeigt:

SQL> select count(*) from dba_objects,dba_objects,dba_objects ;
select count(*) from dba_objects,dba_objects,dba_objects
*
ERROR at line 1:
ORA-00028: your session has been killed

Wenn Oracle 18c oder eine höhere Versionen implementiert ist, bekommt die Session folgenden Error angezeigt und wird nicht gekillt.

Der Benutzer kann in diesem Fall weiterarbeiten.

SQL> select count(*) from dba_objects,dba_objects,dba_objects ;
select count(*) from dba_objects,dba_objects,dba_objects
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Um diese Prozedur autonom zu machen, können wir in der Datenbank einen Scheduler Job erstellen, der alle 5 Minute gestartet wird und jene Sessions killt, die länger als 5 Minuten laufen:

begin
dbms_scheduler.create_job(
job_name=>'SYS.SESSION_KILLER',
job_type=>'PLSQL_BLOCK',
job_action=>'begin dbmonitor(5);end;',
repeat_interval=>'FREQ=MINUTELY;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55',
enabled=>true);
end;
/