15
Change Data Capture

Change Data Capture efficiently identifies and captures data that has been added to, updated, or removed from, Oracle relational tables, and makes the change data available for use by applications. Change Data Capture is provided as an Oracle database server component with Oracle9i.

This chapter introduces Change Data Capture in the following sections:

• About Change Data Capture
• Installation and Implementation
• Security
• Columns in a Change Table
• Change Data Capture Views
• Synchronous Mode of Data Capture
• Publishing Change Data
• Managing Change Tables and Subscriptions
• Subscribing to Change Data
• Export and Import Considerations

  See Also: Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the Change Data Capture publish and subscribe PL/SQL packages.

About Change Data Capture

Oftentimes, data warehousing involves the extraction and transportation of relational data from one or more source databases, into the data warehouse for analysis. Change Data Capture quickly identifies and processes only the data that has changed, not entire tables, and makes the change data available for further use.

Without Change Data Capture, database extraction is a cumbersome process in which you move the entire contents of tables into flat files, and then load the files into the data warehouse. This ad hoc approach is expensive in a number of ways.

Change Data Capture does not depend on intermediate flat files to stage the data outside of the relational database. It captures the change data resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is then stored in a database object called a change table, and the change data is made available to applications in a controlled way.

Table 15-1 describes the advantages of performing database extraction with Change Data Capture.

Table 15-1 Database Extraction With and Without Change Data Capture 

Database Extraction	With Change Data Capture	Without Change Data Capture
Extraction	Database extraction from INSERT, UPDATE, and DELETE operations occurs immediately, at the same time the changes occur to the source tables.	Database extraction is marginal at best for INSERT operations, and problematic for UPDATE and DELETE operations, because the data is no longer in the table.
Staging	Stages data directly to relational tables; there is no need to use flat files.	The entire contents of tables are moved into flat files.
Interface	Provides an easy-to-use publish and subscribe interface using DBMS_LOGMNR_CDC_PUBLISH and DBMS_LOGMNR_CDC_SUBSCRIBE packages.	Error prone and manpower intensive to administer.
Cost	Supplied with the Oracle9i (and later) database server. Reduces overhead cost by simplifying the extraction of change data.	Expensive because you must write and maintain the capture software yourself, or purchase it from a third-party vendors.

A Change Data Capture system is based on the interaction of a publisher and subscribers to capture and distribute change data, as described in the next section.

Publish and Subscribe Model

Most Change Data Capture systems have one publisher that captures and publishes change data for any number of Oracle source tables. There can be multiple subscribers accessing the change data. Change Data Capture provides PL/SQL packages to accomplish the publish and subscribe tasks.

Publisher

The publisher is usually a database administrator (DBA) who is in charge of creating and maintaining schema objects that make up the Change Data Capture system. The publisher performs these tasks:

• Determines the relational tables (called source tables) from which the data warehouse application is interested in capturing change data.
• Uses the Oracle supplied package, DBMS_LOGMNR_CDC_PUBLISH, to set up the system to capture data from one or more source tables.
• Publishes the change data in the form of change tables.
• Allows controlled access to subscribers by using the SQL GRANT and REVOKE statements to grant and revoke the SELECT privilege on change tables for users and roles.

Subscribers

The subscribers, usually applications, are consumers of the published change data. Subscribers subscribe to one or more sets of columns in source tables. Subscribers perform the following tasks:

• Use the Oracle supplied package, DBMS_LOGMNR_CDC_SUBSCRIBE, to subscribe to source tables for controlled access to the published change data for analysis.
• Extend the subscription window and create a new subscriber view when the subscriber is ready to receive a set of change data.
• Use SELECT statements to retrieve change data from the subscriber views.
• Drop the subscriber view and purge the subscription window when finished processing a block of changes.
• Drop the subscription when the subscriber no longer needs its change data.

Example of a Change Data Capture System

The Change Data Capture system captures the effects of DML statements, including INSERT, DELETE, and UPDATE, when they are performed on the source table. As these operations are performed, the change data is captured and published to corresponding change tables.

To capture change data, the publisher creates and administers change tables, which are special database tables that capture change data from a source table.

For example, for each source table for which you want to capture data, the publisher creates a corresponding change table. Change Data Capture ensures that none of the updates are missed or duplicated.

Each subscriber has its own view of the change data. This makes it possible for multiple subscribers to simultaneously subscribe to the same change table without interfering with one another.

Figure 15-1 shows the publish and subscribe model in a Change Data Capture system.

Figure 15-1 Publish and Subscribe Model in a Change Data Capture System

Text description of the illustration syncfig1.gif

For example, assume that the change tables in Figure 15-1 contains all of the changes that occurred between Monday and Friday, and also assume that:

• Subscriber 1 is viewing and processing data from Tuesday.
• Subscriber 2 is viewing and processing data from Wednesday to Thursday.

Subscribers 1 and 2 each have a unique subscription window that contains a block of transactions. Change Data Capture manages the subscription window for each subscriber by creating a subscriber view that returns a range of transactions of interest to that subscriber. The subscriber accesses the change data by performing SELECT statements on the subscriber view that was generated by Change Data Capture.

When a subscriber needs to read additional change data, the subscriber makes procedure calls to extend the window and to create a new subscriber view. Each subscriber can walk through the data at its own pace, while Change Data Capture manages the data storage. As each subscriber finishes processing the data in its subscription window, it calls procedures to drop the subscriber view and purge the contents of the subscription window. Extending and purging windows is necessary to prevent the change table from growing indefinitely, and to prevent the subscriber from seeing the same data again.

Thus, Change Data Capture provides the following benefits for subscribers:

• Guarantees that each subscriber sees all of the changes, does not miss any changes, and does not see the same change data more than once.
• Keeps track of multiple subscribers and gives each subscriber shared access to change data.
• Handles all of the storage management, automatically removing data from change tables when it is no longer required by any of the subscribers.

Components and Terminology for Synchronous Change Data Capture

This section describes the Change Data Capture components shown in Figure 15-2. The publisher is responsible for all of the components shown in Figure 15-2, except for the subscriber views. The publisher creates and maintains all of the schema objects that make up the Change Data Capture system, and publishes change data so that subscribers can use it.

Subscribers are the consumers of change data and are granted controlled access to the change data by the publisher. Subscribers subscribe to one or more columns in source tables.

With synchronous data capture, the change data is generated as data manipulation language (DML) operations are made to the source table. Every time a DML operation occurs on a source table, a record of that operation is written to the change table.

Figure 15-2 Components in a Synchronous Change Data Capture System

Text description of the illustration sync_com.gif

The following subsections describe Change Data Capture components in more detail.

Source System

A source system is a production database that contains source tables for which Change Data Capture will capture changes.

Source Table

A source table is a database table that resides on the source system that contains the data you want to capture. Changes made to the source table are immediately reflected in the change table.

Change Source

A change source represents a source system. There is a system-generated change source named SYNC_SOURCE.

Change Set

A change set represents the collection of change tables. There is a system-generated change set named SYNC_SET.

Change Table

A change table contains the change data resulting from DML statements made to a single source table. A change table consists of two things: the change data itself, which is stored in a database table, and the system metadata necessary to maintain the change table. A given change table can capture changes from only one source table. In addition to published columns, the change table contains control columns that are managed by Change Data Capture. See "Columns in a Change Table" for more information.

Publication

A publication provides a way for publishers to publish multiple change tables on the same source table, and control subscriber access to the published change data. For example, Publication A consists of a change table that contains all the columns from the EMPLOYEE source table, while Publication B contains all the columns except the salary column from the EMPLOYEE source table. Because each change table is a separate publication, the publisher can implement security on the salary column by allowing only selected subscribers to access Publication A.

Subscriber View

A subscriber view is a view created by Change Data Capture that returns all of the rows in the subscription window. In Figure 15-2, the subscribers have created two views: one on columns 7 and 8 of Source Table 3 and one on columns 4, 6, and 8 of Source Table 4 The columns included in the view are based on the actual columns that the subscribers subscribed to in the source table.

Subscription Window

A subscription window defines the time range of change rows that the subscriber can currently see. The oldest row in the window is the low watermark; the newest row in the window is the high watermark. Each subscriber has a subscription window.

Installation and Implementation

Change Data Capture comes pre-packaged with the appropriate Oracle9i drivers already installed with which you can implement synchronous data capture.

In addition, note that Change Data Capture uses Java. Therefore, when you install the Oracle9i database server, ensure that Java is enabled.

Change Data Capture installs systemwide triggers on the CREATE TABLE, ALTER TABLE, and DROP TABLE statements. If system triggers are disabled on the database instance, Change Data Capture will not function correctly. Therefore, you should never disable system triggers.

To remove Change Data Capture from the database, the SQL script rmcdc.sql is provided in the admin directory. This will remove the system triggers that CDC installs on the CREATE TABLE, ALTER TABLE and DROP table statements. In addition, rmcdc.sql removes all Java classes used by Change Data Capture. Note that after rmcdc.sql is called, CDC will no longer operate on the system. If the system administrator decides to remove the Java Virtual Machine from a database instance, rmcdc.sql must be called before rmjvm is called.

To re-install Change Data Capture, the SQL script initcdc.sql is provided in the admin directory. It creates the CDC system triggers and Java classes that are required by Change Data Capture.

Change Data Capture Restriction on Direct-Path INSERT

Change Data Capture does not support the direct-path INSERT statement (and, by association, the multi_table_insert statement) feature in parallel DML mode.

When you create a change table, Change Data Capture creates triggers on the source table. Because a direct-path INSERT disables all database triggers, any rows inserted into the source table using the SQL statement for direct-path INSERT in parallel DML mode will not be captured in the change table.

Similarly, Change Data Capture cannot capture the inserted rows from multitable insert operations because the SQL multi_table_insert statement in parallel DML mode uses direct-path INSERT. Also, note that the multitable insert operation does not return an error message to indicate that the triggers used by Change Data Capture did not fire.

Security

You grant privileges for a change table separately from the privileges you grant for a source table. For example, a subscriber that has privileges to perform a SELECT operation on a source table might not have privileges to perform a SELECT operation on a change table.

The publisher controls subscribers' access to change data by using the SQL GRANT and REVOKE statements to grant and revoke the SELECT privilege on change tables for users and roles. The publisher must grant the SELECT privilege before a user or application can subscribe to the change table.

The publisher must not grant any DML access (using either the INSERT, UPDATE, or DELETE statements) to the subscribers on the change tables because of the risk that a subscriber might inadvertently change the data in the change table, making it inconsistent with its source. Furthermore, the publisher should avoid creating change tables in schemas to which users have DML access.

Columns in a Change Table

A change table contains the change data resulting from DML statements. A change table consists of two things: the change data itself, which is stored in a database table and the system metadata necessary to maintain the change table.

The change table contains control columns that are managed by Change Data Capture. Table 15-2 describes the contents of a change table.

Table 15-2 Control Columns for a Change Table 

Column	Datatype	Nullable?	Description
RSID$	NUMBER	N	Unique row sequence ID.
OPERATION$	CHAR(2)	N	I: Insert UO or UU: Update old value UN: Update new value UL: Update LOB D: Delete
CSCN$	NUMBER	N	Commit SCN.
COMMIT_TIMESTAMP$	DATE	Y	Commit time of this transaction.
SOURCE_COLMAP$	NUMBER	N	Bit mask of updated columns; source table relative (optional column).
TARGET_COLMAP$	NUMBER	N	Bit mask of updated columns; change table relative (optional column).
USERNAME$	VARCHAR2(30)	N	Name of the user who caused the operation (optional column).
TIMESTAMP$	DATE	N	Time when the operation occurred in the source table (optional column).
ROW_ID$	ROW_ID	N	Row ID of affected row in source table (optional column).
SYS_NC_OID$	RAW(16)	Y	Object ID (optional column).

Change Data Capture Views

Information about the Change Data Capture environment is provided in the views described in Table 15-3.

Table 15-3 View Names for Change Data Capture 

View Name	Description
CHANGE_SOURCES	Allows a publisher to see existing change sources
CHANGE_SETS	Allow a publisher to see existing change sets
CHANGE_TABLES	Allows a publisher to see existing change tables
ALL_SOURCE_TABLES	Allows subscribers to see all of the published source tables for which the subscribers have privileges to subscribe
DBA_SOURCE_TABLES	Allows a publisher to see all of the existing (published) source tables
USER_SOURCE_TABLES	Allows the user to see all of the published source tables for which this user has privileges to subscribe
ALL_SOURCE_TAB_COLUMNS	Allows subscribers to see all of the source table columns that have been published, as well as the schema name and table name of the source table
DBA_SOURCE_TAB_COLUMNS	Allows subscribers to see all of the source table columns that have been published, as well as the schema name and table name of the source table
USER_SOURCE_TAB_COLUMNS	Allows users to see all of the source table columns that have been published, as well as the schema name and table name of the source table
ALL_PUBLISHED_COLUMNS	Allows a subscriber to see all of the published source table columns for which the subscriber has privileges
DBA_PUBLISHED_COLUMNS	Allows a subscriber to see all of the published source table columns for which the subscriber has privileges
USER_PUBLISHED_COLUMNS	Allows a user to see all of the published source table columns for which the user has privileges
ALL_SUBSCRIPTIONS	Allows a user to see all current subscriptions
DBA_SUBSCRIPTIONS	Allows a publisher to see all of the subscriptions
USER_SUBSCRIPTIONS	Allows a subscriber to see all of their current subscriptions
ALL_SUBSCRIBED_TABLES	Allows a user to see all of the published tables for which there are subscribers
DBA_SUBSCRIBED_TABLES	Allows a publisher to see all of the published tables to which subscribers have subscribed
USER_SUBSCRIBED_TABLES	Allows a subscriber to see all of the published tables to which the subscriber has subscribed
ALL_SUBSCRIBED_COLUMNS	Allows a user to see all of the published columns for which there are subscribers
DBA_SUBSCRIBED_COLUMNS	Allows a publisher to see all of the columns of published tables to which subscribers have subscribed
USER_SUBSCRIBED_COLUMNS	Allows a publisher to see all of the columns of published tables to which the subscriber has subscribed

Synchronous Mode of Data Capture

Synchronous data capture provides up-to-the-second accuracy because the changes are being captured continuously and in real time on the production system. The change tables are populated after DML operations occur on the source table.

While synchronous mode data capture adds overhead to the system at capture time, it can reduce cost by simplifying the extraction of change data.

Publishing Change Data

This section provides step-by-step instructions for setting up a Change Data Capture system to capture and publish data from one or more Oracle relational source tables. Change Data Capture captures and publishes only committed data.

Step 1: Decide which Oracle Instance will be the Source System

You need to decide which Oracle instance will be the source system that will provide the change data. The publisher needs to gather requirements from the subscribers and determine which source system contains the relevant source tables.

Step 2: Create the Change Tables that will Contain the Changes

You need to create the change tables that will contain the changes to individual source tables. Use the DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to create change tables.

Create a change table for each source table to be published, and decide which columns should be included. For update operations, decide whether to capture old values, new values, or both.

The publisher can set the options_string field of the DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to have more control over the physical properties and tablespace properties of the change tables. The options_string field can contain any option available on the CREATE TABLE DDL statement.

Example: Creating a Change Table

The following example creates a change table that captures changes that happen to a source table. The example uses the sample table SCOTT.EMP.

EXECUTE DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE (OWNER => 'cdc',\


CHANGE_TABLE_NAME => 'emp_ct', \
CHANGE_SET_NAME => 'SYNC_SET', \
SOURCE_SCHEMA => 'scott', \
SOURCE_TABLE => 'emp',\
COLUMN_TYPE_LIST =>. 'empno number, ename varchar2(10), job varchar2(9), mgr     
number, hiredate date, deptno number', \
CAPTURE_VALUES => 'both', \
RS_ID => 'y' \
ROW_ID => 'n', \
USER_ID => 'n', \
TIMESTAMP => 'n', \
OBJECT_ID => 'n', \
SOURCE_COLMAP => 'y', \
TARGET_COLMAP => 'y', \
OPTIONS_STRING => null);

This statement creates a change table named emp_ct within the change set SYNC_SET. The column_type_list parameter identifies the columns captured by the change table. The source_schema and source_table parameters identify the schema and source table that reside on the production system.

The capture_values setting in the example indicates that for UPDATE operations, the change data will contain two separate rows for each row that changed: one row will contain the row values before the update occurred, and the other row will contain the row values after the update occurred.

Managing Change Tables and Subscriptions

This section describes storage management and how the publisher is able to manage change tables and subscriptions.

To ensure that the size of change tables does not grow without limit, Change Data Capture manages the data in change tables and automatically purges change data that is no longer needed. The DBMS_CDC_PUBLISH.PURGE procedure should be called periodically to removed data from change tables that is no longer required. PURGE looks at all active subscription windows to determine which change data is still in use. It will not purge any data as long as subscribers have active subscription windows that reference the change data.

Subscribers must call DBMS_CDC_SUBSCRIBE.PURGE_WINDOW when they are finished using change data. This indicates to CDC that the change data is no longer needed, and that PURGE may safely remove the unneeded rows. Conversely, until all subscribers have called PURGE_WINDOW on their subscription windows, the change data is considered still in use: PURGE will not remove those rows from the change table.

It is possible that a subscriber could fail to call PURGE_WINDOW, with the end result being that a change table would not be purged. The DBA_SUBSCRIPTIONS view helps the publisher determine if this is happening. In extreme circumstances a publisher may decide to drop an active subscription so that space can be reclaimed. An example might be that the subscriber is an applications program that is not calling PURGE_WINDOW as needed. The DBA_CDC_PUBLISH.DROP_SUBSCRIPTION procedure lets the publisher drop active subscriptions if circumstances require it: however, the publisher should first consider that subscribers may still be using the change data. You must use DBMS_CDC_PUBLISH.DROP_SUBSCRIBER_VIEW to drop any subscriber views prior to dropping a subscription using the DBMS_CDC_PUBLISH.DROP_SUBSCRIPTION procedure.

The PURGE procedure normally runs in a job queue, therefore it runs automatically. The publisher can execute PURGE manually at any time however.

Note that it is not possible to drop change tables by using the conventional DROP TABLE statement. If it is necessary to drop a change table, the procedure DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE must be called. This procedure ensures that both the change table itself as well as the CDC metadata for it are both dropped. If you try to use DROP TABLE on a change table, it will raise the error:

ORA-31496 must use DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE to drop change tables

DROP_CHANGE_TABLE procedure also safeguards the publisher from inadvertently dropping a change table while there are active subscribers that are using the change table. If DROP_CHANGE_TABLE is dropped while subscriptions are active, the procedure will fail with the Oracle error:

ORA-31424 change table has active subscriptions

If the publisher really wants to drop the change table in spite of active subscriptions, DROP_CHANGE_TABLE procedure must be called using the parameter FORCE => 'Y'. This tells CDC to override its normal safeguards and allow the change table to be dropped despite active subscriptions. The subscriptions will no longer be valid, and subscribers will lose access to the change data.

Subscribing to Change Data

The subscribers, typically applications, register their interest in one or more source tables, and obtain subscriptions to these tables. Assuming sufficient access privileges, the subscribers may subscribe to any source tables that the publisher has published.

Steps Required to Subscribe to Change Data

The primary role of the subscriber is to access and use the change data. To do this, the subscriber must first determine which source tables are of interest, and then call the procedures in the DBMS_LOGMNR_CDC_SUBSCRIBE package to access them.

Step 1: Find the Source Tables for which the Subscriber has Access Privileges

Query the ALL_SOURCE_TABLES view to see all of the published source tables for which the subscriber has access privileges.

Step 2: Obtain a Subscription Handle

Call the DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE procedure to create a subscription.

The following example shows how the subscriber first names the change set of interest (SYNC_SET), and then returns a unique subscription handle that will be used throughout the session.

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE ( \


CHANGE_SET => 'SYNC_SET',\
DESCRIPTION => 'Change data for emp',\
SUBSCRIPTION_HANDLE => :subhandle);

Step 3: Subscribe to a Source Table and Columns in the Source Table

Use the DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE procedure to specify which columns of the source tables are of interest to the subscriber and are to be captured.

The subscriber identifies the columns of the source table that are of interest. A subscription can contain one source table or multiple tables from the same change set. To see all of the published source table columns for which the subscriber has privileges, query the ALL_PUBLISHED_COLUMNS view.

In the following example, the subscriber wants to see only one source table.

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE (\


SUBSCRIPTION_HANDLE => :subhandle, \
SOURCE_SCHEMA => 'scott', \
SOURCE_TABLE => 'emp', \
COLUMN_LIST => 'empno, ename, hiredate');

Step 4: Activate the Subscription

Use the DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION procedure to activate the subscription.

Subscribers call this procedure when they are finished subscribing to source tables, and are ready to receive change data. Whether subscribing to one or multiple source tables, the subscriber needs to call the ACTIVATE_SUBSCRIPTION procedure only once.

In the following example, the ACTIVATE_SUBSCRIPTION procedure sets the subscription window to empty. At this point, no additional source tables can be added to the subscription.

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION ( \


SUBSCRIPTION_HANDLE => :subhandle);

Step 5: Set the Boundaries to See New Data

Call the DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW procedure to set the upper boundary (called a high-water mark) for a subscription window.

For example:

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW (\


SUBSCRIPTION_HANDLE => :subhandle);

At this point, the subscriber has created a new window that begins where the previous window ends. The new window contains any data that was added to the change table. If no new data has been added, the EXTEND_WINDOW procedure has no effect. To access the new change data, the subscriber must call the CREATE_SUBSCRIBER_VIEW procedure, and select from the new subscriber view that is generated by Change Data Capture.

Step 6: Prepare a Subscriber View

Use the DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW procedure to create and prepare a subscriber view. (You must do this for each change table in the subscription.)

Subscribers do not access data directly from a change table; subscribers see the change data through subscriber views and perform SELECT operations against them. The reason for this is because Change Data Capture generates a view that restricts the data to only the columns to which the application has subscribed, and returns only the rows that the application has not viewed previously. The contents of the subscriber view will not change.

The following example shows how to prepare a subscriber view:

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW ( \


SUBSCRIPTION_HANDLE => :subhandle, \
SOURCE_SCHEMA => 'scott',\
SOURCE_TABLE => 'emp', \
VIEW_NAME => :viewname);

Step 7: Read and Query the Contents of the Change Tables

Use the SQL SELECT statement on the subscriber view to read and query the contents of change tables (within the boundaries of the subscription window). You must do this for each change table in the subscription. For example:

SELECT * FROM CDC#CV$119490;

The subscriber view name, CDC#CV$119490, is a generated name.

Step 8: Drop the Subscriber View

Use the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW procedure to drop the subscriber views.

Change Data Capture guarantees not to change the subscriber view, even if new data has been added. Subscribers continue to have access to a subscriber view until calling the DROP_SUBSCRIBER_VIEW procedure, which indicates the subscriber is finished using the view. For example:

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW (\
SUBSCRIPTION_HANDLE => :subhandle, \
SOURCE_SCHEMA => 'scott', \
SOURCE_TABLE => 'emp');

Step 9: Empty the Old Data from the Subscription Window

Use the DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW procedure to let the Change Data Capture software know that the subscriber no longer needs the data in the current subscription window.

For example:

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW (\
SUBSCRIPTION_HANDLE => :subhandle);

Step 10: Repeat Steps 5 through 9

Repeat steps 5 though 9 as long as you are interested in additional change data.

Step 11: End the Subscription

Use the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure to end the subscription. This is necessary to prevent the change tables from growing without bound. For example:

EXECUTE SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION (\
SUBSCRIPTION_HANDLE => :subhandle);

What Happens to Subscriptions when the Publisher Makes Changes

The Change Data Capture environment is dynamic in nature. The publisher can add and drop change tables at any time. The publisher can also add to and drop columns from existing change tables at any time. The following list describes how changes to the Change Data Capture environment affect subscriptions:

• Subscribers do not get explicit notification if the publisher adds a new change table. The views can be checked to see if new change tables have been added, and whether or not you have access to them.
• If a publisher drops a change table that is currently being subscribed to, the publisher must use the force flag to get a successful drop. It is expected that the publisher will warn subscribers before the force flag is actually used. If the subscribers are unaware of the dropped table, then when the subscriber calls PREPARE_SUBSCRIBER_VIEW procedure, an appropriate exception is generated. This becomes the notification mechanism.
• If the publisher adds a user column to a change table and a new subscription includes this column, then the subscription window starts at the point the column was added.
• If the publisher adds a user column to a change table and a new subscription does not include this newly added column, then the subscription window starts at the low-water mark for the change table thus enabling the subscriber to see the entire table.
• If the publisher adds a user column to a change table, and old subscriptions exist, then the subscription windows remain unchanged.
• Subscribers subscribe to source columns and never to control columns. They can see the control columns that were present at the time of the subscription.
• If the publisher adds a control column to a change table and there is a new subscription, then the subscription window starts at the low-water mark for the change table. The subscription can see the control column immediately. All rows that existed in the change table prior to adding the control column will have the value NULL for the newly added control column field.
• If the publisher adds a control column to a change table, then any existing subscriptions can see the new control column when the window is extended (DBMS_LOGMNR_CDC_PUBLISH.EXTEND_WINDOW procedure) such that the low watermark for the window crosses over the point when the control column was added.

Export and Import Considerations

When exporting or importing change tables for Change Data Capture, consider the following information:

• When change tables are imported, the job queue is checked for a Change Data Capture purge job. If no purge job is found, then one is submitted automatically (using the DBMS_CDC_PUBLISH.PURGE procedure). If a change table is imported, but no subscriptions are taken out before the purge job runs (24 hours later, by default), then all rows in the table will be purged.

  Choose one of the following methods to prevent the purging of data from a change table:

  • Suspend the purge job using the DBMS_JOB package to either disable the job (using the BROKEN procedure) or execute the job sometime in the future when there are subscriptions (using the NEXT_DATE procedure).

    Note: If you disable the purge job by marking it as broken, you need to remember to reset it once subscriptions have been activated. This prevents the change table from growing without bound.

  • Take out a dummy subscription to preserve the change table data until real subscriptions appear. Then, you can drop the dummy subscription.
• When importing data into a source table for which a change table already exists, the imported data is also recorded in any associated change tables.

  Assume that you have a source table Employees that has an associated change table CT_Employees. When you import data into Employees, that data is also recorded in CT_Employees.

• When importing a source table and its change table to a database where the tables did not previously exist, Change Data Capture for that source table will not be established until the import process completes. This protects you from duplicating activity in the change table.
• When exporting a source table and its associated change table, and then importing them into a new instance, the imported source table data is not recorded in the change table because it is already in the change table.
• When importing a change table having the optional control ROW_ID column, the ROW_ID columns stored in the change table have meaning only if the associated source table has not been imported. If a source table is re-created or imported, each row will have a new ROW_ID that is unrelated to the ROW_ID that was previously recorded in a change table.
• Any time a table is exported from one database and imported to another, there is a risk that the import target already has tables or objects with the same name. Moving a change table to a different database where a table exists that has the same name as the source table may result in import errors.
• If you need to move a synchronous change table or its source table, then move both tables together and check the import log for error messages.