• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 884
  • Last Modified:

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

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','');
 
declare
  cursor get_sessions is
    select s.sid,
	       s.serial#,
		   s.username,
		   s.status,
		   s.osuser,
		   s.process,
		   s.machine,
		   s.terminal,
		   s.program,
		   s.module,
		   s.action,
		   s.client_info,
		   s.logon_time
	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);
  begin
    email.get_group_dtls(email_group,group_name,recipients);
	
	email.send('"Stats System" stats@wolseley.co.uk',
			   recipients,
			   '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);
  exception
	when others then
	  dbms_output.put_line('Unable to send email');
	  raise;
  end;
 
begin
  
  -- 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.
    begin
gddebug.put_line('killing session '||r_session.sid||','||r_session.serial#);
      sessions.kill_session(r_session.sid,
	                        r_session.serial#);
	exception
	  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');
		send_email(r_session.sid);
		-- Processing will continue as normal with the next session
	  when others then
		raise;
	end;
  end loop;
 
  -- Run the routine to rename all tables
  dbms_application_info.set_action('Rename BRP Tables');
  brand_rep.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;
 
exception
  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);
end;
/
 
exit;

Open in new window

0
Milleniumaire
Asked:
Milleniumaire
  • 2
1 Solution
 
lilian-arnaudCommented:
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.
0
 
lilian-arnaudCommented:
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.
0
 
MilleniumaireAuthor Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now