Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle.ManagedDataAccess connection remains active after close/dispose

User_XSI6XJun 24 2019 — edited Jun 25 2019

I'm using Powershell to connect to our Oracle database.

Ooriginally I was using 4.122.1.0 from 2018 but have since upgraded to 18.3.0 all the way up to the current release 19.3.0, using the package from https://www.nuget.org/packages/Oracle.ManagedDataAccess/

The issue I have is that since upgrading to 18.3.0 closing and/or disposing the connection object no longer truly disconnects the user.

You can view this by doing a `netstat -an` before connecting, after connecting and after closing and disposing on the windows machine on which I run my code. Even when the connection was closed/disposed hours ago, but with the shell not closed, the connection remains active.

The biggest issue I have with this is that the database on our various environments does a frequent rollback, which fails because the user is still connected. I could off course try to fix this in the rollback script but thats tricky and also not the way it should be.

Steps to reproduce:

  1. Download the latest from https://www.nuget.org/packages/Oracle.ManagedDataAccess/
  2. Extract 'Oracle.ManagedDataAccess.dll' to c:\test
  3. Start powershell
  4. enter 'netstat -an' and verify no connection exists from your machine to your database
  5. run the following powershell code, after editing it to suite your environment. My DataSource contains a failover which I left here because it might be relevant...

$dataSource = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=YOURDATABASEHOST1)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=YOURDATABASEHOST2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=YOURDBSERVICE)))'

$userID= 'YOURDBUSER'

$securePassword = Read-Host -AsSecureString -Prompt 'Password'

$securePassword.MakeReadOnly()

Add-Type -Path "C:\test\Oracle.ManagedDataAccess.dll"

$connBuilder = New-Object Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder

$connBuilder["Data Source"] = $dataSource

$oracleCredential = New-Object Oracle.ManagedDataAccess.Client.OracleCredential $userID, $securePassword

$dbConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection $connBuilder, $oracleCredential

$dbConn.open()

netstat -an | findstr "DBIPRANGE"

$dbConn.close()

netstat -an | findstr "DBIPRANGE"

$dbConn.dispose()

netstat -an | findstr "DBIPRANGE"

Start-Sleep -Seconds 1800

netstat -an | findstr "DBIPRANGE"

Since powershell basicaly just runs .NET the same issue should exist there, even with the using statements. The connection only get's closed when the application in which the Oracle.ManagedDataAccess was loaded closes.

Question1: Is there some way to get this connection to close that I've missed?

Question2: I've not found an official place to put bugs other than file a ticket perhaps, is there some place to post bugs other than here?

This post has been answered by Yves_D on Jun 25 2019
Jump to Answer

Comments

Post Details

Added on Jun 24 2019
5 comments
4,746 views