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

Script:
set linesize 120
set serveroutput on

declare
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;
begin
for l_rec in l_cur
loop
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;
dbms_output.put_line(l_rec.msg);
l_process_list := l_process_list || l_rec.spid;
end loop;

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

Output:
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s