Join dba_extents and dba_rollback_segs : dba_extents « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. dba_extents

SQL>
SQL> SELECT b.segment_name, status, sum(bytes) sum_bytes
  2  FROM   dba_extents a, dba_rollback_segs b
  3  WHERE  a.segment_name = b.segment_name
  4  and    rownum < 50
  5  AND    a.segment_type = 'ROLLBACK'
  6  GROUP BY b.segment_name, status
  7  ORDER BY status DESC, b.segment_name;

SEGMENT_NAME                   STATUS            SUM_BYTES
------------------------------ ---------------- ----------
SYSTEM                         ONLINE               393216

SQL>
SQL>