Results 1 to 5 of 5

Thread: Managing Rollback segment in database

  1. #1
    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.

  2. #2
    Join Date
    Apr 2008
    Posts
    1,949

    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

  3. #3
    Join Date
    May 2008
    Posts
    2,015

    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;

  4. #4
    Join Date
    Apr 2008
    Posts
    2,010

    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 ]...
      ];

  5. #5
    Join Date
    May 2008
    Posts
    2,302

    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>;

Similar Threads

  1. Managing the access control in database
    By Carnie in forum Software Development
    Replies: 4
    Last Post: 19-02-2010, 09:03 PM
  2. Managing users in database
    By LaMarcus in forum Software Development
    Replies: 4
    Last Post: 02-02-2010, 05:46 PM
  3. Managing oracle 9i Database
    By Landan in forum Software Development
    Replies: 3
    Last Post: 21-01-2010, 10:05 AM
  4. set single_user with rollback immediate
    By Abalakin in forum Software Development
    Replies: 3
    Last Post: 06-06-2009, 07:46 PM
  5. TCP segment of a reassembled PDU
    By Will in forum Windows Server Help
    Replies: 0
    Last Post: 16-11-2006, 11:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •