Oracle Session Information via MS Access Database
We have many access DBs that connect and perform various tasks around our Oracle DBs using ODBC connection for statically linked tables (using Jet engine).
When I query the sessions in Oracle, they are all just listed with "MSACCESS.exe" and so far I haven't found a way to find out which Access Access is behind the session from the DBA's point of view.
Using ADODB, I can, for individual processes / connections, use Oracle's dbms_application_info package call to set client specific details.
Is there a way to do something like this for the underlying join established for related tables?
In case it matters, the access DB is v2010 and the Oracle DB is 10gR1.
source to share
I would look at the Oracle docs for ODBC connection string.
In Access, I use this code to create a connection string for a related table.
Public Function dbCon(ServerName As String, _
DataBaseName As String, _
Optional UserID As String, _
Optional USERpw As String, _
Optional APP As String = "InvocieProgram", _
Optional WSID As String = "RobertPC") As String
' returns a SQL server conneciton string
dbCon = "ODBC;DRIVER=" & SQLDRIVER & ";" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DataBaseName & ";"
If UserID <> "" Then
dbCon = dbCon & "UID=" & UserID & ";" & "PWD=" & USERpw & ";"
End If
dbCon = dbCon & _
"APP=" & APP & ";" & _
"WSID=" & WSID & ";" & _
"Network=DBMSSOCN"
End Function
If you look, there is an APP as well as a WSID that can be used by BOTH by the SQL profiler. I haven't looked at Oracle, but there are probably a few "advanced" settings you can use / set on the connection string. With the help above, then the SQL profiling tools will show the workstation and the APP parameter. Although the above is for SQL Server, I would assume Oracle allows some of these additional options as well. And on top of that, you probably want to use some kind of reconnection code to set up the connection strings, and hence you don't have to manually configure each workstation for the application to connect. This way the code to re-link "once" on startup - every additional launch that you don't need to re-link,and do not reconfigure connection strings with an optional APP and workstation ID that ODBC strings allow. These settings don't really have anything to do in terms of connection, but they show up in the SQL profiler - you can hunt down and view the SQL server logs this way and they'll show the application.
source to share