Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with ORA-08176 please

Re: Help with ORA-08176 please

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 2 Oct 2003 23:15:04 -0700
Message-ID: <1ac7c7b3.0310022215.a580aa6@posting.google.com>


"Syltrem" <syltremzulu_at_videotron.ca> wrote in message news:<s0_eb.2547$G1.12138_at_tor-nn1.netcom.ca>...
> Hi
>
> Yesterday a report was running for an hour or so, when the user called and
> asked why it was taking so long.
> I found I needed to create an index so I did.
>
> CREATE INDEX .... NOLOGGING;
> ALTER INDEX ... LOGGING;
>
> I thought creating the index with NOLOGGING would be a good thing since this
> was a pretty large table, and the index creation would run faster.
>
> But the report program stopped abruptly at some point with the message
>
> 08176, 00000, consistent read failure; rollback data not available
>
> // *Cause: Encountered data changed by an operation that does not generate
> // rollback data : create index, direct load or discrete
> transaction.
> // *Action: In read/write transactions, retry the intended operation. Read
> // only transactions must be restarted.
>
>
> Can you explain why a READ transaction would suffer from this (CREATE
> INDEX... NOLOGGING) ?
> To me, Oracle should just start using the index next time it executes the
> problem request again, following index creation. The request that benefitted
> from the new index is executed hundreds of time in that report.
>
> After restarting the report program it took only 2 minutes to run.
>
> Thanks

Hi.

<no Oracle Server version info posted>
NOLOGGING does not affect the creation of undo (rollback). NOLOGGING affects the generation of redo. Redo is not used in the creation of consistent read blocks. Undo is used in the creation of consistent read blocks. More than likely, the rate of undo generation caused blocks of undo that were required to generate a consistend read (at that point) to be over-written (previously).

might I suggest having a look at some papers on this topic, here's a good start:

http://www.nyoug.org/techjournal.htm

click on Tim Gorman's presentation.

hth.

Paul Received on Fri Oct 03 2003 - 01:15:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US