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;

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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google