When you use the Microsoft .NET Framework Data Provider for
Oracle inside ASP.NET, over time, the number of connections to the Oracle
database increases beyond what you expect. Additionally, you detect an
increasing number of idle connections when you examine Oracle connections on
the Oracle server.
A supported fix is now
available from Microsoft, but it is only intended to correct the problem that
is described in this article. Apply it only to computers that are experiencing
this specific problem. This fix may receive additional testing. Therefore, if
you are not severely affected by this problem, Microsoft recommends that you
wait for the next Microsoft .NET Framework service pack that contains this
fix.
To resolve this problem immediately, contact Microsoft Product
Support Services to obtain the fix. For a complete list of Microsoft Product
Support Services phone numbers and information about support costs, visit the
following Microsoft Web site:
NOTE: In special cases, charges that are ordinarily incurred for
support calls may be canceled if a Microsoft Support Professional determines
that a specific update will resolve your problem. The typical support costs
will apply to additional support questions and issues that do not qualify for
the specific update in question.
The English version of
this fix should have the following file attributes or later:
Date Version Size File name
-------------------------------------------------------------
18-Oct-2002 1.0.1087.0 290,816 System.data.oracleclient.dll
You can release the Oracle connections by restarting the IIS
Admin service and all IIS dependant services and processes (such as
dllhost.exe, and aspnet_wp.exe).
Microsoft
has confirmed that this is a problem in the Microsoft products that are listed
at the beginning of this article.
When ASP.NET recycles an AppDomain, the finalizers for the
.NET Framework Data Provider for Oracle do not correctly clean the native
connections to the Oracle server. Over time, when an AppDomain in ASP.NET is
cycled over and over, the number of native Oracle connections increases. When
you restart IIS, all dependant processes recycle. When you close these
processes, the native TCP/IP socket handles are released. This hotfix resolves
this problem by correctly cleaning the native Oracle connections in the
finalizers.
This issue occurs most frequently in ASP.NET applications
because ASP.NET can recycle the application domain in a process periodically.
However, any process that creates and destroys .NET application domains may
also experience this problem. Therefore, this issue is not strictly limited to
ASP.NET applications.
An Oracle administrator can run the following
SQL statement from the SQL*Plus utility, or another utility that can connect to
Oracle and run SQL statements, to view all active and idle Oracle
connections:
SELECT LOGON_TIME, OSUSER, PROCESS,MACHINE,PROGRAM
FROM V$SESSION WHERE PROGRAM IS NOT NULL
This displays a list of all computers and processes that are
connected to the Oracle database. The list includes the names of the processes
that opened the connections. The following is an example of this list:
LOGON_TIME OSUSER PROCESS MACHINE PROGRAM
------------------- ------ ------- ------------------ ------------
2002-08-30 14:31:01 ASPNET 832:177 MYDOMAIN\MYMACHINE aspnet_wp.exe
2002-08-30 14:31:01 ASPNET 832:176 MYDOMAIN\MYMACHINE aspnet_wp.exe
- LOGON_TIME. The time when the connection was
opened.
- OSUSER. The operating system of the user who opened the
connection.
- PROCESS. The process id and thread id where the connection
was first opened.
- MACHINE. The domain and machine name where the process
resides.
- PROGRAM. The executable name that created the
connection.
You can use the LOGON_TIME to search for possible long-running
idle connections.