Script to generate SQLs to terminate rman sessions

Killing the rman main process from the command line doesn’t immediately kill the rman sessions using the script below which will create SQL statements to kill your RMAN sessions in Oracle and in most cases it will also terminate the oracle shadow process. The script below generates ALTER statements to kill oracle session(s) and a ps command to verify the UNIX processes have been terminated. The script works on 8i, 9i and 10g

set linesize 120
set serveroutput on

cursor l_cur is select ‘alter system kill session ”’ || s.sid || ‘,’ || s.serial# || ”’;’ msg, spid
from v$session s, V$process p
where s.program like ‘%rman%’
and s.paddr = p.addr;
l_found boolean := false;
l_process_list VARCHAR2(2000) := null;
for l_rec in l_cur
if l_process_list is not null Then
— create process list to use with ps
l_process_list := l_process_list || ‘|’;
end if;

l_found := true;
l_process_list := l_process_list || l_rec.spid;
end loop;

if l_found = FALSE THEN
dbms_output.put_line(‘No RMAN sessions found’);
dbms_output.put_line(‘ps -ef | egrep “‘ || l_process_list || ‘”‘);
end if;

alter system kill session ‘19,9935’;
alter system kill session ‘21,9470’;

ps -ef | egrep “9248918|9109690”


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.