CODEWITHSUNDEEP
oracle-databaseerror-handlingoracle12ckill-process
Lumberjack
Lumberjack

Reputation: 21

How to handle ORA-00028 "your session has been killed" in Oracle 12c (12.2.0.1.0)?

The problem is that handling an error ORA-00028 is kinda tricky. Please, look at the code below.

If you run proc1 in session 1 and while it's still running you kill session 1 with ALTER SYSTEM KILL SESSION then you get ORA-00028 error message and no row in llog table. If you run proc1 and let it finish (1 min) then error handling works as expected and you get no error message and 1 row in llog table. But the funny thing is if after that you run proc1 again and kill that session you get no error-message (ORA-00028 handled) and one more row in llog table.

So for ORA-00028 to be handled in exception clause you need to catch some other error first. It seems to be a bug. Has anyone faced this problem?

/* creating simple table with logs */

create table llog(time timestamp, error varchar2(4000));
/

/* creating package */

create or replace package my_pack
is
       procedure proc1;
end;
/
/* creating package body*/

create or replace package body my_pack
is

e_session_killed EXCEPTION;
PRAGMA EXCEPTION_INIT(e_session_killed, -00028);


procedure error_log (time llog.time%type, error llog.error%type) is
  pragma autonomous_transaction;
begin
  insert into llog values (time, error); 
  commit;
end;


procedure proc1 is
begin

  dbms_lock.sleep(60);

  raise too_many_rows;

  exception
    when e_session_killed then
      error_log(systimestamp, sqlerrm);

    when others then
      error_log(systimestamp, sqlerrm);

end;

end;

Upvotes: 2

Views: 14590

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

You can't catch a kill session. It interrupts the current operation (as mush as it can - there might be some low level operations that cause issues), rolling back the open transaction(s). Once the rollback is complete the client is told that it is disconnected (assuming the client is still there) and the process goes away.

There's a couple of variants of kill session that affect the order of those but you're not going to be able to insert anything into any table from a killed session.

The only exception might be through a database link or similar, where you actually have two separate sessions/processes going on at the same time.

Upvotes: 0

Related Questions