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?

Chuck,

Consider the following:
You update a column value from 1 -> 2 -> 3-> 4 The undo for 1->2 is in UNDO1
while undo for 2->3 is in UNDO2 (the one you switched to).

Consider time has elapsed and the undo for 2->3 has been expired and overwritten.
There is no way you can get to flashback to the state when the value was 1.

When you switch, the old undo tablespace is set offline once all active
transactions on it have finished....

Anurag Received on Thu Mar 08 2007 - 17:05:18 CST