-------------------Sniped idle session--------------
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON_before, SID, v$session.SERIAL#, v$process.SPID OS_PROCESS, v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE FROM v$session, v$process
WHERE ((v$session.paddr = v$process.addr) AND (status = 'SNIPED'))
ORDER BY logon_time ASC;
-------------------------Last activity / work time ----------
select username,to_char(logon_time, 'DD-Mon-YYYY HH24:MI:SS') Logon_time,last_call_et,
to_char(sysdate-(last_call_et/(60*60*24)),'hh24:mi:ss') last_work_time
from v$session
where username is not null;
--------- Monitor Per Session UGA, PGA UGA Max, PGA Max Usage----------------
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC;
--------- Monitor Per Session UGA (User Global Area), PGA(Process Global Area) Usage----------------
SELECT
e.SID,
e.username,
e.status,
a.UGA_MEMORY,
b.PGA_MEMORY
FROM
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' UGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session uga memory') a,
(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' PGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b,
v$session e
WHERE
e.sid=a.sid
AND
e.sid=b.sid
ORDER BY
e.status,
a.UGA_MEMORY desc;
--------- Monitor Per Session UGA, PGA and Cursor Usage----------------
set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1
select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;
--------------------Determine PGA and Process Memory in use by Process------------------------
set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"
select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;
-------------Session I/O By User---------------------------
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
----------------CPU Usage By Session-------------------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
order by VALUE desc;
----------------Resource Usage By User-------------------------
select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME;
--------------Session Stats By Session-------------
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc;
----------Cursor Usage By Session-------------
select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls" ;
---------------------------User Hit Ratios---------------------
select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS));
--------------------------------generate sql to kill a sessoin. See genDisconnect.sql
accept killme prompt 'User to Kill > '
set ver off
select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' as sql_to_run from V$PROCESS p, V$SESSION s
where s.paddr = p.addr and s.username = upper ('&killme');
User to Kill > UIBADUSER
SQL_TO_RUN
---------------------------------------------------------
alter system disconnect session '296,3841' immediate;
--list old sessions to "disconnect", then generate sql to disconnect them
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
set echo off feedback off heading off
select '--run sql below to disconnect sessions above' from dual;
select 'alter system disconnect session '||chr(39)||sid||','||s.serial#||chr(39)||' immediate;' as sql_to_run from V$PROCESS p,V$SESSION s
where s.paddr = p.addr and s.username is not null and s.status = 'INACTIVE' and s.osuser = 'oracle'
and s.machine like 'banformprod5%' and s.logon_time < sysdate-1;
select '' from dual;
exit
OSUSER SPID USERNAME SID SERIAL# START_TIME STATUS MACHINE MODULE
------------ -------- ------------------------- ----- -------- -------------------- -------- -------------------- ----------
oracle 11911 AWILS1 1317 54857 Thu 25 Aug 07:18:55 INACTIVE banformprod5.admin.u SHACRSE
----------------------run sql below to disconnect sessions above
alter system disconnect session '1317,54857' immediate;
------------------------show current users. See showUsers.sql
set echo off feedback off heading off
set pagesize 0
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select 'Show users ordered by logon_time, username' from dual;
select 'OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE' from dual;
select '------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------' from dual;
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null order by logon_time,1;
Show users ordered by logon_time, username
OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE
------------ -------- ------------------------ ----- -------- ------------------- --------- -------------------- ----------
submit 29273 JOBRUNNER 2 19 332 Sat 20 Aug 04:01:07 ACTIVE servername.dns.netw SFRPIPE
oracle 28413 SYS 26 452 Mon 22 Aug 15:31:55 INACTIVE servername.dns.netw sqlplus@se
johnny 14223 TLJOHN 37 710 Tue 23 Aug 09:03:36 INACTIVE TREE\USER1 TOAD 7.6.0
janedoey 22345 USER01 28 1462 Tue 23 Aug 12:44:00 INACTIVE TREE\USER1 designer.e
oracle 29986 OPS$ORACLE 18 219 Tue 23 Aug 13:01:01 ACTIVE servername.dns.netw SQL*Plus
-------------------generate SQL to disconnect blocking users.
SELECT 'alter system disconnect session '''||s.sid||','||s.serial#||'''immediate;' as "run_this_sql" FROM sys.x$kglpn p, sys.v_$session s,
(select kglhdadr, kglnaobj FROM sys.x$kglob WHERE kglhdadr in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')) k
WHERE p.kglpnuse = s.saddr AND kglpnhdl in
(SELECT p1raw FROM sys.v_$session_wait WHERE event = 'library cache pin' AND state = 'WAITING')
and k.kglhdadr = kglpnhdl and kglpnmod=3 order by kglpnmod;
run_this_sql
--------------------------------------------------------------------------------------
alter system disconnect session '853,44482'immediate;
------------------find the number of current commits occuring.
select command,count(command) from V$SESSION where command = '44' group by command order by 1;
USERNAME COUNT
-------------------- -----
JOBSUMITTER 37680
VISITOR 3488
WORKER 2839
TESTER 1793
DBSNMP 11
SYS 740
-----------------------show "stale" connections. showStale.sql---------------------------
set recsep off
set feedback off
set pages 1000
set wrap off
define l1='Stale Sessions (showStale.sql)'
define l2='==================================='
ttitle left l1 skip 1 left l2 skip 2
col sid_serial format a12 heading 'SID,SERIAL#'
col username format a10 heading 'User'
col osuser format a10 heading 'OS User'
col stats format a8 heading 'Status'
col program format a20 heading 'Program'
col module format a30 heading 'Module'
column last_call format 9999 heading 'Last|Call|(Days)'
select to_char(logon_time,'Dy dd Mon HH24:MI') logon_time,sid || ',' || serial# sid_serial,
username,osuser,trunc(last_call_et/60/60/24) last_call,status,program,module from V$SESSION
where type != 'BACKGROUND' and username is not null and trunc(last_call_et/60/60/24) > 1 order by 5;
exit
Last
Call
LOGON_TIME SID,SERIAL# User OS User (Days) STATUS Program Module
---------------- ------------ ---------- ---------- ------ -------- -------------------- ------------------------------
-----------------------show how users are logged on------------
select program,count(program) from V$SESSION group by program order by program;
PROGRAM COUNT(PROGRAM)
-------------------------------------------------- --------------
