RE: ORA-01555 with Automatic Undo Management mode

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Wed, 18 Jun 2003 12:36:42 -0700
Message-ID: <F001.005B441A.20030618120502@fatcity.com>

My guess is that this has to do with being able to get a read consistent image of the underlying tables of the mview. can you provide us with:

The version of Oracle
The statement being used to create the mview
Is anyone doing anything to the underlying tables while you are creating the mview?
Does this table have any LOB columns in it?

-----Original Message-----
To: Multiple recipients of list ORACLE-L Sent: 6/18/2003 9:59 AM

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

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Freeman Robert - IL
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------