Killing Sessions In Oracle

articles: 

KILLING SESSIONS IN ORACLE
--------------------------
Author JP Vijaykumar
Date Jan 27th 2009
Modified Jan 29th 2009

/*
One of my procedure is taking long time to complete. I want to kill my job and
re-run the procedure after incorporating HINTS.

Let us explore the options to kill long running jobs.

From the v$session, I found that my session is active.
*/

select username, osuser,sid,serial#,status from v$session where sid=57;

SYS oracle 57 6597 ACTIVE

--I killed my session with the command:

alter system kill session '57,6597';
alter system kill session '57,6597'
*
ERROR at line 1:
ORA-00031: session marked for kill

/*
I checked the status of my session, and it says 'KILLED',
but still the procedure is running.
*/

select username, osuser,sid,serial#,status from v$session where sid=57;

SYS oracle 57 6597 KILLED

1 row selected.

/*
I want to kill the job at the os level.
*/

select c.spid , b.osuser , b.username , b.sid , b.serial# ,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
and b.sid = 57;

/*
I found the process id of my job and executed the kill command at os level.
*/

kill -9 159

/*
Then I wondered, why should I go through this hasle to kill my procedure.
Is there a simple way to stop my long running procedure?

I modified my procedural logic:

I created a flag_jp table with one column flg that hold either 'YES' or 'NO'.
At regular intervals, during the run of my procedure, after committing a certain
number of rows, I check the flg value from my flag_jp table.
If the flg value is 'NO' then I simply exit out of the execution of my procedure.
*/

set serverout on size 1000000

declare
v_num number(10):=0;
v_flg char(3);
/***********************************
create table scott.flag_jp(flg char(3));

insert into scott.flag_jp values('YES');
commit;

To exit out of the procedure run the update and commit:
update scott.flag_jp set flg='NO';
commit;
************************************/
begin
for c1 in (select rowid, .., .., from ..
) loop

begin

insert into .. values (
..
..
.. );

delete from .. where rowid = c1.rowid;

v_num:=v_num+1;

if (v_num >= 1000) then
commit;
v_num:=0;

select upper(flg) into v_flg from scott.flag_jp;

if (v_flg = 'NO') then
dbms_output.put_line('Exiting from the loop as the FLAG is set to '||v_flg);
exit;
end if;

end if;

exception
when others then
dbms_output.put_line(c1.rowid||' '||sqlerrm);
end;
end loop;
end;

/*
After every 1000 rows processed, my procedure checks the flg column value
from the flag_jp table. If the flg value is set to 'NO', then my procedure
stops execution.

Whenever I want to stop my procedure, I just run an update statement on
my FLAG_JP table followed by a commit.

update scott.flag_jp set flg='NO';
commit;

Remember, the procedure stops after the current 1000 rows are processed and committed.
You can change the number of rows processed, in the counter, for change of control
in procedure.

You can incorporate a while loop and update the flg value during every run, if you like.

*/
#!/usr/bin/ksh

#This script checks for the existence of stop_file in the current directory.
#As long as the file is not found. The script runs.
#When you touch a file with the name stop_file, the execution of the script stops
#touch stop_file

cd /home/oracle/jp

while [ true ]
do

date
ls -ltr

cat fullexport.log|grep ORA-
echo "To kill this while loop run the command touch /home/oracle/jp/stop_file"

if [ -f stop_file ]; then
exit
fi
sleep 300
done
exit

/*
To stop this shell script run the command
touch /home/oracle/jp/stop_file
No need to find my script's pid
and execute a kill -9 command.
*/

Happy scripting.

References: http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

Comments

gamyers's picture

If your session has done a lot of data changes and not yet committed, then it has probably generated a lot of undo.
When you kill a session, the process will try to apply the undo before responding back to the client with the "your session has been killed" message.
If you kill the process, then one of the background processes (I think PMON) will handle the undo.

Either way, the transaction will be kicking around until those data changes have been backed out.
You can't really speed it up. Just accept it.

The only time it is worth killing the OS process is when the client has been killed. In that case, the OS process can sit around waiting for the non-existent client to respond before it dies cleanly.

I use the following script to report current sessions. I like it because it provides th spid (OS PID) in the event that kill immediate does not work within Oracle. I have found that kill without the immediate clause often results in a long wait.

Best Regards --

set verify off
set echo off
set pagesize 1000
SET LINESIZE 120
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YY HH24:MI';

column  username    format a15     heading "User"
column  osuser      format a21     heading "OSUser"
column  logon_time  format a15     heading "Logged On"
column  program     format a30     heading "Program"
column  machine     format a26     heading "Machine"
select sid,s.serial#,spid,s.username,osuser,logon_time,s.program 
from v$process p, v$session s 
WHERE p.addr = s.paddr 
order by sid;

prompt To kill a session above, use the command:
prompt --   alter system kill session '[sid], [SERIAL#]' immediate

Use -

Alter system disconnect session 'SID,SERIAL#' IMMEDIATE ;

It will kill the dedicated process id on the OS .

Thanks,
Saurabh