Summary of DBMS_LOGMNR_CDC_PUBLISH Subprograms

Through the DBMS_LOGMNR_CDC_PUBLISH package, the publisher creates and maintains change sources, change sets, and change tables, and eventually drops them when they are no longer useful.

Note: To use the DBMS_LOGMNR_CDC_PUBLISH package, you must have the EXECUTE_CATALOG_ROLE privilege, and you must have the SELECT_CATALOG_ROLE privilege to look at all of the views.

Table 26-1 describes the procedures in the DBMS_LOGMNR_CDC_PUBLISH supplied package.

Table 26-1 DBMS_LOGMNR_CDC_PUBLISH Package Subprograms

Subprogram	Description
CREATE_CHANGE_TABLE Procedure	Creates a change table in a specified schema and creates corresponding Change Data Capture metadata.
ALTER_CHANGE_TABLE Procedure	Adds or drops columns for an existing change table, or changes the properties of an existing change table.
DROP_SUBSCRIBER_VIEW Procedure	Allows the publisher to drop a subscriber view from the subscriber's schema. The view must have been created by a prior call to the PREPARE_SUBSCRIBER_VIEW procedure.
DROP_SUBSCRIPTION Procedure	Allows a publisher to drop a subscription that was created with a prior call to the GET_SUBSCRIPTION_HANDLE procedure.
DROP_CHANGE_TABLE Procedure	Drops an existing change table when there is no more activity on the table.
PURGE Procedure	Monitors usage by all subscriptions, determines which rows are no longer needed by subscriptions, and removes the unneeded rows to prevent change tables from growing endlessly.

CREATE_CHANGE_TABLE Procedure

This procedure creates a change table in a specified schema.

Syntax

The following syntax specifies columns and datatypes using a comma-delimited string.

DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE (
     owner                  IN VARCHAR2,
     change_table_name      IN VARCHAR2,
     change_set_name        IN VARCHAR2,
     source_schema          IN VARCHAR2,
     source_table           IN VARCHAR2,
     column_type_list       IN VARCHAR2,
     capture_values         IN VARCHAR2,
     rs_id                  IN CHAR,
     row_id                 IN CHAR,
     user_id                IN CHAR,
     timestamp              IN CHAR,
     object_id              IN CHAR,
     source_colmap          IN CHAR,
     target_colmap          IN CHAR,
     options_string         IN VARCHAR2);

Parameters

Table 26-2 CREATE_CHANGE_TABLE Procedure Parameters

Parameter	Description
owner	Name of the schema that owns the change table.
change_table_name	Name of the change table that is being created.
change_set_name	Name of an existing change set with which this change table is associated. Synchronous change tables must specify SYNC_SET.
source_schema	The schema where the source table is located.
source_table	The source table from which the change records are captured.
column_type_list	Comma-delimited list of columns and datatypes that are being tracked.
capture_values	Set this parameter to one of the following capture values for update operations: OLD: Captures the original values from the source table. NEW: Captures the changed values from the source table. BOTH: Captures the original and changed values from the source table.
rs_id	Adds a column to the change table that contains the row sequence number. This parameter orders the operations in a transaction in the sequence that they were committed in the database. The row sequence ID (rs_id) parameter is optional for synchronous mode. Note: For synchronous mode, the rs_id parameter reflects an operations capture order within a transaction, but you cannot use the rs_id parameter by itself to order committed operations across transactions. Set this parameter to Y or N, as follows: Y: Indicates that you want to add a column to the change table that will contain the row sequence of the change. N: Indicates that you do not want to track the rs_id column.
row_id	Adds a column to the change table that contains the row ID of the changed row in the source table, as follows. Y: Indicates that you want to add a column to the change table that contains the row ID of the changed row in the source table. N: Indicates that you do not want to track the row_id column.
user_id	Adds a column to the change table that contains the user name of the user who entered a DML statement, as follows. Y: Indicates that you want to add a column to the change table that contains the user name of the user who entered a DML statement. N: Indicates that you do not want to track users.
timestamp	Adds a column to the change table that contains the capture timestamp of the change record, as follows: Y: Indicates that you want to add a column to the change table that contains the capture timestamp of the change record. N: Indicates that you do not want to track timestamps.
object_id	Adds a column to the change table that contains the object ID of this change record. This is a control column for object support. Specify Y or N, as follows: Y: Indicates that you want to add a column to the change table that contains the object ID of this change record. N: Indicates that you do not want to track object IDs.
source_colmap	Adds a column to the change table as a change column vector that indicates which source columns actually changed. Specify Y or N, as follows: Y: Indicates that you want to add a column to the change table to track the source columns that have changed. N: Indicates that you do not want to track which source columns changed.
target_colmap	Adds a column to the change table as a column vector indicating which change table user columns actually changed. Specify Y or N, as follows. Y: Indicates that you want to add a column to the change table to track the change table user columns that have changed. N: Indicates that you do not want to track changes which change table user columns changed.
options_string	A string that contains syntactically correct options to be passed to a CREATE TABLE DDL statement. The options string is appended to the generated CREATE TABLE DDL statement after the closing parenthesis that defines the columns of the table. See the Usage Notes for more information.

Exceptions

Table 26-3 CREATE_CHANGE_TABLE Procedure Exceptions

Exception	Description
ORA-31409	One or more of the input parameters to the CREATE_CHANGE_TABLE procedure had invalid values. Identify the incorrect parameters and supply the correct values to the procedure.
ORA-31416	The value specified for the source_colmap parameter is invalid. For synchronous mode, specify either Y or N.
ORA-31417	A reserved column name was specified in a column list or column type parameter. Ensure that the name specified does not conflict with a reserved column name.
ORA-31418	While creating a synchronous change table, the name of the source schema did not match any existing schema name in the database.
ORA-31419	When creating a synchronous change table, the underlying source table did not exist when the procedure was called.
ORA-31420	When creating the first change table, a purge job is submitted to the job queue. Submission of this purge job failed.
ORA-31421	The specified change table does not exist. Check the specified change table name to see that it matches the name of an existing change table.
ORA-31422	Owner schema does not exist.
ORA-31438	Duplicate change table. Re-create the change table with a unique name.
ORA-31450	Invalid value was specified for change_table_name.
ORA-31451	Invalid value was specified for the capture_value. Expecting either OLD, NEW, or BOTH.
ORA-31452	Invalid value was specified. Expecting either Y or N.
ORA-31459	System triggers for DBMS_LOGMRN_CDC_PUBLISH package are not installed.
ORA-31467	No column found in the source table. The OBJECT_ID flag was set to Y on the call to CREATE_CHANGE_TABLE and change table belongs to the synchronous change set. The corresponding object column was not detected in the source table.

Usage Notes

• A change table is a database object that contains the change data resulting from DML statements (INSERT, UPDATE, and DELETE) made to a source table. A given change table can capture changes from only one source table.
• A synchronous change table must belong to the SYNC_SET change set.
• A change table is a database table that maintains the change data in these two types of columns:
  • Source columns identify the columns from the source table to capture. Source columns are copies of actual source table columns that reside in the change table.
  • Control columns maintain special metadata for each change row in the container table. Information such as the DML operation performed, the capture time (timestamp), and changed column vectors are examples of control columns.
• The publisher can control a change table's physical properties, tablespace properties, and so on by specifying the options_string parameter. With the options_string parameter, you can set any option that is valid for the CREATE TABLE DDL statement.
• Do not attempt to control a change table's partitioning properties. When Change Data Capture performs a purge operation to remove rows from a change set, it automatically manages the change table partitioning for you.

  Note: How you define the options_string parameter can have an effect on the performance and operations in a Change Data Capture system. For example, if the publisher places several constraints in the options column, it can have a noticeable effect on performance. Also, if the publisher uses NOT NULL constraints and a particular column is not changed in an incoming change row, then the constraint can cause the entire INSERT operation to fail.

Example

execute DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(OWNER => 'cdc1', \ 
   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 => 'n', \
   TARGET_COLMAP => 'y', \
   OPTIONS_STRING => NULL); 

ALTER_CHANGE_TABLE Procedure

This procedure adds columns to, or drops columns from, an existing change table.

Syntax

The following syntax specifies columns and datatypes as a comma-delimited list.

DBMS_LOGMNR_CDC_PUBLISH.ALTER_CHANGE_TABLE (
     owner                  IN VARCHAR2,
     change_table_name      IN VARCHAR2,
     operation              IN VARCHAR2,
     column_list            IN VARCHAR2,
     rs_id                  IN CHAR,
     row_id                 IN CHAR,
     user_id                IN CHAR,
     timestamp              IN CHAR,
     object_id              IN CHAR,
     source_colmap          IN CHAR,
     target_colmap          IN CHAR);

Parameters

Table 26-4 ALTER_CHANGE_TABLE Procedure Parameters

Parameter	Description
owner	Name of the schema that owns the change table.
change_table_name	Name of the change table that is being altered.
operation	Specifies either the value DROP or ADD to indicate whether to add or drop the columns in the field column_table or column_list.
column_list	A comma-delimited list of column names and datatypes for each column of the source table that should be added to, or dropped from, the change table.
rs_id	Adds or drops the control column that tracks the row sequence (rs_id). Set this parameter to Y or N, as follows: Y: Adds or drops a column on the change table that contains the row sequence (rs_id). N: The rs_id control column is not changed in the change table.
row_id	Adds or drops a row_id column, as follows: Y: Adds or drops the row_id control column for the change table. N: The row_id column is not changed in the change table.
user_id	Adds or drops the user name control column. Specify Y or N, as follows: Y: Adds or drops a column on the change table that contains the user name (user_id). N: The user_id column is not changed in the change table.
timestamp	Adds or drops the timestamp control column to the change table, as follows: Y: Adds or drops a column on the change table that contains the timestamp. N: The timestamp control column is not changed in the change table.
object_id	Add or drops the object_id column, as follows: Y: Adds or drops a column on the change table that contains the object_id. N: The object_id control column is not changed in the change table.
source_colmap	Adds or drops the source_colmap control column from the change table, as follows: Y: Adds or drops a column on the change table that contains the source columns (source_colmap). N: The source_colmap column is not changed in the change table.
target_colmap	Adds or drops the target_colmap control column from the change table, as follows: Y: Adds or drops a column on the change table that contains the target columns (target_colmap). N: The target_colmap column is not changed in the change table.

Exceptions

Table 26-5 ALTER_CHANGE_TABLE Procedure Exceptions  

Exception	Description
ORA-31403	You issued an ALTER_CHANGE_TABLE procedure with an ADD operation but a column by this name already exists in the specified table.
ORA-31409	One or more of the input parameters to the ALTER_CHANGE_SET procedure had invalid values. Identify the incorrect parameters and supply the correct values to the procedure.
ORA-31417	A reserved column name was specified in the column list parameter. Ensure that the name specified does not conflict with a reserved column name.
ORA-31421	The specified change table does not exist. Check the specified change table name to see that it matches the name of an existing change table.
ORA-31423	You issued the ALTER_CHANGE_TABLE with a drop operation and the specified column does not exist in the change table.
ORA-31454	Illegal value was specified for operation parameter; expecting ADD or DROP.
ORA-31455	Nothing to alter. The specified column list is NULL and all optional control columns are N.
ORA-31456	An internal attempt to invoke a procedure within the DBMS_CDC_UTILITY package failed. Check the trace logs for more information.
ORA-31459	One or more required system triggers are not installed.

Usage Notes

• You cannot add and drop user columns in the same call to the ALTER_CHANGE_TABLE procedure; these schema changes require separate calls.
• Do not specify the name of the control columns in the user-column lists.
• The following table describes what happens when you add a column to a change table:

If the publisher adds . . . .	And . . . .	Then . . .
A user column	A new subscription includes this column	The subscription window starts at the point the column was added.
A user column	A new subscription does not include this newly added column	The subscription window starts at the low-water mark for the change table thus enabling the subscriber to see the entire table.
A user column	Old subscriptions exist	The subscription window remains unchanged and the entire table can be seen.
A control column	There is a new subscription	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.
A control column	--	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.

Example

EXECUTE DBMS_LOGMNR_CDC_PUBLISH.ALTER_CHANGE_TABLE (OWNER => 'cdc1') \
   CHANGE_TABLE_NAME => 'emp_ct' \
   OPERATION => ADD \
   ADD_COLUMN_LIST => '' \
   RS_ID => 'Y' \
   ROW_ID => 'N' \
   USER_ID => 'N' \
   TIMESTAMP => 'N' \
   OBJECT_ID => 'N' \
   SOURCE_COLMAP => 'N' \
   TARGET_COLMAP => 'N');

DROP_SUBSCRIBER_VIEW Procedure

This procedure allows a publisher to drop a subscriber view in the subscriber's schema.

Note: This procedure works the same way as the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW procedure.

Syntax

DBMS_LOGMNR_CDC_PUBLISH.DROP_SUBSCRIBER_VIEW (
     subscription_handle    IN NUMBER,
     source_schema          IN VARCHAR2,
     source_table           IN VARCHAR2)

Parameters

Table 26-6 DROP_SUBSCRIBER_VIEW Procedure Parameters

Parameter	Description
subscription_handle	Unique number of the subscription handle that was returned by a previous call to the DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE procedure.
source_schema	Schema name where the source table resides.
source_table	Name of the published source table.

Exceptions

Table 26-7 DROP_SUBSCRIBER_VIEW Procedure Exceptions

Exception	Description
ORA-31425	Subscription handle does not exist or handle does not belong to this user. Call the function again with a valid subscription handle.
ORA-31429	The subscription has not been activated. Check the subscription handle and correct it, if necessary. Call the DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION procedure for this subscription handle and then try the original command again.
ORA-31432	The schema_name.source_table does not exist or does not belong to this subscription. Check the spelling of the schema_name and source_table parameters. Verify the specified table exists in the specified schema and is subscribed to by the subscription handle.
ORA-31433	The subscriber view does not exist. Either you specified an incorrect subscriber view or the view is already dropped. Check the name and specify the name of an existing subscriber view.

Usage Notes

• This procedure provides the publisher with a way to clean up views that have not been removed by the subscriber. (Typically, subscribers drop the subscriber views using the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW procedure.)
• The subscriber view you want to drop must have been created with a prior call to the DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW procedure.
• You must use this procedure to drop any subscriber views prior to dropping a subscription using the DBMS_LOGMNR_CDC_PUBLISH.DROP_SUBSCRIPTION procedure.

Example

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

DROP_SUBSCRIPTION Procedure

This procedure allows a publisher to drop a subscription that was created with a prior call to the DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE procedure.

Note: This procedure works the same way as the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure.

Syntax

DBMS_LOGMNR_CDC_PUBLISH.DROP_SUBSCRIPTION (
     subscription_handle  IN NUMBER)

Parameters

Table 26-8 DROP_SUBSCRIPTION Procedure Parameters

Parameter	Description
subscription_handle	Unique number of the subscription handle that was returned by a previous call to the DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE procedure.

Exceptions

Table 26-9 DROP_SUBSCRIPTION Procedure Exceptions

Exception	Description
ORA-31425	Subscription handle does not exist or handle does not belong to this user. Call the function again with a valid subscription handle.
ORA-31430	The subscriber view was not dropped prior to making this call. Call the DBMS_LOGMNR_CDC_PUBLISH.DROP_SUBSCRIBER_VIEW procedure and then try the original command again.

Usage Notes

• This procedure provides the publisher with a way to drop subscriptions that have not been dropped by the subscriber. (Typically, subscribers drop subscriptions using the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure.)
• Prior to dropping a subscription, you must drop the subscriber view using the DBMS_LOGMNR_CDC_PUBLISH.DROP_SUBSCRIBER_VIEW procedure.

Example

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

DROP_CHANGE_TABLE Procedure

This procedure drops an existing change table.

Syntax

DBMS_LOGMNR_CDC_PUBLISH.DROP_CHANGE_TABLE (
     owner              IN VARCHAR2,
     change_table_name  IN VARCHAR2,
     force_flag         IN CHAR)

Parameters

Table 26-10 DROP_CHANGE_TABLE Procedure Parameters

Parameter	Description
owner	Name of the schema that owns the change table.
change_table_name	Name of the change table that is being dropped.
force_flag	Drops the change table, depending on whether or not there are subscriptions making references to it, as follows: Y: Drops the change table even if there are subscriptions making references to it. N: Drops the change table only if there are no subscribers referencing it.

Exceptions

Table 26-11 DROP_CHANGE_TABLE Procedure Exceptions

Exception	Description
ORA-31421	The specified change table does not exist. Check the specified change table name to see that it matches the name of an existing change table.
ORA-31422	Owner schema does not exist.
ORA-31424	The specified change table has active subscriptions, and thus it cannot be dropped. If you must drop the table, use the force_flag parameter to immediately drop the change table from all of the subscribers.
ORA-31441	Table is not a change table. You attempted to execute the DROP_CHANGE_TABLE procedure on a table that is not a change table.

Example

EXECUTE DBMS_LOGMNR_CDC_PUBLISH.DROP_CHANGE_TABLE ( \
   OWNER => 'cdc1', \
   CHANGE_TABLE_NAME => 'emp_ct' \
   FORCE_FLAG => 'N')

PURGE Procedure

This procedure monitors change table usage by all subscriptions, determines which rows are no longer needed by subscriptions, and removes the unneeded rows to prevent change tables from growing endlessly.

Syntax

DBMS_LOGMNR_CDC_PUBLISH.PURGE ( )

Exceptions

Only standard Oracle exceptions (for example, a privilege violation) are returned during a purge operation.

Usage Notes

• You can run this procedure manually or automatically:
  • Run this procedure manually from the command line at any time that you want to purge data from change tables.
  • Run this procedure in a script to routinely perform a purge operation and proactively control the growth of change tables. You can always remove or disable (or suspend) the purge operation if you want to prevent it from running automatically.
• Use this procedure to control the growth of change tables.
• Do not attempt to control a change table's partitioning properties. When the DBMS_LOGMNR_CDC_PUBLISH.PURGE procedure runs, Change Data Capture performs partition maintenance automatically.

Example

EXECUTE DBMS_LOGMNR_CDC_PUBLISH.PURGE