Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Managing Rollback segment in database

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 08-02-2010
Member
 
Join Date: Nov 2009
Posts: 56
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.

Reply With Quote
  #2  
Old 08-02-2010
Member
 
Join Date: Apr 2008
Posts: 1,947
The Rollback segment in database

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
Reply With Quote
  #3  
Old 08-02-2010
Member
 
Join Date: May 2008
Posts: 2,007
Creating Rollback segment in database

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;
Reply With Quote
  #4  
Old 08-02-2010
Member
 
Join Date: Apr 2008
Posts: 2,000
PUBLIC Rollback segment in database

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 ]...
  ];
Reply With Quote
  #5  
Old 08-02-2010
Member
 
Join Date: May 2008
Posts: 2,290
Dropping Rollback segment in database

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>;
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Managing Rollback segment in database"
Thread Thread Starter Forum Replies Last Post
Managing the access control in database Carnie Software Development 4 19-02-2010 09:03 PM
Managing users in database LaMarcus Software Development 4 02-02-2010 05:46 PM
Managing oracle 9i Database Landan Software Development 3 21-01-2010 10:05 AM
set single_user with rollback immediate Abalakin Software Development 3 06-06-2009 07:46 PM
TCP segment of a reassembled PDU Will Windows Server Help 0 16-11-2006 11:24 AM


All times are GMT +5.5. The time now is 11:59 PM.