Hi!

For my project I need a history of all changed recordsets of one or more
tables. If I change one record, the old one should be in the history.

I have two choices. First of all to do it with my application. Well, I think
that is a bad idea, because I have to do addional SQL Statements and when
the scheme changes, I have to recompile some parts of my application.
It sound like a perfect Job for the Oracle DB.
The second choice I have is to build trigger and do it this way. But
actually I have to do it again when I have to change the scheme.

I wonder if it is possible to let Oracle create a history on itself. I read
nearly a week about Redo Log. It seems that Oracle should know everything to
create a history of all changes that has affected the scheme.

Does anyone know if this is possible to create a history of all changes and
showing the corresponding records. And then how I could do that?


Thanks,

Danny

You have a few options available to you...

1) You can use LogMiner to go through the archived redo logs to see
which transactions occurred on your tables of interest. I would
recommend this tool if you need to do this once or twice, but not on a
regular basis. However, if you have not set up any of the options below,
this may be your only way to retrieve the information until one of the
other options is implemented.
2) Use auditing. See the following doc for more details:

http://download-east.oracle.com/docs...b14266/toc.htm

3) If auditing does not meet your needs, then create a trigger to
capture information you need when DML is performed. Store the
information in a table to be queried later. In this sense, you are
implementing your own auditing, but you can do more here.


HTH,
Brian

--
================================================== =================

Brian Peasland
oracle_dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Danny Gerst wrote:
And your Oracle version number is?
What transaction volume (bytes and tx/sec.)?

In 10g I can think of at least a half-dozen ways to do this.

Daniel Morgan
www.psoug.org

Hi!

Thx for your answers.

Well, I work with 10g and there will be at most one transaction every
minute. I need the historyfunvtion one a regular bases nearly one or twice a
month. Their want to get an overview over all changes that had be done on a
table and an overview of all old records. So the new one will be in the
regular database, while the history should show all the old records.
It like saving the current record in a separate table and than apply the
changes.

First I have to check the two last suggestion from Brian. But I am open for
every another possibility to make that happen.


Greetings,

Danny

Danny Gerst wrote:
Since you are using 10g, another option is to leverage something called
a Flashback Versions Query.

http://download-east.oracle.com/docs...htm#sthref1477

This will give you all of the changes made to a table between two SCN's
or timestamps. This can be used with Flashback Transaction Query to Undo
a change if necessary. This relies on your UNDO tablespace, so you'll
need to query the records before your UNDO_RETENTION starts expiring the
information.

HTH,
Brian

--
================================================== =================

Brian Peasland
oracle_dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown