Re: ORA-01555 with Automatic Undo Management mode

From: Thomas Day <tday6_at_csc.com>
Date: Wed, 18 Jun 2003 09:39:50 -0700
Message-ID: <F001.005B409D.20030618090508@fatcity.com>

Sorry, I'm the only user with access to this database and this was the only job running.

Very frustrating two days of reading manuals and metalink has not improved my "warm and fuzzy" about using UNDO.

                                                                                       
                                                
                      Rachel                                                           
                                                
                      Carmichael               To:      Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>                    
                      <wisernet100             cc:                                     
                                                
                      @yahoo.com>              Subject: Re: ORA-01555  with Automatic 
Undo Management mode                             
                      Sent by:                                                         
                                                
                      ml-errors                                                        
                                                
                                                                                       
                                                
                                                                                       
                                                
                      06/18/2003 12:14                                                 
                                                
                      PM                                                               
                                                
                      Please respond                                                   
                                                
                      to ORACLE-L

I'm sure Dan Fink will have more (and better) information on this, as he did a presentation on AUM at IOUG. But we did do a demonstration of how you can get an ORA-1555 even with AUM. It has nothing to do with trying to create another extent or how much room is left in the tablespace but has much to do with the fact that other transactions are running in the database at the same time.

Undo_retention (if I remember Dan's presentation correctly) is a "wish" -- Oracle can and will overwrite the undo segment extent even if the retention time is not past if space is needed.

Dan -- did I get it right?

Rachel

Thomas Day <[EMAIL PROTECTED]> wrote:
>
> I'm trying to create a materialized view on a 7G table. I'm using
> UNDO and
> undo_retention=108000, which if I understand correctly is 30 hours.
> I have
> 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get
> ORA-01555: snapshot too old: rollback segment number with name ""
> too
> small
>
> The FM says:
>
> ORA-01555 snapshot too old: rollback segment number string with name
> "
> string" too small
> Cause: Rollback records needed by a reader for consistent read
> are
> overwritten by other writers.
> Action: If in Automatic Undo Management mode, increase the
> setting of
> UNDO_RETENTION. Otherwise, use larger rollback segments.
>
>
>
> UNDOTBS1 still had 3G of space free with about 88 active undo
> extents, the
> largest was 4,154,458,112 bytes. Was it trying to create another 4G
> extent? Is there something I am missing? This Automatic stuff
> doesn't
> seem to be so automatic. I can create this using rollback segments
> but I
> wanted to use UNDO because it allows past point in time queries.
>
>
> Oracle 9.2.0.1.0
>
>
> Win2K.
>
>
>
>
>
> Any thoughts (besides get a real operating system and use rollback
> segments) would be greatly appreciated.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Day
> INET: [EMAIL PROTECTED]
>
> 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: [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).

Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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: [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.net
-- 
Author: Thomas Day
  INET: [EMAIL PROTECTED]

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: [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).

Received on Wed Jun 18 2003 - 11:39:50 CDT