强制关闭 C# 中的 oracle 连接

Force closing an oracle connection in C#

提问by jonnii

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.

我有一个报告窗口，显示从可能长时间运行的 oracle 存储过程返回的结果。我的问题是当用户关闭窗口时，与 oracle 的连接保持打开状态，并且可能长时间运行的报告不会被取消。

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.

关闭打开的连接的唯一方法是让 DBA 手动终止它们，或者让用户退出整个应用程序。

I've tried calling Closeon 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.

我试过Close从不同的线程调用连接，但这似乎一直在阻塞。我也尝试回滚事务，但这表现出同样的问题。

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 usingstatement. It is about how to force an oracle connection that is executing a query to close.

这个问题不是关于在using声明中包装我的联系。它是关于如何强制执行查询的 oracle 连接关闭。

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();
  }
  

• 启动一个运行查询的线程
• 将连接对象藏在某处

• 在连接对象上调用 close

  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.

问题是这不会关闭连接，而是调用 connection.Close() 阻塞等待过程执行。

采纳答案by flq

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.

嗯，我在 API 中看不到任何内容来中止/取消正在进行的查询。从技术上讲，应该可以使用具有完全权限的第二个会话来识别要中止的会话并在该会话上发出 kill session 命令。我希望您的原始会话能够以某种例外情况出现，但我从未尝试过。

Hereit is explained how to kill a session.

这里解释了如何终止会话。

Hereit 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.

这里回答了如何获取会话 ID。您可以在开始长时间运行的查询之前找到一个，那么从第二个连接中准确杀死该会话应该很容易。

Let us know if it works ;)

让我们知道它是否有效;)

回答by tbone

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;

回答by Nick Berardi

As with any provider in .NET you can call Dispose

与 .NET 中的任何提供程序一样，您可以调用 Dispose

using(var conn = /* your connection */) {
    // do your stuff

    conn.Close();
} // this will automatically call .Dispose()

So that is all you need to do.

所以这就是你需要做的。