None noted.=20 Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -----Original Message----- From: Kip.Bryant@xxxxxxxxxx [mailto:Kip.Bryant@xxxxxxxxxx]=20 Sent: Wednesday, February 09, 2005 12:57 PM To: Goulet, Dick Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: The good old ORA-01555 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 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]=3D20 |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 |=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? |=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@xxxxxxxxxxxx> michael.kline@xxxxxxxxxxxx=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 |-- |//www.freelists.org/webpage/oracle-l |-- |//www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l