SQL Script Monitor Rollback Segments

Updated : 03-Apr-2002
Version : 2.0

Description

This script reports key indicators in determining whats happening with rollback segments. The first statement lists the username and the statement type, the second lists any locks and the third reports how long it's been since the rollback segment last wrap. If the segment if wrapping very frequently, you should review the size and storage parameters on your rollback segments.

Parameters

None.

SQL Source

set pages 200
col user0 form a15
col comm0 form a15
col name0 form a10
col extents0 form 999 Heading "Extents"
col shrinks0 form 999 Heading "Shrinks"
col waits form 9999 heading "Wraps"

select 
       rn.name    name0,
       s.username user0,
       r.rssize ,
       r.waits,
       r.extents  extents0, 
       r.shrinks shrinks0,
	 r.optsize,
       decode (s.command,1,'CREATE TABLE',
                         2,'INSERT',
                         3,'SELECT',
                         6,'UPDATE',
                         7,'DELETE',
                         9,'CREATE INDEX',
                        10,'DROP INDEX',
                        12,'DROP INDEX',
                        26,'LOCK TABLE',
                        44,'COMMIT',
                        45,'ROLLBACK',
                        46,'SAVEPOINT', 
                        48,'SET TRANSACTION', 
                        NULL, NULL,
                        'look it up '||to_char(s.command)) comm0
from v$session s, v$transaction t, v$rollstat r, v$rollname rn
where s.taddr (+) = t.addr 
and t.xidusn (+) = r.usn 
and rn.usn = r.usn 
order by rn.name
/
SELECT   rn.name name0
	 , p.pid
         ,p.spid 
         , NVL (p.username, 'NO TRANSACTION') user0
         , p.terminal
  FROM v$lock l, v$process p, v$rollname rn
  WHERE    l.sid = p.pid(+)
  AND      TRUNC (l.id1(+)/65536) = rn.usn
  AND      l.type(+) = 'TX'
  AND      l.lmode(+) = 6
  ORDER BY rn.name;

PROMPT
PROMPT  Time since last WRAP
PROMPT
select n.name
       , round( 
           24*((sysdate-startup_time) - trunc(sysdate-startup_time)) /  
                          (s.writes/s.rssize),1) "Hours" 
from v$instance ,v$rollname n,v$rollstat s 
where n.usn = s.usn 
and s.status = 'ONLINE'
/ 
spool off

Previous Oracle Version Links

Monitor Rollback Segments

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal