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

_____________________________________________________________________________________________________________________

Website Stats