locked
1000s of Inactive session in Oracle RRS feed

  • Question

  • User349027298 posted

    There are 1000s of Inactive sessions are there in Oracle database. Some are even 1 month old.

    sql_text shows a particular query in 90% of the cases. Query is working fine without any slowness.

    OracleConnection is disposed properly.

    Any idea what can be reason ?

    Query used to find inactive sessions

    SELECT s.SID, s.STATUS, s.process, s.osuser, a.sql_text, p.program FROM v$session s, v$sqlarea a, v$process p WHERE s.PREV_HASH_VALUE = a.hash_value

    AND s.PREV_SQL_ADDR = a.address AND s.paddr = p.addr AND s.STATUS = 'INACTIVE'

    Wednesday, January 3, 2018 9:35 PM

All replies

  • User269602965 posted

    1.

    Each Oracle user is assigned a PROFILE to control things like password expiration and such.

    But each profile has a IDLE_TIME which by default is set to UNLIMITED.

    You can change this PROFILE setting to terminate session when user is idle for defined period of time, like 30 for limit 30 minutes.

    2. How do you dispose of your Oracle connections?  Perhaps you have a process that is exiting the subroutine before you close and dispose.  I have had best luck with TRY with USING-END USING CATCH END TRY

    Try
      Dim SQL = <SQL>
                  SELECT FROM {YourSchemaName}.NAME_LIST
                  WHERE FIRST_NAME LIKE :bindvarFIRST_NAME
                </SQL>
      Using conn As New OracleConnection(connectionString)
        Using cmd As New OracleCommand(SQL.Value, conn)
          cmd.Parameters.Clear()
          cmd.Parameters.Add("bindvarFIRST_NAME", OracleDbType.Varchar2, strFirstName, ParameterDirection.Input)
          conn.Open()
          cmd.ExecuteNonQuery()
        End Using
      End Using
    Catch ex As Exception
    End Try
    Thursday, January 4, 2018 2:16 AM
  • User349027298 posted

    It is a hibernate call. There are 1000s of Inactive sessions. 

    Why system is not re-using the inactive sessions?

    Whay System is creating new sessions every time and leaving them inactive.

    Some sessions are even 1 month old as well.

    Application code

    --------------------

    //NHibernate transaction class

    namespace NHibernate

    {

     public interface ITransaction : IDisposable

    {

    }

    }

    //tx is nHibernate's  ITransaction object

    try

    {

      if (tx != null && !tx.WasCommitted && !tx.WasRolledBack)

       {

         tx.Commit();

       }

    }

    finally()

    {

       tx.dispose();

    }

    Thursday, January 4, 2018 9:40 PM