ORA-01555 with Automatic Undo Management mode

From: Thomas Day <tday6_at_csc.com>
Date: Wed, 18 Jun 2003 07:25:46 -0700
Message-ID: <F001.005B3E26.20030618065937@fatcity.com>

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). Received on Wed Jun 18 2003 - 09:25:46 CDT