Re: Oracle data change history ?
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 26 Aug 2009 09:03:07 -0700 (PDT)
Message-ID: <22783b3b-47c9-4f1b-84b4-67699f557740_at_k30g2000yqf.googlegroups.com>
On Aug 26, 7:58 am, grz01 <gr..._at_spray.se> wrote:
> Hi,
>
> We are building a new application with Java/Spring, Hibernate and
> Oracle.
>
> One requirement is to be able to trace back the history of all objects
> (table-rows) in the database and see how they looked at a certain
> point back in time.
> We also need to know who changed what, and when -- but "who" is
> determined by programming-logic (not the userid in Oracle) so I guess
> that info has to be stored along in the database-tables.
>
> The flashback-feature in Oracle seems to have the general
> functionality we want, but being based on redo-logs, etc, I guess it's
> not useful if you want to retain all history going back, say, 24
> months, or so?
>
> Is there some other feature or framework (from Oracle, a third-party
> or open-source) suitable for this purpose?
>
> TIA, grz
Date: Wed, 26 Aug 2009 09:03:07 -0700 (PDT)
Message-ID: <22783b3b-47c9-4f1b-84b4-67699f557740_at_k30g2000yqf.googlegroups.com>
On Aug 26, 7:58 am, grz01 <gr..._at_spray.se> wrote:
> Hi,
>
> We are building a new application with Java/Spring, Hibernate and
> Oracle.
>
> One requirement is to be able to trace back the history of all objects
> (table-rows) in the database and see how they looked at a certain
> point back in time.
> We also need to know who changed what, and when -- but "who" is
> determined by programming-logic (not the userid in Oracle) so I guess
> that info has to be stored along in the database-tables.
>
> The flashback-feature in Oracle seems to have the general
> functionality we want, but being based on redo-logs, etc, I guess it's
> not useful if you want to retain all history going back, say, 24
> months, or so?
>
> Is there some other feature or framework (from Oracle, a third-party
> or open-source) suitable for this purpose?
>
> TIA, grz
What full version of Oracle?
Version 11g includes archive tablespaces and it is possible to have Oracle maintain a record of all row changes for tables assigned to archive tablespaces.
On prior versions you normally use some combination of Fine Grained Auditing, history tables maintained through table level triggers, and standard auditing to track changes.
HTH -- Mark D Powell -- Received on Wed Aug 26 2009 - 11:03:07 CDT