Quick flashback question

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! Received on Thu Mar 08 2007 - 11:11:55 CST