oracle-wiki.net - Show Rollback Stats

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

Show Rollback Stats

-- Displays rollback stats.

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

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

Notes

Use this script to review rollback segment sizing and activity. It reports the current size, configured optimal size, shrink history, wrap count, and extent allocation statistics for each rollback segment, providing a quick overview of rollback segment utilisation and behaviour.

Use it to identify rollback segments that are frequently extending or shrinking, investigate unusually high rollback activity, and assess whether rollback segments are appropriately sized for the database workload. The report can also help highlight inefficient space management or changing transaction patterns that may warrant further investigation.

Published on

Published 9th July 2026

Page updated

Google Sites

Report abuse