Re: Snapshot too old in undo tablespace in 9i?

Abraham,

Setting the retention time may not solve the problem. One of the ways that an ORA-1555 can be triggered is when the transaction table slot is overwritten. This is caused by having many small, serial transactions in the database while the export is running.

In each undo segment (or rollback segment), there is a structure called the transaction(tx) table. This contains transaction - undo segment binding/status information. The number of slots is block-size dependent. I don't recall the exact numbers. When a transaction is bound to an undo segment, it allocates a slot in the tx table. This provides the links between the data/index block and the undo entries. If the data block points to a slot that has been reused, there is not a way to reconstruct the data, so it throws a 1555. The undo information may be preserved in the segments, but the link necessary has been lost.

For illustration purposes, let's say you have 10 undo segments and each of them has a transaction table containing 40 slots. You have 400 slots available. If you have 100 transactions per minute (no more than 10 concurrently (so as to prevent new undo segments being created)), a slot will be reused every 4 minutes.

If I have not sufficiently bored you, more detail can be found at www.optimaldba.com/library.html. Look for the documents on Undo Internals and Automatic Undo Internals. Tim Gorman also has a great paper called "Cats, Dogs and ORA-1555s" on his site (www.evdbt.com).

Daniel

"Guerra, Abraham J" wrote:
>
> Thanks.
>
> Abraham
>
> -----Original Message-----
> Sent: Tuesday, August 05, 2003 12:35 PM
> To: Multiple recipients of list ORACLE-L
>
> retention time --- set it for a couple of hours longer than you think the
> export will take.
>
>
> "Guerra, Abraham
> J" <AGUERRA To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> @amfam.com> cc:
> Sent by: Subject: Snapshot too old in undo tablespace in 9i?
> ml-errors
>
>
> 08/05/2003 01:24
> PM
> Please respond
> to ORACLE-L
>
>
>
> Hello Group,
>
> I just upgraded a database to Oracle 9.2.0.3 from 8.1.7. I created an
> undo tablespace with 10 (default) undo segments... however, during an
> export I got the following message:
>
> ORA-01555: snapshot too old: rollback segment number 15 with name "
> _SYSSMU15$" too small
>
> I thought this was a thing of the past... According to the documentation,
> if an undo segment gets full, it starts using idle ones... also, the undo
> tablespace still had a lot of room to grow...
>
> Any insights will be welcome.
>
> Thanks
>
> Abraham Guerra
> Oracle DBA
> American Family Insurance
>
> << Attachment Removed : Notebook.jpg >>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Day
> INET: tday6_at_csc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
> --
> Author: Guerra, Abraham J
> INET: AGUERRA_at_amfam.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: Daniel Fink
  INET: daniel.fink_at_sun.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).



text/x-vcard attachment: Card for Daniel Fink