Re: State of data over the time

"Patrick J." <patrickj_at_kneipN-O-S-P-A-M.com> wrote in message news:3b974576$1_2_at_news.vo.lu...
> Hi,
>
> We are currently analyzing an application that will be based on an Oracle
> database. This database will contain about 40 tables, with many foreign
keys
> between all these tables.
> An important requirement for this application is that it must be able to
> show the data as it was at any given date and time in the past, this
> concerning every data in the database, and therefore also the links
between
> the data. The implementation of this functionality should not compromise
the
> integrity of the database (most important point), nor result in too bad
> performances of the application.
> I am looking for the better way to fulfill this requirement, in terms of
> organization of data at the database level. Could you please give me some
> advices, based on your own experience?
>
> Thanks,
>
> Patrick J.
>

I don't know about being able to see the data "at ANY time in the past", but you should consider Oracle 9i, and it's new Flashback feature.

When you configure 9i, you specify an 'undo retention' parameter, which means that the redo for DML is retained for the specified number of seconds. You can set that into the hundreds of thousands (but you'd better have an enormous rollback tablespace), and that gives you the ability to query any table, as it was at any time within that retention window.

It's part of the database engine, so there are minimal performance impacts, and Flashback is read only, so there are no worries about database integrity.

And I never thought I'd be urging a move to 9i this early in the piece!

Regards
HJR
>
Received on Thu Sep 06 2001 - 15:37:43 CDT