Oracle Rollback Segment Statistics Report

Displays statistics for rollback segments, including size, shrink activity, wraps, and extensions. Useful for monitoring rollback segment behavior and analyzing space usage patterns in legacy rollback segment environments.

oraclesqlmonitoring-alertsv1.0.0

0 stars0 downloads16 views0 comments

By OracleDba • Created 2/24/2026

Code

(28 lines)

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/rbs_stats.sql
-- Author       : Tim Hall
-- Description  : Displays rollback segment statistics.
-- Requirements : Access to the v$ & DBA views.
-- Call Syntax  : @rbs_stats
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT b.name "Segment Name",
       Trunc(c.bytes/1024) "Size (Kb)",
       a.optsize "Optimal",
       a.shrinks "Shrinks",
       a.aveshrink "Avg Shrink",
       a.wraps "Wraps",
       a.extends "Extends"
FROM   v$rollstat a,
       v$rollname b,
       dba_segments c
WHERE  a.usn  = b.usn
AND    b.name = c.segment_name
ORDER BY b.name;

SET PAGESIZE 14
SET VERIFY ON

Oracle Rollback Segment Statistics Report

Code

General Comments(0)