Useful scripts for monitoring database

Hello Friend's,

In this post i am providing some useful scripts for monitoring database.

1) ------------------Find active sessions in oracle database-------------------------

set echo off 
set linesize 95 
set head on 
set feedback on 
col sid head "Sid" form 9999 trunc 
col serial# form 99999 trunc head "Ser#" 
col username form a8 trunc 
col osuser form a7 trunc 
col machine form a20 trunc head "Client|Machine" 
col program form a15 trunc head "Client|Program" 
col login form a11 
col "last call" form 9999999 trunc head "Last Call|In Secs" 
col status form a6 trunc 
select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser, 
substr(program||module,1,15) program,substr(machine,1,22) machine, 
to_char(logon_time,'ddMon hh24:mi') login, 
last_call_et "last call",status 
from gv$session where status='ACTIVE' 
order by 1 
/

2) ----------------Find waitevents in database--------------------

select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser, 
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine, 
a.event,a.p1,b.sql_hash_value 
from v$session_wait a,V$session b 
where b.sid=a.sid 
and a.event not in('SQL*Net message from client','SQL*Net message to client', 
'smon timer','pmon timer') 
and username is not null 
order by 6 
/

3)----------------Find the temp usage of the sessions------------------

SELECT b.tablespace, 
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, 
a.inst_id as Instance, 
a.sid||','||a.serial# AS sid_serial, 
NVL(a.username, '(oracle)') AS username, 
a.program, 
a.status, 
a.sql_id 
FROM gv$session a, 
gv$sort_usage b, 
gv$parameter p 
WHERE p.name = 'db_block_size' 
AND a.saddr = b.session_addr 
AND a.inst_id=b.inst_id 
AND a.inst_id=p.inst_id 
ORDER BY temp_size desc 
/

************************************************************************

select a.tablespace_name tablespace,
        d.TEMP_TOTAL_MB,
         sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
        d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
        (
          select   b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
          from     v$tablespace b, v$tempfile c
          where    b.ts#= c.ts#
          group by b.name, c.block_size
        ) d
where    a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;

4)-----------------Find sessions generating lot of redo--------------------

set lines 2000 
set pages 1000 
col sid for 99999 
col name for a09 
col username for a14 
col PROGRAM for a21 
col MODULE for a25 
select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id 
from v$sesstat s join v$statname n on n.statistic# = s.statistic# 
join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by 
redo_mb desc;

5)------------------Find sessions generating undo-----------------------

select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks 
from v$session a, v$transaction b 
where a.saddr=b.ses_addr ;

Hope This Helps.

Regards

Sultan Khan

• Tweet
• Share
• Share
• Share
• Share

Related Post