RE: ORA-01555 Snapshot Too Old

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:steve.adams_at_ixora.com.au]
> 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:bakerb_at_rockymountainnews.com]
> 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: bakerb_at_rockymountainnews.com
>
> 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: ListGuru_at_fatcity.com (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: steve.adams_at_ixora.com.au

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: ListGuru_at_fatcity.com (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).