Barb - Having worked through a few of these ORA-01555 errors, my sympathy is
with you. A pretty good site at explaining some of the deeper issues with it
is http://home.clara.net/dwotton/dba/snapshot.htm. Sounds like you have a
fetch across commit.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Thursday, October 18, 2001 4:05 PM
To: Multiple recipients of list ORACLE-L
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: DENNIS WILLIAMS
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).
