René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Rollback segments [Oracle]

Bringing rollback segments online

In order for a rollback segment to be useful, it must be online. You can achieve this with
ALTER ROLLBACK SEGMENT users ONLINE;
Beware, however, if you shutdown and restart the database, the rollback segment will be offline unless you added the rollback's segment name to the database's parameter file:
rollback_segments=(rb_users, rb1, rb2, rb3, rb4)

Rollback Segments and Extents

  • A transaction can only use one rollback segment to store its rollback record.
  • The same rollback segment can be used by multiple transactions.
  • No extent of a rollback segment is overwritten while a transaction that is not commited has written to that rollback segment.

Private vs Public rollback segments

The difference beetween private and public rollback segments is important only for the parallel server option: a public rbs can be brought online by any instance in a parallel server environment while a private one can only be aquired by the instance specifying the rbs in its init file through the rollback_segments.

Creating Rollback Segments

CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 1M next 1M minextents 20)
  tablespace rbs;
If there are too few rollback segments it will cause a " undo header contention" If the rollback segments are too small it will cause a " undo block contention"

Backup

Rollback Data must be backed up.

Owner of rollback segments

Rollback segments belong to sys, irrespective of who creates them. They are not accessible to ordinary users.