Using an Audit table

When defining a replication task, you can choose to store changes to all tables in a single Audit table (located in the target endpoint). The changes can then be pushed from a single stream to any queue (JMS for example).

The following table describes the structure of the Audit table.

Audit table structure
Column Name	Type	Description
`task_name`	varchar (128)	The name of the replication task.
`change_seq`	bigint	A monotonically increasing change sequencer that is common to all change tables of a task. The change sequence has the following format: YYYYMMDDHHmmSShhxxxxxxxxxxxxxxxxxxx Where: YYYY is the four-digit year (such as 2012) MM is the two-digit month (range from 01-12) DD is the two-digit day (range from 01-31) HH is the hour in the day (range from 00-23) mm is the minute in the hour (range from 00-59) SS is the second in the minute (range from 00-59) hh is the hundredth of the second (range from 00-99) xxxxxxxxxxxxxxxxxxx is a 19-digit, zero prefixed change number (global per task) The time part usually refers to the commit time of the transaction that includes the change record. Replicate contains logic that maintains the monotonicity of the sequence number so modifying or adjusting the endpoint time may result in multiple changes to seem that they are within the same timestamp but with increasing change number. The xxx...xxx is usually the internal change number from the data record except that for BEFORE-IMAGE records it is the same as the change number of the matching UPDATE record (for example, if the change number of BEFORE-IMAGE is 1000 and that of the UPDATE is 1001, then both have 1001). This allows a simple left-outer-join between the table and itself where on the left we scan until the point in time but filter out `operation=before-image`, and on the right we join on the same `change_seq` with the `change_oper` being 'B' .
`change_oper`	varchar (1)	The operation type. This can be one of the following: `I`: INSERT `D`: DELETE `U`: UPDATE `B`: Before Image
`stream_position`	varchar (128)	The source CDC stream position.
`schema_name`	nvarchar (128)	The name of the source table schema.
`table_name`	nvarchar	The name of the source table.
`operation`	varchar (12)	The operation associated with the change record. It can be one of the following: INSERT UPDATE DELETE BEFOREIMAGE
`transaction_id`	varchar (32)	The ID of the transaction that the change record belongs to. The value is a hex-string of the 128-bit transaction ID.
`timestamp`	timestamp	The original change timestamp (the value may be approximate).
`change_record` Information note The data is stored in JSON format.	nclob	The new data. Information note LOB columns with unlimited size are not supported in the `change_record` field. The other fields will be recorded but the LOB will have a NULL value.
`bu_change_record` Information note The data is stored in JSON format.	nclob	The data before the update. Information note LOB columns with unlimited size are not supported in the `bu_change_record` field. The other fields will be recorded but the LOB will have a NULL value.
Partition start		Partition name, partition start time (UTC), and estimated partition end time (UTC)
Partition end		Partition name, partition start time (UTC), partition end time (UTC), and a list of changed tables (identified by table name and schema name).

For more information on storing changes in an audit table, see Store Changes Settings.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here