SQL Script to Find out Current Running Active, Inactive Sessions Connected to the Database

This is the one of the most useful queries 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;

advertisements

 

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