hpuxrac wrote:
> 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!
>
I'll search the archives. Thanks. There's so much traffic on this ng
that I can't follow it all.
Received on Thu Mar 08 2007 - 16:34:54 CST
