RE: The good old ORA-01555 rollback too small
- From: Kip.Bryant@xxxxxxxxxx
- To: DGoulet@xxxxxxxx
- Date: Wed, 09 Feb 2005 09:56:58 -0800
Interesting concept. Any problems with lockwaits or other side effects?
Kip Bryant
|Michael,
| 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 [mailto:Michael.Kline@xxxxxxxxxxxx]=20
|Sent: Wednesday, February 09, 2005 8:16 AM
|To: oracle-l@xxxxxxxxxxxxx
|Subject: The good old ORA-01555 rollback too small
|ORA-01555: snapshot too old: rollback segment number 3 with name "RBS02"
|too small
|=20
|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.
|=20
|I would normally assume this would have been just fine.
|=20
|If not, is there any "locking" that can be done to stop the ORA-01555?
|=20
|(I still suspect some where along the line, someone tried to update
|while these jobs were running.)
|=20
|This has been running fine in the past. There are some new jobs being
|brought into production.
|=20
|Oracle is 8.1.7.4 on HP-UX. Rollback capacity is in the vicinity of 32
|GB and has never ran out yet.
|=20
|=20
|=20
|Michael Kline
|Database Administration
|SunTrust Technology Center
|1030 Wilmer Avenue
|Richmond, Virginia 23227
|Outside 804.261.9446
|STNet 643.9446
|Cell 804.744.1545
| <mailto:michael.kline@xxxxxxxxxxxx> michael.kline@xxxxxxxxxxxx=20
|************************************************=20
|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
|************************************************=20
|--
|//www.freelists.org/webpage/oracle-l
|--
|//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
