Free space in rollback segments?

From: Jeff Boes <jboes_at_qtm.net>
Date: 1997/02/12
Message-ID: <5dteth$3gd@lana.zippo.com>#1/1

Our database (2 GB data, 2 GB indexes) appears to be running out of rollback segment space. My predecessor has some cron scripts which monitor free space, and the scripts have suddenly started complaining about our rollback segment tablespace. My problem: I'm not a very experienced DBA, and this appears to be a serious problem, but it is not addressed in any of the documentation I've been able to uncover.

Here's some of the pertinent information (then my conclusions, so you won't think of me as a lazy slacker):

SQL> l

  1          SELECT
  2                  SUBSTR(DS.SEGMENT_NAME,1,22) R_SEGMENT,
  3                  SUBSTR(DS.TABLESPACE_NAME,1,20) TABLESPACE,
  4                  DS.BLOCKS,
  5                  DS.EXTENTS,
  6                  DRS.STATUS
  7          FROM DBA_SEGMENTS DS,DBA_ROLLBACK_SEGS DRS
  8          WHERE DS.SEGMENT_NAME = DRS.SEGMENT_NAME
  9*         ORDER BY 1

SQL> / R_SEGMENT TABLESPACE BLOCKS EXTENTS STATUS

---------------------- --------------- ---------- ---------- ----------------
R00                    MMXRBS               24320         18 ONLINE
R01                    MMXRBS                1820          7 ONLINE
R02                    MMXRBS                2860         11 ONLINE
R03                    MMXRBS                5200         20 ONLINE
R04                    MMXRBS               23140         89 ONLINE
R05                    MMXRBS                1560          6 ONLINE
R06                    MMXRBS               31460        121 ONLINE
R07                    MMXRBS               12740         49 ONLINE
R08                    MMXRBS                4160         16 ONLINE
R09                    MMXRBS                1820          7 ONLINE
R10                    MMXRBS               31460        121 ONLINE
SYSTEM                 SYSTEM                  30          3 ONLINE

12 rows selected.

SQL> select * from dba_free_space
2 where TABLESPACE_NAME = 'MMXRBS'
3 /

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS

-------------------- ---------- ---------- ---------- ----------
MMXRBS                       11      38262    1138688        139
MMXRBS                       10      60702     811008         99
MMXRBS                        6      41582     155648         19

1 select * from dba_data_files
2* where TABLESPACE_NAME = 'MMXRBS'
SQL> / FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS

--------------------- --------------- ---------- ------ ---------
rollback1.dbf       6 MMXRBS           340787200  41600 AVAILABLE
rollback2.dbf      10 MMXRBS           498073600  60800 AVAILABLE
rollback3.dbf      11 MMXRBS           314572800  38400 AVAILABLE

What I've figured out so far:

The RBS is fragmented, but that's not bad.
There aren't any long-running queries or heavy-duty transactions in progress.
The database was bounced 3 days ago; the scripts started reporting the problem Monday evening.
Performance isn't (currently) suffering.

So what is the deal here? The DB appears to have plenty of RBS, and not that many of them are in use. Why is there so little free space in the tablespace? Received on Wed Feb 12 1997 - 00:00:00 CST