Managing Rollback segment in database

Hi all ,

I am getting error during creation of table in database and the error is as follows :

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

When I go ahead to create the rollback segment for USERS tablespace,It occur some error again which is below

ORA-30574: Cannot create rollback segment in tablespace with AUTO segment space
management

Can somebody help and suggest me regarding this.
Thanks.

The Rollback segment in database :

The Rollback segments,which is an Database object which is used to store the undo data of database after any transaction which causes the changes in data.

Before creating the rollback segment for a tablespace, make sure database parameter is not configured with UNDO_MANAGEMENT=AUTO because this parameter requires the undo management would be performed automatically.

And one Thing is more important here which will solve the problem that you have specify above.If your database has the SYSTEM tablespace which is locally managed then you can't create rollback segment .In this case,you need to use the UNDO_MANAGEMENT=AUTO parameter for your database or you have to create a locally managed tablepspace and then use that tablespace to create the rollback segment

Creating Rollback segment in database :

To create the rollback segment for your database,you have to specify a reserve tablespace for its functioning which depends on your SYSTEM tablespace behavior.

Code:

---

CREATE TABLESPACE Rollbsc_tabs
  DATAFILE 'Rollbsc_tabs.dbf' SIZE 20M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 120K;

---

And then create a rollback segment which would be stored and manged by above created tablespace.

Code:

---

CREATE ROLLBACK SEGMENT Rollbsc_frst
  TABLESPACE Rollbsc_tabs;

---

PUBLIC Rollback segment :

During the creation of ROLLBACK segment,another option is specified according to use of rollback segment.The PUBLIC option is used to specify the rollback segment that will be public and used by any instance.

You can avoid this clause,then the rollback segment would be used as private for the current instance for which ROLLBACK_SEGMENTS parameter is configured.The syntax would be as follows to create the rollback segment :

Code:

---

CREATE [ PUBLIC ] ROLLBACK SEGMENT rollback_segment
  [ { TABLESPACE tablespace | storage_clause }
      [ TABLESPACE tablespace | storage_clause ]...
  ];

---

Dropping Rollback segment :

Using DROP ROLLBACK segment,you can drop the rollback segment from the database,During removal of rollback segment,the space occupied by rollback segment is returned back to the tablespace :

To drop the rollback segment :

Code:

---

DROP ROLLBACK SEGMENT <name_of_rollbacksegment>;

---