Skip to main content

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!