Re: Database Change Notification

Capturing the PKEY using Before Delete / Before Update trigger
should help. You can generate a sequence to capture the "order"
in which the DMLs took place. I had done this before in
production to capture the Changes and push them to Sybase from
Oracle. In addition you can try
[1]www.oracle.com/technology/deploy/availability/htdocs/LogMinerO
verview.htm. I am giving it below should Oracle remove the
contents of the URL at a later date(!)

LogMiner? is a powerful audit tool for Oracle databases, allowing
administrators to easily locate changes in the database, enabling
sophisticated data analyses, and providing undo capabilities to
rollback logical data corruptions or user errors.

LogMiner offers two interfaces:
  * A SQL interface, which calls built-in PL/SQL packages to
    setup a data dictionary, specify redo logs, and execute
    queries (this interface is applicable to SQL*Plus,
    command-line scripts, or custom applications).
  * LogMiner Viewer, an intuitive GUI which allows the
    administrator to setup a data dictionary, specify the redo
    logs, specify query criteria, and view/save redo log data.

LogMiner can also be leveraged for:
  * Ex post facto auditing of DML statements, the order in which
    transactions were committed and the user responsible for the
    updates.
  * Historical analysis of data access patterns for database
    tuning and capacity planning.
  * Tracking schema evolution and the impact on data structure.

LogMiner directly accesses the Oracle redo logs, which are
complete records of all activities performed on the database, and
the associated data dictionary, which is used to translate
internal object identifiers and types to external names and data
formats. Using a dynamic view V$LOGMNR_CONTENTS, LogMiner
populates a row in this view with each logical operation
performed on the database, thus offering familiar,
relational-based access for ad-hoc querying or custom application
consumption. Each row contains a SQL UNDO statement, which can be
used to rollback the change, and SQL REDO statement, which
details the original operation. Log analysis can be conducted
with the online catalog as the dictionary source, or an offline
dictionary that has been extracted to a set of relevant redo
logs, or a standalone flat file. With offline analysis, LogMiner
can be run on a separate database, using archived redo logs and
the associated dictionary from the source database, thus
alleviating resource consumption on the source system.

HTH
GovindanK
On Sat, 4 Apr 2009 12:00:32 -0400, "Brady, Mark"
<[2]Mark.Brady@xxxxxxxxxxxxxxxxx> said:
> So DCN gives a collection of ROWIDs that have changed and what
affected
> them, but we are left to go back and retrieve the row
ourselves. So has
> anyone built a wrapping package that handles deletes and
updates by
> retrieving prior images from flashback or some other mechanism?
>
>
> >>> This e-mail and any attachments are confidential, may
contain legal, professional or other privileged information, and
are intended solely for the addressee.  If you are not the
intended recipient, do not use the information in this e-mail in
any way, delete this e-mail and notify the sender. CEG-IP1
>
> --
> [3]www.freelists.org/webpage/oracle-l
>
>

References

1. 
http://www.oracle.com/technology/deploy/availability/htdocs/LogMinerOverview.htm
2. mailto:Mark.Brady@xxxxxxxxxxxxxxxxx
3. file://localhost/tmp/linkstmp/www.freelists.org/webpage/oracle

Other related posts: