Oracle: How can I programmatically arrange to have my current connection killed?

Question

For testing exception handling in the case of a broken connection it is useful to be able to programmatically request the database to kill a connection.

What's the best way to do that?

Answer 1

Here are a couple of bits of code to handle that. This works in a RAC multi-node environment. It assumes you have a TNS entry for each of your cluster instances.

1. a shell script to kill a connection:

   #!/bin/sh
   # oracle-killsession -- kill a specified oracle session
   
   
   sid=$1; ser=$2; inst=$3
   echo -n sys password:
   stty -echo; read pass; stty echo
   echo "alter system kill session '$sid,$ser';"|
        sqlplus -SL sys/$pass@$inst as sysdba
   

2. a client-side function (this in python) to generate a call to the script. I call this at the beginning of my test program and print the string so I can cut and paste.

   def killstring(curs):
       """return a string that will kill this db connection"""
       curs.execute("""SELECT dbms_debug_jdwp.current_session_id,
                              dbms_debug_jdwp.current_session_serial,
                              sys_context('USERENV', 'INSTANCE_NAME')
                       FROM dual""")
       (sid,serial,instance)=curs.fetchone()
       s="oracle-killsession %s %s %s"%(sid,serial,instance)
       return s
   

3. and a sample invocation

   $ oracle-killsession 98 45809 orcl2
   sys password:
   System altered.
   

4. and from my client... hooray!!!

   cx_Oracle.DatabaseError: ORA-00028: your session has been killed