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
>
thanks
The table I test queried is pretty static, so when I find out the actual
tables that may have lost or trashed data I'll find out more. The db is 91R2
Received on Thu Mar 08 2007 - 11:49:03 CST
