Re: Quick flashback question

On Mar 8, 1:02 pm, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> Anurag Varma wrote:
> > On Mar 8, 12:11 pm, GS <g..._at_canada.com> wrote:
> >> I've used this feature only once before, so I am a bit rusty here..
>
> >> A user just came and said they might have trashed some data in a table
> >> or tables, I am just waiting to hear back more details. In any case,
> >> they dont want to do a PIT recovery of the whole database, so I said I'd
> >> look into how far back I could query the tables when I find out which
> >> ones they need. (there is no export current enough to help here)
>
> >> I checked the DB and the undo retention was set for 3 hours, so I
> >> figured they were out of luck, but just out of curiosity I queried a
> >> table from the schema using the "as of" syntax for a point in time
> >> yesterday and it returned data. I was expecting it to return an error.
>
> >> The undo tablespace is 500MB of which only 23MB are currently being
> >> used. My understanding of flashback query was that you could only query
> >> back as far as undo retention is set. When I query the v$undostat view
> >> it is goes back just over 2 days.
>
> >> If the database has very little activity can you query beyond the undo
> >> retention parameter? Been searching tahiti and from what I see there I
> >> should be limited to what the retention param is set to.
>
> >> thanks in advance!
>
> > Yes, if the database has very little activity, then you can in lot of
> > cases
> > query beyond the undo retention parameter if your expired undo has not
> > been overwritten yet.
>
> > In fact in 10gr2, oracle "autotunes" your undo_retention parameter
> > and tries filling upto 85% of the undo tablespace in some cases.
> > It also tunes the undo_retention paramter based on the undo generated
> > (see tuned_undoretention column in v$undostat)
>
> > Anurag
>
> A related but different question. What would happen if you created a new
> undo tablespace and switched the instance over to that? Would the undo
> information in the old undo tablespace be available indefinitely for
> flashback queries?- Hide quoted text -
>

This has already been answered several times in cdos as well as in asktom.oracle.com ...

If you don't want to find those ... why not do an experiment or 2 and let us know what your results were! Received on Thu Mar 08 2007 - 16:16:58 CST