Thread: Rollback segment
11-08-04, 18:47 #1Registered User
- Join Date
- Nov 2004
Unanswered: Rollback segment
Ihave two questions that bogg me from long time on our database
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?
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.
11-08-04, 18:52 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
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.
11-08-04, 19:20 #3Registered User
- Join Date
- Sep 2004
- London, UK
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.
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.