Re: Manual flashback implementation

Log Miner?

Not being funny, but if this sort of functionality is *that* crucial to your application, why don't you upgrade to a version that supports it 'out of the box'. Anything else is going to be cludgy implementation with triggers firing off left, right and centre; slowing things down as a result; and possibly not doing to job particularly well at the end of it.

Regards
HJR "Richard Kuhler" <noone_at_nowhere.com> wrote in message news:_5wC9.18360$%k2.5216758_at_twister.socal.rr.com...
> I'm looking for design ideas to manually implement the equivalent of
> Oracle's flashback query. In other words, I need to be able to retrieve
> the data as it looked at any point in the past. Since I need a 100%
> guarantee that I can do this forever, I don't see how I can safely rely
> on Oracle's flashback mechanism.
> Here's what I've come up with...
>
> 1. Triggers on all base tables record all changes to a history table
> 2. Any time a commit occurs, the SCN and time of the commit must be
> recorded in my own table
> 3. All rows that were modified during that commit must have the SCN
> recorded in the history tables rows
>
> It's step 3 that I hate the most. This will result in lots of
> additional updates in the system. Actually, it's not even clear to me
> yet how I can implement steps 2 and 3 (is there some way to get this
> information from Oracle?). I suspect I'm going to have to create my own
> SCN mechanism and implement a special commit procedure for all processes
> that manipulate data. There's also a delay between steps 2 and 3 when
> queries would not be able to retrieve the data as of the current time
> (although a delay of as much as 24 hours would be acceptable). I have a
> feeling that it's probably possible to process the redo logs to build
> the history table information with the necessary SCN in one step but I'm
> not sure how best to proceed with that.
>
> Anybody have any ideas?
>
>
> Thanks,
> Richard Kuhler
>
Received on Tue Nov 19 2002 - 13:41:28 CST