Oracle monitor user

@monitor_user.sql


column name form a60
column sid form 999
column serial# form 999999 Heading "Serial"
column logon_time format a15
column program format a22
column username format a12
column idle_mins format 99999 Heading "Idle"
column object format a30

PROMPT
PROMPT Displaying All non background sessions
PROMPT

select sid,serial#,username,program,status
,to_char(logon_time,'DD-MON-RR hh24:mi') logon_time
,trunc(last_call_et/60) idle_mins
from v$session
where username is not null;

ACCEPT user PROMPT 'Enter Value for username > '

PROMPT
PROMPT User is running the following queries
select oc.sid, t.sql_text
from v$open_cursor oc, v$sqltext t
where t.hash_value = oc.hash_value and t.address=oc.address
and oc.user_name=UPPER('&user')
order by oc.sid, t.address, t.hash_value, t.piece
/

ACCEPT sid PROMPT 'Enter sid to refine (return for all) :'
ACCEPT stat PROMPT 'Filter on statistics (return for all) :'

select s.sid, sn.name, s.value
from v$sesstat s, v$statname sn, v$session sess
where s.sid=sess.sid
and s.statistic# = sn.statistic#
and sess.username= UPPER('&user')
and s.sid LIKE '&sid%'
and sn.name like '&stat%'
and s.value != 0
order by s.sid, sn.name
/

PROMPT
PROMPT Selecting V$SESSION_WAIT
PROMPT
column sid format 990
column seq# format 99990
column wait_time heading 'WTime' format 99990
column event format a30
column state forma a10 heading "State"
column seconds_in_wait form 999,999 heading secsinwait
column p1 format 9999999990
column p2 format 9999999990
column p3 format 9990

select event,p1,p2,p3,wait_time,seconds_in_wait from v$session_wait
where sid = '&sid';

PROMPT
PROMPT Selecting V$SESSION_EVENT
PROMPT
select event,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT, MAX_WAIT
from v$session_event where sid = '&sid';


PROMPT
PROMPT Selecting V$SESS_IO
PROMPT
select * from v$sess_io where sid = '&sid';




SQL> @monitor_user

Displaying All non background sessions


SID Serial USERNAME PROGRAM STATUS LOGON_TIME Idle
---- ------- ------------ ---------------------- -------- --------------- ------
1 1831 SYS sqlplus@apt-amd-02 (TN INACTIVE 19-OCT-11 10:15 1463
S V1-V3)

42 777 SYS sqlplus@apt-amd-02 (TN ACTIVE 20-OCT-11 12:55 0
S V1-V3)


Enter Value for username > SCOTT

User is running the following queries
old 4: and oc.user_name=UPPER('&user')
new 4: and oc.user_name=UPPER('SCOTT')

no rows selected

Enter sid to refine (return for all) :42
Filter on statistics (return for all) :
old 5: and sess.username= UPPER('&user')
new 5: and sess.username= UPPER('SCOTT')
old 6: and s.sid LIKE '&sid%'
new 6: and s.sid LIKE '42%'
old 7: and sn.name like '&stat%'
new 7: and sn.name like '%'

no rows selected


Selecting V$SESSION_WAIT

old 3: where sid = '&sid'
new 3: where sid = '42'

EVENT P1 P2 P3 WTime secsinwait
------------------------------ ----------- ----------- ----- ------ ----------
SQL*Net message to client 1650815232 1 0 -1 0


Selecting V$SESSION_EVENT

old 2: from v$session_event where sid = '&sid'
new 2: from v$session_event where sid = '42'

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT
------------------------------ ----------- -------------- ----------- ------------ ----------
Disk file operations I/O 10 0 0 .02 0
control file sequential read 14 0 0 0 0
log file sync 2 0 5 2.51 3
db file sequential read 48 0 47 .98 2
db file scattered read 4 0 3 .77 1
utl_file I/O 86 0 4 .05 3
SQL*Net message to client 434 0 0 0 0
SQL*Net message from client 433 0 29762 68.73 8104
SQL*Net break/reset to client 4 0 0 .01 0
events in waitclass Other 91 91 0 0 0

10 rows selected.


Selecting V$SESS_IO

old 2: from v$sess_io where sid = '&sid'
new 2: from v$sess_io where sid = '42'

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES OPTIMIZED_PHYSICAL_READS
---- ---------- --------------- -------------- ------------- ------------------ ------------------------
42 90855 465475 76 195 0 0