Re: Help with ORA-08176 please

> <no Oracle Server version info posted>
Look at the end - it is: OpenVMS 7.3-1 - Oracle 8.1.6.0

> NOLOGGING does not affect the creation of undo (rollback).
agreed

> NOLOGGING affects the generation of redo.
agreed

> Redo is not used in the creation of consistent read blocks.
> Undo is used in the creation of consistent read blocks.
don`t agree. *Rollback segments* are used for consistent view.

Pls correct me if I`m wrong
I still don't understand what caused ORA-8176

Thanks

-- 
Syltrem
OpenVMS 7.3-1 - Oracle 8.1.7.4

http://pages.infinit.net/syltrem (OpenVMS site in french language)
"Paul Drake" <drak0nian_at_yahoo.com> a écrit dans le message de
news:1ac7c7b3.0310022215.a580aa6_at_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