| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ORA-01555 with Automatic Undo Management mode
>>That all being said, the fact that there is free space in the
tablespace implies that the 1555 is not due to an extent being
overwritten. Oracle should grab free space before it grabs other
extents, even expired ones. <<
Daniel,
Are you saying this correctly? The reason I ask, is I've seen a 1555
error in a system with one job running, only 480 MBs of undo space used,
10 minute retention setting, and an undo tablespace of 12 GB. It
doesn't seem that Oracle grabbed an extent from free space before using
an expired one. (Retention setting is now much, much higher. Got to
watch those hours to seconds conversions.)
>>> [EMAIL PROTECTED] 06/18/03 03:19PM >>> Thomas,
How long ago was the data for the mview loaded? It is possible that you are running into the scenario where delayed block cleanout is causing the ORA-1555. Is the name of the undo segment in the error the same as the large undo extent? I would suspect that they are different.
I have not tested mviews as transactions, but I presume oracle considers it as one large transaction. This would prevent it from acquiring new undo segments, which makes sense as there is one segment that is very large in respect to all the others. There are only 88 segments in the tablespace, both offline and online, not 88 online? Assuming the 88 segments have been shrunk to minimum (128k), they will consume only 10meg. With the numbers you quote, this sounds like the case.
SMON awakes periodically to offline undo segments and it may
have offlined a segment that was needed by the transaction (it just did
not know that at the time). SMON should not offline a segment if the
retention time has not been met, but I don't know this for a fact. There
is a thought in the deep dark recesses of my mind that the situation may
be the result of the expire time algorithm that Oracle uses. (The sound
you have just heard is Kirti fleeing from the list before we go down
that path
again!)
That all being said, the fact that there is free space in the tablespace implies that the 1555 is not due to an extent being overwritten. Oracle should grab free space before it grabs other extents, even expired ones. If the process was attempting to acquire an extent and could not find sufficient free space, you would receive the 'Unable to extend segment' error and not the ORA-1555.
Since you are the only process running, I think delayed block
cleanout (a result of the data loading process and nothing to do with
your mview) or segment offlining are your likely culprits. I can't
recommend this will work, but give it a shot. Alter the tablespace
containing the source table into read only mode, then run the mview
statement. The fact that the ts is read-only guarantees that all data
contained within has been committed. This solution was suggested to me
by a very wise member of
the list, so it is worth a try.
Daniel Fink
BTW, AUM is not a requirement for Flashback Query. However, it is the only configuration Oracle will support for FBQ and it is more likely to succeed with AUM.
Thomas Day 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
>
Message containing double extensions/CLSID exploit. This message contained attachments that have been blocked by Guinevere. Please see your system administrator for more details
.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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 - 16:39:38 CDT
 |
 |