Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Rollback segment

    Hello
    Ihave two questions that bogg me from long time on our database
    Qn1)

    I am unable to create any rollback segment in any of the other tablespaces we have other than system tablespace.I get the follwing error
    Ora-30574 Cannot create rollback segment in Table space with AUTO segment space management?I know that creating in system table space is not recommended but i get the same error on all the rest of table spaces we have on our instance.Can anybody help me What does that mean and how to fix that problem?


    Qn2)

    ORA-01555: snapshot too old: rollback segment number 11 with name "ROLLBACK1" too small
    ORA-06512: at line 15
    I am encountering the above error when i was trying to exectute one of my scripts.Inorder to over come this error I have created a roll back segment with intial as 1024 K,optimal size of 1024K and next size of 1024K.

    Inorder to over come this error i modified the params to 1000M 1000M and optimal to 1000M as we have enough space.
    but still i get the same error as rollback segment too small
    Can anybody help me how can i fix this problem
    Any help is highly appreciated.
    Thanks
    Sreeni

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    visit http://asktom.com & do a keywork search on ORA-01555

    As a long shot, I suspect that you have a COMMIT within a long running cursor loop.

    If so, this is likely the cause of the error; so comment out the COMMIT!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    ORA-30574 Cannot create rollback segment in tablespace with AUTO segment space management

    Cause: A rollback segment is being created in a tablespace that was created with AUTO segment space management.

    Action: Create the rollback segment in a different tablespace.

    Try

    SELECT tablespace_name, segment_space_management FROM user_tablespaces;

    and see whether you have any tablespaces that do not have segment space management = AUTO.

    I agree with anacedent - ORA-01555 is usually caused by slow queries against data that is being updated, often by the the same process if it COMMITs as it goes along (a CURSOR FOR loop will often do the trick) - if there are periodic COMMITs, take them out or at least make them less frequent.
    Last edited by WilliamR; 11-08-04 at 19:22.

Posting Permissions

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