This question is related to Oracle and .net core

Think of this as you are working on a web application that connects to an Oracle database. Every time a user makes a request to the application, it establishes a connection to the Oracle database to retrieve or insert data. Under heavy load, after multiple requests, users sometimes get the following exception:
 

 
ORA-01000: maximum open cursors exceeded
could not allocate new oci handle of type statement.

This exception occurs because the application has too many open database connections or cursors that haven't been disposed of correctly. 

basic example in which the issue might arise due to improper connection management:

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;

public class OracleDbConnectionExample
{
    private string _connectionString = "User Id=your_user;Password=your_password;Data Source=your_oracle_db";

    public void GetDataFromDatabase()
    {
        OracleConnection conn = new OracleConnection(_connectionString);
        try
        {
            conn.Open();
            
            // Creating and executing a command without disposing of it properly
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM some_table";
            OracleDataReader reader = cmd.ExecuteReader();
            
            while (reader.Read())
            {
                Console.WriteLine(reader.GetString(0)); // Assuming first column is string
            }

            // Forgetting to close reader and command
            // reader.Close(); 
            // cmd.Dispose(); // Missing disposal of command
        }
        catch (OracleException ex)
        {
            Console.WriteLine($"Oracle error: {ex.Message}");
        }
        finally
        {
            // Forgetting to close the connection
            // conn.Close(); 
        }
    }
}

The conn.Close() in the finally block is missing, which leads to open connections being left around. OracleCommand and OracleDataReader are not disposed of, leading to unfreed resources.

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;

public class OracleDbConnectionExample
{
    private string _connectionString = "User Id=your_user;Password=your_password;Data Source=your_oracle_db";

    public void GetDataFromDatabase()
    {
        using (OracleConnection conn = new OracleConnection(_connectionString))
        {
            try
            {
                conn.Open();
                
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM some_table";
                    
                    using (OracleDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader.GetString(0)); // Assuming first column is string
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine($"Oracle error: {ex.Message}");
            }
        }
    }
}

I think this issue often arises when:

• There is an inadequate closing or disposing of database connections or commands.
• The application opens too many connections and doesn’t release them in time.
• Connection pooling is not configured correctly, causing the application to run out of resources.
• Long-running or failed queries leave orphaned OCI handles.

When app pool is refresh it works

Without any more information,, it's hard to understand the issue. It might be because the connection is not closed!!! But again, this is an assumption that we have no support for!