The more I thought about this (after exchanging a few direct emails with Thomas), I am
leaning
towards the delayed block cleanout possibility.
There was nothing in the alert log that indicated SMON off-lining any undo segments
(there were
only 10 SMUs to deal with). It is, however, possible that SMON may have stolen extents
from other
segments for long running jobs (if needed) and eventually caused ORA-1555. But Thomas
did not find
any relevant rows in V$UNDOSTAT, corresponding to the timeframe of the 1555 error.
That would have
confirmed if there was any stealing and mugging going on.. So, we are really back to
just brain
storming and intelligent guesswork, unfortunately :(
- Kirti
- Daniel Fink <[EMAIL PROTECTED]> wrote:
> 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
> > ---------------------------------------------------------------------
> > 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).> begin:vcard
> n:Fink;Daniel
> tel;cell:303.808.3282
> tel;work:303.272.3225
> x-mozilla-html:TRUE
> adr:;;;;;;
> version:2.1
> email;internet:[EMAIL PROTECTED]
> title:DB Services Lead
> x-mozilla-cpt:;-4832
> fn:Daniel Fink
> end:vcard
>
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: Kirtikumar Deshpande
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 - 17:04:17 CDT
