Rollback Segment Information

SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK ON
SET PAGES 80
SET LINES 132

REM
REM
REM || Title : show_rollback_segments.sql
REM ||
REM || Purpose : Script which display basic rollback info
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM


COLUMN tablespace_name FORMAT A12 HEADING 'Tablespace'
COLUMN segment_name FORMAT A10 HEADING 'Segment'
COLUMN name FORMAT A10 HEADING 'Segment'
COLUMN pub FORMAT A3 HEADING 'Public'
COLUMN status FORMAT A8 HEADING 'Status'
COLUMN extents FORMAT A4 HEADING '#|Ext'
COLUMN max_extents FORMAT A6 HEADING 'Max #|Ext'
COLUMN next_extent FORMAT A6 HEADING 'Next|Ext MB'
COLUMN xacts FORMAT A4 HEADING 'Act|Tx'
COLUMN optsize FORMAT A7 HEADING 'Optimal|size MB'
COLUMN Hours FORMAT 999,999 HEADING 'Hours'


BREAK ON tablespace_name

SPOOL rollback_segs_info.lst

PROMPT
PROMPT
PROMPT Rollback Segments
PROMPT
PROMPT

SELECT rbs.tablespace_name,
rbs.segment_name,
DECODE( rbs.owner, 'PUBLIC', ' Y ', ' N ') "Pub",
rbs.status,
TO_CHAR( rst.xacts, 999) xacts,
TO_CHAR( smt.bytes / 1048576, 99999.9) "Size MB",
TO_CHAR( rst.optsize / 1048576, 9999.9) optsize,
TO_CHAR( rbs.next_extent / 1048576, 999.9) next_extent,
TO_CHAR( smt.extents, 999) extents,
DECODE( SIGN( rbs.max_extents - 10000)
, 1, ' Unltd'
, TO_CHAR( rbs.max_extents, 99999)
) max_extents
FROM dba_rollback_segs rbs,
dba_segments smt,
v$rollname rnm,
v$rollstat rst
WHERE rbs.segment_name = rnm.name
AND rbs.segment_name = smt.segment_name
AND smt.segment_type = 'ROLLBACK'
AND rnm.usn = rst.usn
ORDER BY rbs.segment_name
/

PROMPT
PROMPT
PROMPT Rap Round for Rollback Segs
PROMPT
PROMPT


SELECT n.name,
round((sysdate-to_date(i1.startup_time))/(s.writes/s.rssize),1) "Hours"
FROM v$instance i1,
v$instance i2,
v$rollname n,
v$rollstat s
WHERE n.usn = s.usn
AND s.status = 'ONLINE'
/

PROMPT
PROMPT
PROMPT Rollback Users Transaction
PROMPT
PROMPT

COLUMN rr HEADING 'RB Segment' FORMAT a18
COLUMN us HEADING 'Username' FORMAT a15
COLUMN os HEADING 'OS User' FORMAT a10
COLUMN te HEADING 'Terminal' FORMAT a10


SELECT r.name rr,
NVL(s.username,'no transaction') us,
s.osuser os,
s.terminal te
FROM v$lock l,
v$session s,
v$rollname r
WHERE l.sid = s.sid(+)
AND TRUNC(l.id1/65536) = r.usn
AND l.type = 'TX'
AND l.lmode = 6
ORDER BY r.name
/

SPOOL OFF

SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 60

CLEAR BREAKS
CLEAR COL
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.