SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Count the number of active sessions in an interval ?

741795Oct 25 2012 — edited Oct 26 2012

Hi Folks;

I executed these three requests on my Oracle 11g r2 database but i get three differents results !?!

SQL>  SELECT count(*) FROM v$session WHERE to_char(LOGON_TIME,'HH24:MI') BETWEEN '10:00' AND '12:00' AND LOGON_TIME>sysdate-1/2 ;
 
  COUNT(*)
----------
        45

SQL>  SELECT count(*) AS nbconx
 FROM (
 SELECT DISTINCT SESSION_ID
 FROM v$active_session_history
 WHERE to_char(SAMPLE_TIME,'HH24:MI') BETWEEN '10:00' AND '12:00'
AND SAMPLE_TIME>sysdate-1/2
 ); 
    NBCONX
----------
       827

SQL>  SELECT count(*) AS nbconx FROM (
 SELECT DISTINCT SESSION_ID
 FROM  dba_hist_active_sess_history WHERE to_char(SAMPLE_TIME,'HH24:MI') BETWEEN '10:00' AND '12:00' AND SAMPLE_TIME>sysdate-1/2
 );  
 
    NBCONX
----------
      971

In fact, all i want to know is : "How many active session have been opened on the database in a certain interval of a day".
Which of these request is THE good one ? Or are they all bad ?

Help please.
Thanks for your knowledge

This post has been answered by Stew Ashton on Oct 25 2012

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 23 2012

Added on Oct 25 2012

#11g, #sql

7 comments

30,773 views