CODEWITHSUNDEEP
oracle-databaseplsql
Astora
Astora

Reputation: 725

PL-SQL - How to add a exception for this loop?

I have a script that I need to use to deploy some changes in the database. For it I need to kill all sessions before to start the changes.

set serveroutput on;
begin
for rec in (SELECT se.sid,se.serial#,se.inst_id FROM gv$session se where type <> 'BACKGROUND' and username not in ('SYSRAC','SYS') and sid <> (select sys_context('userenv','sid') from dual) and status <> 'KILLED') 
loop
  execute immediate 'Alter System Kill Session '''|| rec.Sid|| ',' || rec.Serial# || ',@' ||rec.inst_id||''' IMMEDIATE';
  dbms_output.put_line('Eliminada: '||rec.sid);
end loop;
end;
/

The problem is: I have a database with more than 3000 connections. While this script is executing it is normal for some sessions to disconnect by themselves and it result in the following error:

ERROR at line 1:
ORA-00030: User session ID does not exist.
ORA-06512: at line 4
ORA-06512: at line 4

How can I control the loop to ignore the sessions that disconnect from the database for themselves?

Upvotes: 0

Views: 370

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

I would do this:

set serveroutput on;
declare
sess_not_exist exception;
pragma exception_init(sess_not_exist, -30);
begin
for rec in (SELECT se.sid,se.serial#,se.inst_id FROM gv$session se where type <> 'BACKGROUND' and username not in ('SYSRAC','SYS') 
            and sid <> (select sys_context('userenv','sid') from dual) and status <> 'KILLED') 
loop
  begin
    execute immediate 'Alter System Kill Session '''|| rec.Sid|| ',' || rec.Serial# || ',@' ||rec.inst_id||''' IMMEDIATE';
    dbms_output.put_line('Eliminada: '||rec.sid);
    exception 
    when sess_not_exist then null;
    when others then raise;
  end;
end loop;
end;
/

The reasons:

  1. if the session no longer exists between the cursor and the execution, you should not raise an error
  2. you must encapsulate the unit which runs the execute immediate to treat the exception in any entry in the loop, hence the double begin end section.

I think you want to raise any other exception, therefore I put when others then raise.

Upvotes: 1

Related Questions