Oracle Managed DataAccess connection object is keeping the connection open

Question

I'm using Oracle.ManagedDataAccess Nuget package version 18.3.0. I tried many things. I tried to dispose everything I can think of, even oracle parameters objects. And wrapped everything inside a using block but to no avail. The only thing that actually worked for me is that the commented line OracleConnection.ClearPool(oracle);. Is this a bug, or some configuration related issue, or am I misunderstand things here? Also, I tried to remove the reference of Oracle.ManagedDataAccess and replaced it with a reference to System.Data.OracleClient and that actually worked for me. It automatically closed the connection, so no connection left with "In-Active" status. The code below I moved it into a simple, single button, Windows Forms application to make 100% sure nothing is interfering and the problem still occurring.

using (var oracle = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=SomePortHere))(CONNECT_DATA=(SERVER=SHARED)(SERVICE_NAME=anotherHost)))", new OracleCredential(userName,password)))
                {
                    oracle.Open();
                    using (var command = new OracleCommand())
                    {
                        var query = "SELECT x from y where z=:param1";
                        command.Connection = oracle;
                        command.CommandText = query;
                        command.CommandType = System.Data.CommandType.Text;
                        var param1 = new OracleParameter(":param1", xyz);
                        command.Parameters.Add(param1);
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                //read the data from the reader
                            }
                        }
                        param1.Dispose();
                    }
                    //If this line is commented, there will be a connection left open, with InActive status
                    //OracleConnection.ClearPool(oracle);
                }
                password.Dispose();
                return myData;

And this is an image to show the opened connection through Toad.
Of course, for each click to that button, the code above will execute and a new session will remain open, until what you see in the image. The name "TheTesterOfAllTests.exe" is the Windows Forms app.
Is this a configuration problem? Is there any way to solve this issue other than using ClearPool method? Because it affects the performance of the app.
P.S. The application that is originally using the code above is a WCF Service that is consumed by a Web application.
P.S. 2 There is some kind of memory leakage, with each click to that button the memory usage increases

Answer 1

It turns out the problem is in the way internally oracle creates connection, since for each newly created OracleConnection object, a new Connection is added to the Connection pool. I counted 91 connection entry in the connection pool. The solution was to use one OracleConnection instance for each request "Per Request Scope". I implemented that by using a simple generic IDatabase<TConnection> interface with one TConnection GetConnection<TConnection>() method and of course for each method that will be called on that same request's instance, a pair of Open/Close call will take place so we don't keep the connection open the whole time.
Regarding the memory leak, I'm still not able to 100% confirm this, but when I used the Oracle.DataAccess.Client library instead of Oracle.ManagedDataAccess the memory usage reduced dramatically. So, I switched back to Oracle.DataAccess.Client.

P.S. I will update this answer in case of new information regarding these two issues, and contributions are very welcome, maybe I misunderstand something regarding how Oracle deals with the database connection.

Answer 2

I ran into the same problem. I solved it by changing the initialization of the OracleConnection.

from

var con = new OracleConnection(
    "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=SomePortHere))(CONNECT_DATA=(SERVER=SHARED)(SERVICE_NAME=anotherHost)))",
    new OracleCredential(userName,password));

to

var con = new OracleConnection(
    "USER ID=myuser;PASSWORD=mypwd;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=SomePortHere))(CONNECT_DATA=(SERVER=SHARED)(SERVICE_NAME=anotherHost)))");

To me, this different behavior looks like a bug.

As a result, i can no longer use OracleCredential and must store the password as a string. That's okay for me.

remark: SecureString shouldn't be used