How can I make Oracle session time length limit with ODP.NET C#

Question

I want to know how to Oracle kill session in limited length of time on ODP.Net.

Thease are what I tryed. 1.I made Oracle user profile set CONNECT_TIME = 1 min. 2.I made Oracle user with this profile. 3.I made C# Console AP with ODP as below. 4.Run AP. 5.Stop Afte conn.Open(); 6.Keep Wait till spend more than 1 min. 7.Continue Run AP. 8.Exception occurred on reader.Read(); ex.message is Oracle is already disconnected. And I can find Oracle Session is disappeared. This is fine.

The problem is If I don't Continue Run AP(#7), Then there is no Exception. Without Exception occurred Oracle didn't kill session.

using Oracle.ManagedDataAccess.Client;

class Test1
{
    static void Main()
    {
        using (OracleConnection conn = new OracleConnection("connect string"))
        {
            conn.Open();
            using (OracleCommand cmd = new OracleCommand("select id from test_table"))
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        Console.WriteLine(reader["id"]);
                }
            }
        }
    }
}

Answer 1

To automatically kill sessions that have been connected for longer than one minute, you need to set profile limits for both CONNECT_TIME and IDLE_TIME, and you must use (ENABLE=BROKEN) in the connection string. With those three settings, an old session will have its V$SESSION.STATUS set to KILLED and any relevant transactions will be rolled back.

The status of KILLED means that most of the session resources were freed. For example, any uncommitted transactions were rolled back. However, the session still exists in V$SESSION and Oracle has no built-in mechanism to completely destroy those sessions. If you absolutely need those rows removed, you'll need to either force the client to try to run a command, or you'll need to create a job to run a PL/SQL command or an operating system command. Some sources claim that Oracle's PMON process will eventually cleanup the sessions entirely, but I am not able to reproduce that behavior on 19c Enterprise Edition on Windows 10 Pro.

Killing Oracle sessions has always been kind of a pain, and trying too hard to kill sessions can cause problems. I recommend that you lower your expectations and accept that STATUS='KILLED' is good enough.

Below is some sample code for creating and applying the relevant profile, and how to connect with SQL*Plus. I'm not sure exactly how to format the connection for C# though.

create profile test_profile limit connect_time 1 idle_time 1;
create user test_user identified by test_user profile test_profile;

D:\>sqlplus test_user/test_user@"(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=TCP)(HOST=host_name_here)(PORT = 1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb.Home)))"