_____________________________________________________________________________________________________________________
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.
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