Why isn't pre-defined exception being trapped after upgrade to 10g?

Posted on 2009-02-16
Last Modified: 2013-12-18
I have a pl/sql routine that has been running successfully for years on Oracle 9i and after upgrading to 10g this weekend it has started to fail with the following error:

ORA-20002: Error occurred: ORA-20002: ORA-00030: User session ID does not exist.
ORA-06512: at line 111

I've attached a copy of the code, the purpose of which is to rename a bunch of tables that have already been re-built on a data warehouse.  The code identifies all user sessions and kills them before performing the rename.  This is necessary as any sessions using the tables will prevent the rename from occuring.  Normally the sessions being killed will be long running Business Objects reports.

As you can see there is an exception handler defined for the "ORA-00030: User session ID does not exist" error as it is possible that between identifying the session and killing it the session disconnects.

For some reason this exception handler is not being processed even when an ORA-00030 occurs.

Any idea why this is the case?

whenever sqlerror exit failure rollback;

set lines 250 verify off

set serveroutput on size 999999

execute dbms_application_info.set_module('ST3470','');


  cursor get_sessions is

    select s.sid,













	from v$session s

	where s.type = 'USER'

	and   s.schemaname not in ('SYS','SYSTEM')

	and   s.status in ('ACTIVE','INACTIVE')

	and   s.audsid != userenv('SESSIONID')

	and   s.module != 'SL1880'

	order by s.sid;

  email_group     number(6) := &2;

  first           boolean := true;

  restricted_mode boolean := false;

  no_session      exception;

  pragma exception_init(no_session, -30); -- User session ID does not exist

  procedure send_email (i_session_id in number) is

    group_name varchar2(30);

    recipients varchar2(4000);




	email.send('"Stats System"',


			   'ST3470 is unable to kill a session',

			   'Session '||i_session_id||' could not be killed. Please investigate');

    dbms_output.put_line('Email sent to '||group_name);


	when others then

	  dbms_output.put_line('Unable to send email');





  -- Switch database to restricted session mode to prevent new logins.

  dbms_application_info.set_action('Set restricted mode');

  execute immediate 'alter system enable restricted session';

  restricted_mode := true;


  -- Identify and kill sessions

  for r_session in get_sessions loop

    if first then

	  dbms_output.put_line('The following sessions will be killed');

	  dbms_output.put_line('SID    SERIAL#  USERNAME        STATUS   OSUSER          PROCESS      MACHINE              TERMINAL        PROGRAM                   MODULE                    ACTION               CLIENT INFO          LOGON TIME');

	  first := false;

	end if;


    -- Display details of the session that is about to be killed.

	-- Note: For some strange reason a null (chr(0)) character sometimes appears on the end

	--       of the machine value so this is removed.

    dbms_output.put_line(rpad(to_char(r_session.sid),6)||' '||

	                     substr(to_char(r_session.serial#,'99999999'),2)||' '||

	                     rpad(substr(nvl(r_session.username,' '),1,15),15)||' '||

	                     rpad(r_session.status,8)||' '||

	                     rpad(substr(nvl(r_session.osuser,' '),1,15),15)||' '||

	                     rpad(r_session.process,12)||' '||

	                     rpad(substr(nvl(replace(r_session.machine,chr(0)),' '),1,20),20)||' '||

	                     rpad(substr(nvl(r_session.terminal,' '),1,15),15)||' '||

	                     rpad(substr(nvl(r_session.program,' '),1,25),25)||' '||

	                     rpad(substr(nvl(r_session.module,' '),1,25),25)||' '||

	                     rpad(substr(nvl(r_session.action,' '),1,20),20)||' '||

	                     rpad(substr(nvl(r_session.client_info,' '),1,20),20)||' '||

	                     to_char(r_session.logon_time,'DD-MON-YY HH24:MI:SS'));


	-- Kill the session and trap known errors so that they can be ignored.


gddebug.put_line('killing session '||r_session.sid||','||r_session.serial#);




	  when no_session then

gddebug.put_line('unable to kill session '||r_session.sid);

		dbms_output.put_line('WARNING: Session '||r_session.sid||

							 ' no longer exists and cannot be killed');


		-- Processing will continue as normal with the next session

	  when others then



  end loop;

  -- Run the routine to rename all tables

  dbms_application_info.set_action('Rename BRP Tables');



  -- Switch database to normal mode to allow new logins.

  dbms_application_info.set_action('Unset restricted mode');

  execute immediate 'alter system disable restricted session';

  restricted_mode := false;


  when others then

    -- Ensure database is not left in restricted mode when script fails

    if restricted_mode then

      execute immediate 'alter system disable restricted session';

    end if;

    raise_application_error(-20002,'Error occurred: '||sqlerrm);




Open in new window

Question by:Milleniumaire
    LVL 3

    Expert Comment

    in fact, your issue is related to sessions closed in oracle db, but the shadow process is still active on the OS side.

    You can trap this error, but you have to kill the remaining shadow process on OS level.
    LVL 3

    Accepted Solution

    sorry, I missed the declaration of the exception no_session in your code.

    Could you post the content of the packaged procedure sessions.kill_session ?

    Perhaps the issue is its own exception handling.
    LVL 16

    Author Comment

    Dooh!  Just realised that the ORA-00030 is raised in the sessions.kill_sessions package which then raises a user-defined error and it is this that needs to be trapped by the code, not the ORA-00030.

    Sometimes it's just good to work through an issue to make things clearer.

    Thanks for pointing out the exception handler in sessions.kill_session.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now