Force closing an oracle connection in C#

Question

I have a report window which shows the results returned from a potentially long running oracle stored procedure. My problem is when the user closes the window the connection to oracle remains open and the potentially long running report doesn't get cancelled.

The only way to close the open connection is for either the DBAs to kill them manually or for the user to exit the entire application.

I've tried calling Close on the connection from a different thread, but this seems to continuously block. I also tried rolling back a transaction, but this exhibits the same problem.

I'm worried that the only solution will be to run the query in a different process (or maybe app domain?).

It's likely that I'm missing something obvious, any help would be greatly appreciated.

PLEASE READ

This question is not about wrapping my connection in a using statement. It is about how to force an oracle connection that is executing a query to close.

Example:

• Start a thread running a query
• Stash the connection object somewhere

• Call close on the connection object

  public void Go()
  {
      OracleConnection connection;
      var queryThread = new Thread(
          () =>
              {
                  using (connection = OpenOracleConnection())
                  {
                      // execute stored proc that takes 45 mins
                      // raise an event with the data set we load
                  }
              });
  
      Thread.Sleep(3000); // give it time to be useless
  
      var closeThread = new Thread(
          () =>
              {
                  connection.Close();
              });
      closeThread.Start();
  }
  

The problem is that this doesn't close the connection, instead the call to connection.Close() blocks waiting for the procedure to execute.

Answer 1

Hm, I can't see anything in the API to abort / cancel an ongoing query . Technically it should be possible, with a second session with full privileges, to identify the session you want to abort and issue the kill session command on that session. I would expect your original session to bail out with some kind of exception, but I've never tried it out.

Here it is explained how to kill a session.

Here it is answered how to get the session id. You could find that one out before starting the long running query, then it should be pretty easy to kill exactly that session from a second connection.

Let us know if it works ;)

Answer 2

To see what/who is blocking who:

select s1.username || '@' || s1.machine
   || ' ( SID=' || s1.sid || ' )  is blocking '
   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS status
   from v$lock l1, v$session s1, v$lock l2, v$session s2
   where s1.sid=l1.sid and s2.sid=l2.sid
   and l1.BLOCK=1 and l2.request > 0
   and l1.id1 = l2.id1
   and l2.id2 = l2.id2;