Scripts - Sessions Related

To find the No. of Active and Inactive Users

set lines 136 
set pages 200 
column username format a18 
column machine format a18 
column program format a55 
COLUMN DUMMY NOPRINT 
COMPUTE SUM OF "NUM_CONN" ON DUMMY 
BREAK ON DUMMY 
select null dummy, inst_id, username, machine, program, status, count(*) "NUM_CONN" 
from gv$session group by inst_id, username, machine, program, status order by 7 asc
/

Find total active sessions,inactive sessions & logon time from each machine:

set lines 136 pages 999
col OSUSER for a20
col USERNAME for a20
col MACHINE for a30
col STATUS for a9
col MIN_DATE for a20
col MAX_DATE for a20
col CNT for 999
break on status skip 1
compute sum of CNT on status


SELECT OSUSER,
USERNAME,
MACHINE,
STATUS,
to_char(min(logon_time),'dd-MON-YYYY:HH24:mi:ss') MIN_DATE,
to_char(max(logon_time),'dd-MON-YYYY:HH24:mi:ss') MAX_DATE,
/* rpad(COUNT(*),3,' ') "CNT" */
COUNT(*) "CNT"
FROM V$SESSION
GROUP BY OSUSER,
USERNAME,
STATUS,
MACHINE
ORDER BY STATUS,
to_char(min(logon_time),'dd-MON-YYYY:HH24:mi:ss'),
to_char(max(logon_time),'dd-MON-YYYY:HH24:mi:ss')
/


To find SID , Serail and Status of all users:-

column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status
from v$session
where username is not null
/


Find what the Active sessions are executing

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/

Find how many sessions has been established from each machine

select distinct(MACHINE), count(*) from v$session
group by MACHINE
order by count(*)
/


Get the Session ID using the PID

select a.sid, a.serial#, a.process, a.program , c.sql_text
from v$session a, v$process b , V$sql c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value and a.sql_address = c.address and 
b.spid = &1
/

Eg. If a process utilizing more CPU or Memory then get the Process id of that process and give as input for the above script.

Find the spid (oracle process id) of the sid:

select s.username, s.sid session_id, s.serial#, p.spid, 
s.status,to_char(s.logon_time,'dd-mm-yy hh24:mi:ss') from v$session s, v$process p
where s.paddr = p.addr(+) and (s.username = upper('&username') 
or (s.sid = '&session_id' and s.serial# = '&serial_number'))
/

All sql which been executed currently
SQL> col osuser form a10;
SQL> col program form a31;
SQL> col logon_time form a21;

select b.osuser,b.program,b.status,to_char(b.LOGON_TIME,'dd/mm/yyyy hh:mi:ss') as logon_time,a.sql_text from v$sql a,v$session b where
b.sql_hash_value in (select a.hash_value from v$session where SCHEMANAME <> 'SYS' and sql_hash_value > 0 ) and b.sql_hash_value=a.hash_value
/

Find the sql_text from the sid

set pagesize 99
break on sid on serial#

select s.sid, s.serial#, t.sql_text from v$session s, v$sqltext t 
where s.sid = &sid and s.sql_address = t.address
and s.sql_hash_value = t.hash_value order by t.piece
/

Query to find what are the sessions are using undo and howmuch

SELECT s.SID, ((t.used_ublk) * (SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size')) / (1024 * 1024) as used_undo_mb
FROM v$transaction t, v$session s WHERE s.taddr = t.addr
/


Which sessions are using which undo segments with sql statements:-

select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text
from v$rollname a, v$rollstat b, v$session c, v$sqltext d,
v$transaction e
where a.usn = b.usn
and b.usn = e.xidusn
and c.taddr = e.addr
and c.sql_address = d.address
and c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece;

name xacts sid serial# username sql_text
RB1 1 5 33 USER1 delete from test1;
RB2 1 7 41 USER9 update orders set items = 5 where orderno = 555;



Users using High CPU

select s.username “Oracle User”,s.osuser “OS User”,i.consistent_gets “Consistent Gets”,i.physical_reads “Physical Reads”,s.status “Status”,s.sid “SID”,s.serial# “Serial#”,
s.machine “Machine”,s.program “Program”,to_char(logon_time, ‘DD/MM/YYYY HH24:MI:SS’) “Logon Time”,w.seconds_in_wait “Idle Time”, P.SPID “PROC”,
name “Stat CPU”, value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and ‘SQL*Net message from client’ = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like ‘%cpu%’
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc;