The good old ORA-01555 rollback too small 2005-02-09 - By Goulet, Dick
None noted.=20
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-- --Original Message-- --
From: Kip.Bryant@(protected) [mailto:Kip.Bryant@(protected)]=20
Sent: Wednesday, February 09, 2005 12:57 PM
To: Goulet, Dick
Cc: oracle-l@(protected)
Subject: RE: The good old ORA-01555 (See ORA-01555.ora-code.com) rollback too small
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 (See ORA-01555.ora-code.com) 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@(protected)]=3D20
|Sent: Wednesday, February 09, 2005 8:16 AM
|To: oracle-l@(protected)
|Subject: The good old ORA-01555 (See ORA-01555.ora-code.com) rollback too small
|ORA-01555 (See ORA-01555.ora-code.com): snapshot too old: rollback segment number 3 with name
"RBS02"
|too small
|=3D20
|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.
|=3D20
|I would normally assume this would have been just fine.
|=3D20
|If not, is there any "locking" that can be done to stop the ORA-01555 (See ORA-01555.ora-code.com)?
|=3D20
|(I still suspect some where along the line, someone tried to update
|while these jobs were running.)
|=3D20
|This has been running fine in the past. There are some new jobs being
|brought into production.
|=3D20
|Oracle is 8.1.7.4 on HP-UX. Rollback capacity is in the vicinity of 32
|GB and has never ran out yet.
|=3D20
|=3D20
|=3D20
|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@(protected)> michael.kline@(protected)=3D20
|************************************************=3D20
|The information transmitted is intended solely=3D20
|for the individual or entity to which it is =3D20
|addressed and may contain confidential and/or=3D20
|privileged material. Any review, retransmission,=3D20
|dissemination or other use of or taking action=3D20
|in reliance upon this information by persons or=3D20
|entities other than the intended recipient is=3D20
|prohibited. If you have received this email in=3D20
|error please contact the sender and delete the=3D20
|material from any computer. [ST:A234]=3D20
|************************************************=3D20
|--
|http://www.freelists.org/webpage/oracle-l
|--
|http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
|
|
