 | |
ORA - 01555 rollback segment too old
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
ORA - 01555 rollback segment too old -
07-08-2003
, 08:52 PM
#2
| |||
| |||
|
|
Hi all I have a database which is about 500GB in size, a tablespace for the ROLLBACK which is 40GG with 20 rollback @ 780MB each, then we introduce partition tables after about 2 weeks we start getting "ORA-01555 rollback segment RBS1 too old, too small" I called oracle support on this they are useless, they are just giving me notes to read, which most of them do not apply (I have a data warehouse db). I don't know what to do anymore? currently we are planning to hard code a rollback to this job which fails, which drop and recreates a partition table with 2 partitions, is there any issues with rollback segments with partition tables? should I increase the rollback's from 20 to 30 rollback segments? any ideas would be great, I'm running out of ideas as what to do next Thanks Maria |
What does the job do (i.e. how does it recreate the partition table ... anything else it does?)?
What Oracle version?
Do you have optimal set on the rollback segments?
What kind of activity is happening in the database when the job fails?
post code...
Are you getting 1555 error (snapshot too old) or are you getting "rollback segment too small"?
According to your post you seem to be running a weird database which gives you the mix of the two errors.
Research Google and metalink .. this is a well documented/commented on error.
Anurag
#3
| |||
| |||
|
|
Are you getting 1555 error (snapshot too old) or are you getting "rollback segment too small"? According to your post you seem to be running a weird database which gives you the mix of the two errors. |
my bad ... your error statement was correct. Tells you how long its been since I've seen this error.Anurag
#4
| |||
| |||
|
|
"Maria" <evoradba (AT) yahoo (DOT) ca> wrote in message news:351fd9d6.0307081752.72ea3340 (AT) posting (DOT) google.com... Hi all I have a database which is about 500GB in size, a tablespace for the ROLLBACK which is 40GG with 20 rollback @ 780MB each, then we introduce partition tables after about 2 weeks we start getting "ORA-01555 rollback segment RBS1 too old, too small" I called oracle support on this they are useless, they are just giving me notes to read, which most of them do not apply (I have a data warehouse db). I don't know what to do anymore? currently we are planning to hard code a rollback to this job which fails, which drop and recreates a partition table with 2 partitions, is there any issues with rollback segments with partition tables? should I increase the rollback's from 20 to 30 rollback segments? any ideas would be great, I'm running out of ideas as what to do next Thanks Maria Maria 1555 errors are commonly caused by issuing commits while a cursor is left open. Subsequent fetches are then in danger of receiving a 1555. If this is your case rewriting the code to close and re-open the cursor at each commit will probably solve it. I realise this may be difficult but it ensures that the cursor is based on the actual state of the database and not a snapshot that may be trampled on at any moment. HTH eric |
#5
| |||
| |||
|
|
Oops Oracle veriosn is 8174 on solaris, the largest table is 20GB but the issue only started to happen when we introduce partition tables... does this mean that partition will take more of the RBS's then other's? also this job which fails drops and then re creates a partition, which means creating the DDL.. also includes a procedure, we tried to hard code a RBS but there are 5 jobs which depende on it... too complicated. Oracle support told me to keep trying the sizes of the rollback's, I did this select query, but I don't know how to get the " If Shrinks "Low" and Avesize "High" then, Optsize is OK" from the select query. I don't know what I'm missing on this query "Select a.name, b.extents, b.rssize, b.optsize, b.shrinks, b.aveshrink, b.aveactive, b.wraps, b.extends, b.status from v$rollname a, v$rollstat b where a.usn = b.usn;" If Shrinks "High" and Avesize "High" then, Optsize = too low, increase optimal If Shrinks "High" and Avesize "Low" then, Optsize = too low, increase optimal If Shrinks "Low" and Avesize "Low" then, Optsize = Too high, reduce optimal (unless nearly equal to AVEACTIVE) If Shrinks "Low" and Avesize "High" then, Optsize is OK Thanks Maria "Eric Parker" <eric.parker.spamless (AT) virgin (DOT) net> wrote "Maria" <evoradba (AT) yahoo (DOT) ca> wrote in message news:351fd9d6.0307081752.72ea3340 (AT) posting (DOT) google.com... Hi all I have a database which is about 500GB in size, a tablespace for the ROLLBACK which is 40GG with 20 rollback @ 780MB each, then we introduce partition tables after about 2 weeks we start getting "ORA-01555 rollback segment RBS1 too old, too small" I called oracle support on this they are useless, they are just giving me notes to read, which most of them do not apply (I have a data warehouse db). I don't know what to do anymore? currently we are planning to hard code a rollback to this job which fails, which drop and recreates a partition table with 2 partitions, is there any issues with rollback segments with partition tables? should I increase the rollback's from 20 to 30 rollback segments? any ideas would be great, I'm running out of ideas as what to do next Thanks Maria Maria 1555 errors are commonly caused by issuing commits while a cursor is left open. Subsequent fetches are then in danger of receiving a 1555. If this is your case rewriting the code to close and re-open the cursor at each commit will probably solve it. I realise this may be difficult but it ensures that the cursor is based on the actual state of the database and not a snapshot that may be trampled on at any moment. HTH eric |
As far as I'm concerned the no. 1 cause of this is fetchin from a cursor
after a commit when
that cursor was opened before the commit.
You may be able to find a "solution" by adjusting rollback segments but you
also
may find that your problem re-appears next week when there is a change to
the
data or user load.
Can you verify that you are not using cursors and commits (might be DDL
operation)
as I've descibed ?
Regards
eric
#6
| |||
| |||
|
|
Oops Oracle veriosn is 8174 on solaris, the largest table is 20GB but the issue only started to happen when we introduce partition tables... does this mean that partition will take more of the RBS's then other's? also this job which fails drops and then re creates a partition, which means creating the DDL.. also includes a procedure, we tried to hard code a RBS but there are 5 jobs which depende on it... too complicated. Oracle support told me to keep trying the sizes of the rollback's, I did this select query, but I don't know how to get the " If Shrinks "Low" and Avesize "High" then, Optsize is OK" from the select query. I don't know what I'm missing on this query "Select a.name, b.extents, b.rssize, b.optsize, b.shrinks, b.aveshrink, b.aveactive, b.wraps, b.extends, b.status from v$rollname a, v$rollstat b where a.usn = b.usn;" If Shrinks "High" and Avesize "High" then, Optsize = too low, increase optimal If Shrinks "High" and Avesize "Low" then, Optsize = too low, increase optimal If Shrinks "Low" and Avesize "Low" then, Optsize = Too high, reduce optimal (unless nearly equal to AVEACTIVE) If Shrinks "Low" and Avesize "High" then, Optsize is OK Thanks Maria Just forget about setting optimal. It is a pain which increases the |
rollback segs, wait until they go baloney and shrink them manually
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.