Oracle SQL Query/Script: Find out Current Running Active, Inactive Sessions Connected to the Database.

_____________________________________________________________________________________________________________________

This is the one of the most useful query which is used by the DBAs on daily basis. Here I have provided multiple queries which can be used for finding out Active,
Inactive with RAC and Non-RAC databases.

1.  Query to Find basic session details

set pages 10000 lines 333

col MODULE for a30

col event for a40

col WAIT_CLASS for a40

SELECT sid, serial#,sql_id , program, module ,

event, status ,     wait_class,    seconds_in_wait

FROM   v$session;

2.  RAC-Query to Find basic session details

SELECT       sid, serial#,inst_id, sql_id , program, module , event,

status ,     wait_class,    seconds_in_wait

FROM         gv$session;

Are you Interested in Concurrent User Details

3.  Query to find the session details with SQL Text

SELECT s.inst_id, s.sid, s.username, T.SQL_TEXT

FROM   gV$SQLTEXT T, gV$SESSION S

WHERE  S.SQL_ADDRESS=T.ADDRESS

ORDER BY s.inst_id,S.SID, T.PIECE;

4.  Query to find the session details with OS Process ID (PID)

SELECT s.sid,s.inst_id, p.spid OS_PID,s.serial#, s.program, module,s.sql_id

FROM   gv$session s , gv$process p

WHERE  p.addr =s.paddr;

5.  Query to find count of sessions with status

SELECT status, count(*) no_of_session

FROM   v$session group by status;

STATUS   NO_OF_SESSION

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

ACTIVE             145

INACTIVE           601

6.  RAC-Query to find count of sessions with status

SELECT inst_id, status, count(*) no_of_session

FROM   gv$session

GROUP BY status, inst_id order by 1,2;

   INST_ID STATUS   NO_OF_SESSION

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

         1 ACTIVE             144

         1 INACTIVE           589

         2 ACTIVE             138

         2 INACTIVE           580

_____________________________________________________________________________________________________________________