ODP.net连接池：客户端ID，客户端标识符从来没有从谁登录第一个用户改变 [英] ODP.net Connection Pooling: ClientID, Client Identifier never changes from first user who logs in

问题描述

场景：我们正在使用Oracle 10g和一个ASP.net应用程序中的最新版本ODP.net的应用。我们正在利用的OracleConnection对象上的.ClientID只写属性在一个特定的用户ID传递给数据库审计目的。当连接池被禁用，这完美的作品。

Scenario: We have an application that is using Oracle 10g and the latest version of ODP.net within an ASP.net application. We are utilizing the .ClientID WriteOnly property on the OracleConnection object to pass in a specific UserID to the database for auditing purposes. When Connection Pooling is disabled, this works perfectly.

当它被启用，谁记录的第一人（例如：USER1）更新了记录和MODIFIED_BY是USER1，但是当不同的用户头进入该网站后，从而抓住了池连接时，MODIFIED_BY仍然USER1尽管USER2传递到客户端ID。

When it is enabled, the first person who logs in (ex: USER1) updates a record and the MODIFIED_BY is USER1, but when a different user heads into the website after, thus grabbing the pooled connection, the MODIFIED_BY is still USER1 despite passing in USER2 to the ClientID.

我们的数据库逻辑如下：

Our database logic is as follows:

我们在有我们的数据库连接的逻辑在它的ASP.net会话持久化类。在初始呼叫，这是我们的构造：

We persist a class in an ASP.net session that has our database connection logic in it. On the initial call, this is our constructor:

Public Sub New(ByVal connection As String, Optional ByVal oracleClientID As String = "")
        MyBase.New()
        _oracleConnection = New OracleConnection(connection)
        _clientID = oracleClientID
        End If
    End Sub

这里的code的要点打开连接和关闭，配置：

Here’s the gist of the code to open connection and close, dispose:

Try
    _OraCmd = New OracleCommand(command, _oracleConnection)
    With _OraCmd
        .BindByName = True
        .Parameters.Clear()
        .CommandType = CommandType.StoredProcedure
        _oracleConnection.Open()
            If _clientID <> "" Then _oracleConnection.ClientId = _clientID
        Dim OraDadpt As New OracleDataAdapter(_OraCmd)
            '' Logic to get data
        OraDadpt.Fill(ds)
    End With
Catch ex As Exception
    Throw ex
Finally

    ClearParameters()
    _OraCmd.Dispose()
    _oracleConnection.Close()
End Try

本以为是因为连接池，还有就是登录触发器从未发生，客户端标识符不会再设置一个假定的呼叫。 ORACLE的文档，但是，指出客户端ID用于正是我们正在尝试做的。

The thought is that since the connection is pooled, there is an assumed call to a LOGON Trigger that never happens and the Client Identifier is never set again. ORACLE's documentation, however, states that the ClientID is used for exactly what we are trying to do.

没有人有任何想法，为什么SYS_CONTEXT（'USERENV'，'CLIENT_IDENTIFIER'）没有被设置为当连接池是我们的.NET应用程序中使用，ODP.net传递到客户端ID新的USERID ？这是一个数据库设置，侦听器设置？任何帮助将是AP preciated。

Does anyone have any thoughts as to why the SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') is not being set to a new USERID that is passed into the ClientID when connection pooling is used within our .NET application with ODP.net? Is this a database setting, a listener setting? Any help would be appreciated.

更新

我们将问题到Oracle。为此，我们必须创建一个小的测试应用程序，模仿的问题。如果这样做，在我的本地 - 一切工作完全使用Visual Studio的内置卡西尼Web服务器。 使用IIS，就会出现问题。

We forwarded the issue to Oracle. In doing so, we had to create a small test app that mimicked the issue. When doing that, on my localhost -- everything worked perfectly using Visual Studio's built-in Cassini web server. With IIS, the issue occurs.

更新

确定IIS是没有问题的。它包变量没有被清理出去，由于该合并被重新使用，在本质上的连接，什么池是应该做的。我们通过使用DBMS_SESSION.MODIFY_PACKAGE_STATE（DBMS_SESSION.REINITIALIZE）解决了这个。

Determined that IIS wasn't the problem. It was package variables not being cleared out due to connections that were pooled being re-used, in essence, what pooling is supposed to do. We solved this by using DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE).

推荐答案

尝试关闭连接之前使用DBMS.Rest_Package。

Try using DBMS.Rest_Package before closing the connections.

我觉得这个问题是统筹开启ODP因此保持连接，每个用户打开和关闭与ODP的连接，池是保持会话包变量在内存中;直到连接超时。然而，由于时间输入/输出和重新建立从数据库到池中只发生后一个连接返回到池中，你与别人的会话数据运行的连接。

The problem I think is with pooling turned on ODP is keeping the connection thus as each user opens and closes a connection with ODP, pooling is keeping the session package variables in memory; until the connection times out. However, since the time/out and re-establish of a connection from the database to the pool only occurs AFTER a connection is RETURNED to the pool, you're operating with someone else's session data.

这篇关于ODP.net连接池：客户端ID，客户端标识符从来没有从谁登录第一个用户改变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！