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

Question

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?

Why not 'alter system disconnect session'? oracle-base.com/articles/misc/… — Sayan Malakshinov
– Sayan Malakshinov, Commented Jul 13, 2020 at 21:58

Astora · Accepted Answer · 2020-07-13 17:33:39Z

1

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:

if the session no longer exists between the cursor and the execution, you should not raise an error
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.

edited Jul 13, 2020 at 17:33

Astora

7672 gold badges13 silver badges30 bronze badges

answered Jul 13, 2020 at 17:14

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Roberto Hernandez Over a year ago

@Solaire of Astore, yeah, there was a typo in the pragma declaration.

Astora Over a year ago

I've made some tests and it's working perfectly how I wanted. Thanks!

Roberto Hernandez Over a year ago

happy to hear that. :)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related