Creating Rollback Segment in Oracle with Example

Rollback segments

Two rollback segments are created per database. Below are a few handy examples for creating and changing rollback segments. Note that for some Oracle upgrades, you may need to extend the system rollback segment ( or increase its next extent size ).

/* example 1 */

CREATE ROLLBACK SEGMENT rbs01
TABLESPACE ts22
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 4M
MINEXTENTS 20 MAXEXTENTS 100) ;

ALTER ROLLBACK SEGMENT rbs01 ONLINE ;


/* example 2 */


CREATE ROLLBACK SEGMENT R05
TABLESPACE rb5
STORAGE ( INITIAL 100K NEXT 100K MAXEXTENTS 300 ) ;

ALTER ROLLBACK SEGMENT R05 ONLINE ;
   

Note:

Add this line to the initXX.ora file, to ensure the
rollback segments are brought online after each server restart:

      rollback_segments=(r01,r02,r03,r04)


Altering a Rollback Segment

ALTER ROLLBACK SEGMENT r03
STORAGE ( MAXEXTENTS 250 );


ALTER ROLLBACK SEGMENT r05 SHRINK TO 10M ;



/* example3, includes tablespace creates */


connect internal/oracle

CREATE TABLESPACE rbs01
DATAFILE '/data1/oradata/DB19/rbs01.dbf' SIZE 100M
MINIMUM EXTENT 10K
DEFAULT STORAGE (
  INITIAL 20K
  NEXT 500K
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0  )
PERMANENT
ONLINE ;

CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs01
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 10M
MINEXTENTS 20 MAXEXTENTS 999) ;

ALTER ROLLBACK SEGMENT rbs01 ONLINE ;



CREATE TABLESPACE rbs02
DATAFILE '/data1/oradata/DB19/rbs02.dbf' SIZE 100M
MINIMUM EXTENT 10K
DEFAULT STORAGE (
  INITIAL 20K
  NEXT 500K
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0  )
PERMANENT
ONLINE ;

CREATE ROLLBACK SEGMENT rbs02
TABLESPACE rbs02
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 10M
MINEXTENTS 20 MAXEXTENTS 999) ;

ALTER ROLLBACK SEGMENT rbs02 ONLINE ;


CREATE TABLESPACE rbs03
DATAFILE '/data1/oradata/DB19/rbs03.dbf' SIZE 100M
MINIMUM EXTENT 10K
DEFAULT STORAGE (
  INITIAL 20K
  NEXT 500K
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0  )
PERMANENT
ONLINE ;

CREATE ROLLBACK SEGMENT rbs03
TABLESPACE rbs03
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 10M
MINEXTENTS 20 MAXEXTENTS 999) ;

ALTER ROLLBACK SEGMENT rbs03 ONLINE ;