0

I have oracle database 11gR2 . I want to save all changes of all tables in database such as insert,update and delete values . I want to save them in a table . When i search for its solution ,find out that ORACLE AUDIT can not save data of changes . Does ORACLE have any solution for this problem ? Thanks

1
  • Create auditing tables & triggers. Very simple. Why do you want to do this? I've often seen this requirement and it's usually misguided/unneeded.
    – Philᵀᴹ
    Commented Dec 16, 2013 at 12:11

2 Answers 2

2

Flashback Data Archive (formerly known as Oracle Total Recall) let you store history for all the changes to one or more tables automatically. If you have the Advanced Compression option, Oracle will apply a number of optimizations to the history table that will significantly decrease the amount of space that is required and that will generally make queries against history more efficient.

0
1

As Phil mentions in his comments... WHY? It may be better/faster to get help on what actually needs to be done than on a perceived method, good or bad, of achieving that.

All changes can be found in the REDO (and ARCHIVE) logs, not auditing tables. Depending on the real 'why' the one may be better than the other. You may also need "Supplemental logging" to truly get all data which is needed.

Oracle offers GOLDENGATE, STREAMS and LOGMINER as methods to get at Change data. Here is an article to get you going: https://blogs.oracle.com/Bakers_Byte/resource/General_DBA/Oracle_Log_Mining_Data.pdf

There are other companies that have productized CDC (Change Data Capture) information. I work for one of those companies: Attunity. Its Replicate product can use logminer or direct (ASM) redo log reads to find changes and apply them to an other database, or store them in easy access change tables for further processing. Attunity Replicate would create a change table per source table watched. Other products may implement this differently, but there is no point speculating what is best for your purpose until we learn WHY you want to save all changes and how many resources ( Compute, Effort, Licenses ) are available to achieve that need.

Good luck!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.