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:
- Download the latest from https://www.nuget.org/packages/Oracle.ManagedDataAccess/
- Extract 'Oracle.ManagedDataAccess.dll' to c:\test
- Start powershell
- enter 'netstat -an' and verify no connection exists from your machine to your database
- 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?