Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: The good old ORA-01555 rollback too small

RE: The good old ORA-01555 rollback too small

From: <>
Date: Wed, 09 Feb 2005 09:56:58 -0800
Message-id: <>

Interesting concept. Any problems with lockwaits or other side effects?

Kip Bryant


| You've got it right, someone did an update/delete somewhere
|along the way, committed & left. Suggestion try speeding up the queries
|that these programs use. Sometimes a specialized index is just the
|trick. Have a job that archives data from PeopleSoft's CM_ACCTG_LINE
|table, 500M rows today. They create a specialized index on the table at
|the start of the program, great for locking things that get in the way
|up, do their thing, and then drop the index. Not only does it make the
|ORA-01555 go away but it cut the run time down around 60%.

|Dick Goulet
|Senior Oracle DBA
|Oracle Certified 8i DBA
|-----Original Message-----
|From: Kline.Michael []=20
|Sent: Wednesday, February 09, 2005 8:16 AM
|Subject: The good old ORA-01555 rollback too small

|ORA-01555: snapshot too old: rollback segment number 3 with name "RBS02"
|too small

|Production got one of these last night from two huge jobs trying to run
|against a table in the range of 200M rows. They were reading this table
|based on perhaps months and codes to build another table. So perhaps job
|A was reading "base" to build "job_a_table" based on current month and a
|set of codes, and job B was reading "base" to build "job_b_table" based
|on a different set of criteria.


|I would normally assume this would have been just fine.


|If not, is there any "locking" that can be done to stop the ORA-01555?


|(I still suspect some where along the line, someone tried to update
|while these jobs were running.)


|This has been running fine in the past. There are some new jobs being
|brought into production.


|Oracle is on HP-UX. Rollback capacity is in the vicinity of 32
|GB and has never ran out yet.




|Michael Kline
|Database Administration
|SunTrust Technology Center
|1030 Wilmer Avenue
|Richmond, Virginia 23227
|Outside 804.261.9446
|STNet 643.9446

|Cell 804.744.1545
| <>
|The information transmitted is intended solely=20
|for the individual or entity to which it is =20
|addressed and may contain confidential and/or=20
|privileged material. Any review, retransmission,=20
|dissemination or other use of or taking action=20
|in reliance upon this information by persons or=20
|entities other than the intended recipient is=20
|prohibited. If you have received this email in=20
|error please contact the sender and delete the=20
|material from any computer. [ST:A234]=20


Received on Wed Feb 09 2005 - 13:01:02 CST

Original text of this message