4 Replies Latest reply: Mar 21, 2013 8:19 AM by 977635 RSS

    historical count of user sessions connected

      For Oracle 11.x, is there a way to look at how many sessions were connected at some time in the past (e.g., a week ago, or every Sunday at 3:00pm for past 6 weeks, etc.).
      I want to know how many sessions were connected, verses how many session were active.
      It seems this information should be located somewhere, but where?
      I looked at v$active_session_history, but I really don't know what I'm looking at there, and also, it doesn't show inactive connections.
        • 1. Re: historical count of user sessions connected
          If you are looking at v$active_session_history, you must be licensed to use the AWR. So by default you can generate AWR reports going back 8 days. If your DBA is any good he has probably extended the retention period to at least a month.
          • 2. Re: historical count of user sessions connected
            Pavan DBA
            until auditing is enabled or you write some logon and logoff triggers, i afraid it is not possible to find out previous session information.

            please read this link to know what is for v$active_session_history
            • 3. Re: historical count of user sessions connected
              You could create a table and run the following SQL using insert into utilising the scheduler when you need it for (alter the SQL as needed) :
              select machine, sysdate, status, sessions from (
                 select machine, null, status, count(*) sessions
                 from v$session
                 group by status, machine);
              Don't think you can get the information you are after from any historical view.

              Edited by: Freddie Essex on 21-Mar-2013 12:56
              • 4. Re: historical count of user sessions connected
                Thanks Paven.

                Session auditing is on by default (since database was created using dbca).

                It seems what I am looking for is not readily available without coming up with some kind of homegrown method using logon triggers along with v$audit_session, or something like that.
                I was looking for our current system anyway, not for the future, although it might be useful for the future if i create something now.