oracle ODP.net 连接池：ClientID、Client Identifier 从第一个登录的用户开始永远不会改变
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5761175/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ODP.net Connection Pooling: ClientID, Client Identifier never changes from first user who logs in
提问by jlrolin
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.
场景：我们有一个应用程序在 ASP.net 应用程序中使用 Oracle 10g 和最新版本的 ODP.net。我们正在利用 OracleConnection 对象上的 .ClientID WriteOnly 属性将特定用户 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.
启用后，第一个登录的人（例如：USER1）更新记录，MODIFIED_BY 是 USER1，但是当不同的用户随后进入网站，从而获取池连接时，尽管传入，MODIFIED_BY 仍然是 USER1 USER2 到 ClientID。
Our database logic is as follows:
我们的数据库逻辑如下：
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:
我们在 ASP.net 会话中保留一个类，其中包含我们的数据库连接逻辑。在最初的调用中，这是我们的构造函数：
Public Sub New(ByVal connection As String, Optional ByVal oracleClientID As String = "")
MyBase.New()
_oracleConnection = New OracleConnection(connection)
_clientID = oracleClientID
End If
End Sub
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
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.
想法是，由于连接是池化的，因此假设调用 LOGON 触发器永远不会发生，并且客户端标识符永远不会再次设置。然而，ORACLE 的文档指出 ClientID 正是用于我们正在尝试做的事情。
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?
有没有人对为什么 SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') 没有被设置为新的 USERID 有任何想法，当连接池在我们的 .NET 应用程序中使用 ODP.net 时传递到 ClientID 中？这是数据库设置还是侦听器设置？
Update
更新
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.
我们将此问题转发给了 Oracle。为此，我们必须创建一个模拟该问题的小型测试应用程序。这样做时，在我的本地主机上 - 使用 Visual Studio 的内置 Cassini Web 服务器一切正常。使用 IIS 时，会出现此问题。
UPDATE
更新
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).
确定 IIS 不是问题。由于池化的连接被重用，包变量没有被清除，本质上，池化应该做什么。我们通过使用 DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE) 解决了这个问题。
回答by Luke Davis
Try using DBMS.Rest_Package before closing the connections.
在关闭连接之前尝试使用 DBMS.Rest_Package。
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 的问题是保持连接，因此当每个用户打开和关闭与 ODP 的连接时，池化将会话包变量保存在内存中；直到连接超时。但是，由于从数据库到池的连接的超时/超时和重新建立仅在连接返回到池之后发生，因此您正在使用其他人的会话数据。
回答by mservidio
This works fine both with pooling on and off. The ClientId and ClientInfo on the Oracle Session do not update until a command is executed.
这在打开和关闭池时都可以正常工作。在执行命令之前，Oracle 会话上的 ClientId 和 ClientInfo 不会更新。
Could you verify your if statement is correct? If _clientID <> "" Then _oracleConnection.ClientId = _clientID. Even when you close the connection, the clientId will still stay the same. Not sure where your setting/getting _clientId when you pass that into your method.
你能验证你的 if 语句是正确的吗？如果 _clientID <> "" 那么 _oracleConnection.ClientId = _clientID。即使关闭连接，clientId 仍将保持不变。当您将其传递到您的方法时，不确定您的设置/获取 _clientId 在哪里。
class Program
{
static void Main(string[] args)
{
TestClientId test = new TestClientId();
test.DoSomething("FirstClientId");
test.DoSomething("ChangedClientId");
}
}
public class TestClientId
{
/// <summary>
/// The connection string.
/// </summary>
private const string ConnString = "DATA SOURCE=//server:port/service_name;USER ID=user;PASSWORD=pswd;";
/// <summary>
/// The oracle connection.
/// </summary>
private OracleConnection connection;
/// <summary>
/// The oracle session id.
/// </summary>
private long sid;
/// <summary>
/// Initializes a new instance of the <see cref="TestClientId"/> class.
/// </summary>
public TestClientId()
{
this.connection = new OracleConnection(ConnString);
}
/// <summary>
/// Changes the client id of the oracle connection.
/// </summary>
/// <param name="clientId">The client id.</param>
public void DoSomething(string clientId)
{
this.connection.Open();
this.sid = this.GetSessionId(this.connection);
if (!string.IsNullOrEmpty(clientId))
{
this.connection.ClientInfo = clientId;
this.connection.ClientId = clientId;
}
OracleCommand command = new OracleCommand("select * from dual", this.connection);
command.ExecuteNonQuery();
this.connection.Close();
}
/// <summary>
/// Gets the session id.
/// </summary>
/// <param name="con">The connection object.</param>
/// <returns>The current oracle session id.</returns>
public int GetSessionId(OracleConnection con)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "select SYS_CONTEXT('USERENV','SID') from dual";
object sid = cmd.ExecuteScalar();
return Convert.ToInt32(sid);
}
}
回答by V4Vendetta
The ClientId is reset only on the connection close and if you are closing then its bound to be reset.
ClientId 仅在连接关闭时重置，如果您正在关闭，则其绑定将被重置。
Connection pooling helps the database server to timeout an idle session and use the connection to service an active session. The idle logical session remains open, and the physical connection is automatically reestablished when the next request comes from that session. So is the connection actually closed ?
连接池帮助数据库服务器使空闲会话超时并使用该连接为活动会话提供服务。空闲逻辑会话保持打开状态，当下一个请求来自该会话时，物理连接会自动重新建立。那么连接实际上关闭了吗？
So it would be good to set the Session identifier by DBMS_SESSION.SET_IDENTIFIER
所以最好通过DBMS_SESSION.SET_IDENTIFIER设置会话标识符
Hope this helps
希望这可以帮助
回答by Codo
It seems very dangerous to me to keep a reference to a database connection in your session. The idea of the connection pool is that a request borrows a connection for the duration of the request. When the request completes, the connection goes back to the pool. The connection will then be reused for more requests from different users.
在您的会话中保留对数据库连接的引用对我来说似乎非常危险。连接池的思想是一个请求在请求的持续时间内借用一个连接。当请求完成时，连接返回到池中。然后，该连接将被重复用于来自不同用户的更多请求。
I suspect that all sorts of nasty things happen when you store a connection in your session. You probably use the connection while it's being concurrently used by another request. Or you might get a closed connection because the connectin pool has to decide to close it.
我怀疑在会话中存储连接时会发生各种令人讨厌的事情。您可能会在另一个请求同时使用该连接时使用该连接。或者你可能会得到一个关闭的连接，因为连接池必须决定关闭它。
Furthmore, the LOGON trigger is probably only executed when the database connection is created for the first time but is not executed again when the connection is reused for a different request or a different user.
此外，登录触发器可能仅在第一次创建数据库连接时执行，但在连接被重用于不同的请求或不同的用户时不会再次执行。
To fix your problem, grab a database connection at the beginning of every request and explicitly set the Client ID (and/or execute the code that's run by the LOGON trigger). Then use this connection for the duration of the request. But don't store it anywhere after the request has finished.
要解决您的问题，请在每个请求的开头获取数据库连接并显式设置客户端 ID（和/或执行由 LOGON 触发器运行的代码）。然后在请求期间使用此连接。但是不要在请求完成后将它存储在任何地方。
That way, your connection is always properly initialized with the current user's context. And you adhere to the rules of the connection pool.
这样，您的连接始终使用当前用户的上下文正确初始化。并且您遵守连接池的规则。
回答by Simon Mourier
When connection pooling is enabled, which is good and of course the way to go in an ASP.NET scenario (and in most scenarios in fact), you should not storeany db connection. You must open and close connections when you need it.
当启用连接池时，这当然是 ASP.NET 场景中的好方法（事实上在大多数场景中），您不应该存储任何数据库连接。您必须在需要时打开和关闭连接。
Here is a link about SQL Server, but it's the same with Oracle that explains it: SqlConnection Class
这里有一个关于 SQL Server 的链接，但它与 Oracle 的解释相同：SqlConnection Class
So the code you need to use when calling Oracle should be something like this, anywhere in your app, whenyou need it:
因此，您在调用 Oracle 时需要使用的代码应该是这样的，在您的应用程序中的任何位置，当您需要时：
Using connection As New OracleConnection(connectionString)
connection.Open()
' Do work here; connection closed on following line.
End Using
The thing is: you cannot have connection pooling enabled with specific user information in the connection string. So I suggest you implement your auditing code without using the connection string.
问题是：您不能使用连接字符串中的特定用户信息启用连接池。所以我建议你在不使用连接字符串的情况下实现你的审计代码。
