Hi Barb, Good, You need to prevent extent reuse in ALL online rollback segments because your long-running transaction may need to read from rollback segments other than the one to which it is writing in order get a consistent snapshot of the database at the time that the job started.
@ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -----Original Message----- Sent: Friday, 19 October 2001 7:10 To: Steve Adams Steve: Thanks! I'll definitely do that. (I didn't think I needed to, since I'm doing the "set transaction" stuff.) That's what I get for modifying your original stuff. Should have allowed it to just drop the transaction in each segment, as you originally intended... Barb > ---------- > From: Steve Adams[SMTP:[EMAIL PROTECTED]] > Sent: Thursday, October 18, 2001 3:11 PM > To: Baker, Barbara > Subject: RE: ORA-01555 Snapshot Too Old > > Hi Barb, > > You should either have all other rollback segments offline, or put an > uncommitted transaction in each online rollback segment. > > @ Regards, > @ Steve Adams > @ http://www.ixora.com.au/ > @ http://www.christianity.net.au/ > > > -----Original Message----- > From: Baker, Barbara [mailto:[EMAIL PROTECTED]] > Sent: Friday, 19 October 2001 7:05 > To: Multiple recipients of list ORACLE-L > Subject: ORA-01555 Snapshot Too Old > > > The same weekly job has been blowing up with "snapshot too old" for the > last > year. After having been awakened (again) at 3:00 am, I'm a desperate > woman. > Any ideas would be greatly appreciated. > > We get the ORA-1555 error every time we run this job. We get this error > if > the job is the only process on the system. We get this error even if we > fire up Steve Adams' job to hang a transaction in the same rollback > segment > with an uncommitted transaction. (Steve's job has definitely helped - we > run significantly longer than we used to before we get the error.) > > The specifics: > The job belongs to our vendor. It's junk. Our development > staff does not have time to rewrite it. The vendor has been unable > (unwilling?) to help. (No, I do not know why we pay them a gazillion > dollars per year for support.) > > The job is written in Cobol. (No, I'm not making this up). > > The rollback segment we're using (via "set transaction use > rollback segment ...") is the only rollback segment in this particular > tablespace. The tablespace size is 1500 megs. > > I've tried a number of configurations for the rollback > segment. Currently it's set like this: initial 20m, next 20m, min > extents > 40 (ya, I know...). So, initially 800m of the 1500m tablespace is set > aside > for this rollback segment. The rollback segment currently does not have > optimal set. I've tried different configurations, but none have been > successful. > > It always takes 3 runs to complete this job: First run fails with > snapshot > too old. The rollback segment NEVER EXTENDS. (i.e., it has only the > initial 40 extents -- even tho there's another 700 megs available, it > never > attempts to extend. ) After a restart, the second run fails because it > actually does extend, and then it runs the 1.5 gig tablespace out of > space. > Third run successfully completes. > > The job is deleting about 2 million records from a table of about 5 > million > records. It "seems" to select via an index, delete by row id, > select,delete,,,, I believe the snapshot too old comes about because, at > the very end, the job somehow tries to query the table it's been deleting > from, and would therefore need to read the records from its own rollback > space. > > The database is 7.3.4 (No, I'm not making this up.) > > Any ideas?? I'm mostly confused about why Steve's job to hang the > transaction is not preventing re-writing the rollback segment. > > Thanks for any help. > > Barb > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Baker, Barbara > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
