33 DBMS_CDC_PUBLISH

Syntax

DBMS_CDC_PUBLISH.ALTER_AUTOLOG_CHANGE_SOURCE(
     change_source_name  IN VARCHAR2,
     description         IN VARCHAR2 DEFAULT NULL,
     remove_description  IN CHAR DEFAULT 'N',
     first_scn           IN NUMBER DEFAULT NULL);

Parameters

Exceptions

Usage Notes

• Properties supplied to this procedure with a NULL value are unchanged.

• This procedure can be used to change more than one property at a time.

• This procedure can be used in making SCN adjustments after determining which redo logs are no longer needed for an asynchronous AutoLog change set.

Syntax

DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
     change_set_name         IN VARCHAR2,
     description             IN VARCHAR2 DEFAULT NULL,
     remove_description      IN CHAR DEFAULT 'N',
     enable_capture          IN CHAR DEFAULT NULL,
     recover_after_error     IN CHAR DEFAULT NULL,
     remove_ddl              IN CHAR DEFAULT NULL,
     stop_on_ddl             IN CHAR DEFAULT NULL);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture. However, the predefined synchronous change set, SYNC_SET, cannot be altered, and the following parameters cannot be altered for publisher-defined synchronous change sets: enable_capture, recover_after_error, remove_ddl, and stop_on_ddl.

• Properties supplied to this procedure with a NULL value are unchanged.

• This procedure can alter more than one parameter at a time.

• Enabling or disabling an asynchronous HotLog or AutoLog change set starts or stops the Oracle Streams capture process and apply process underlying the change set. Enabling or disabling an asynchronous Distributed HotLog change set starts or stops the Oracle Streams apply process underlying the change set.

• The effect of the stop_on_ddl parameter is as follows:

  • When the stop_on_ddl parameter is set to 'Y', asynchronous Change Data Capture stops if DDL is encountered during change data capture. Some DDL statements can adversely affect capture, such as a statement that drops a source table column that is being captured. The publisher has an opportunity to analyze and adjust to DDL changes that may adversely affect change tables while capture is stopped, thereby preventing possible errors during capture.

    Because these statements do not affect the column data itself, Change Data Capture does not stop capturing change data when the stop_on_ddl parameter is set to 'Y' and any of the following statements is encountered:

    • ANALYZE TABLE

    • LOCK TABLE

    • GRANT privileges to access a table

    • REVOKE privileges to access a table

    • COMMENT on a table

    • COMMENT on a column

    These statements can be issued on the source database without concern for their impact on Change Data Capture processing.

  • When the stop_on_ddl parameter is set to 'N', Change Data Capture does not stop if DDL is encountered during change data capture. If a change set does not stop on DDL, but a DDL change occurs that affects change tables, that change can result in a capture error. There are also system conditions that can cause capture errors, such as being out of disk space.

    See Also:

    Oracle Database Data Warehousing Guide for information on the effects of, and how to recover from, a capture error

  Whenever a DDL statement causes processing to stop, a message is written to the alert log indicating for which change set processing has been stopped and the DDL statement that caused it to be stopped. Similarly, whenever DDL statements are ignored by Change Data Capture and processing continues, a message is written to the alert log indicating which DDL statement was ignored.

• The publisher can attempt to recover an asynchronous change set after a capture error by specifying 'Y' for the recover_after_error parameter. Capture errors can occur when any of the following is true:

  • The stop_on_ddl parameter is set to 'Y' and there is a DDL record in the change data. In this case, to recover from the error, the publisher must also specify 'Y' for the remove_ddl parameter.

  • The stop_on_ddl parameter is set to 'N' and there is a DDL record that affects capture. For example, if the publisher drops and re-creates a change table, it causes an error the next time that Change Data Capture attempts to add change data to the named change table.

  • A miscellaneous error occurs, such as running out of disk space, or a redo log file error (such as ORA-01688: unable to extend table string.string partition string by string in tablespace string).

    See Also:

    Oracle Database Data Warehousing Guide for more information on how to recover from a capture error.

Syntax

DBMS_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,
     ddl_markers            IN CHAR  DEFAULT NULL);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• The publisher cannot add and drop user columns in the same call to the ALTER_CHANGE_TABLE procedure; these schema changes require separate calls.

• The publisher must not specify the name of the control columns in the column_ list parameter.

• When altering an asynchronous change table, the publisher must accept the default value or specify 'N' for the source_colmap and object_id parameters. In addition, for the asynchronous Distributed HotLog mode, the publisher also must accept the default value or specify 'N' for the row_id and username parameters when the change source is 9.2 or 10.1.

  See Also:

  Oracle Database Data Warehousing Guide for information about the impact on subscriptions when a publisher adds a column to a change table.

Syntax

DBMS_CDC_PUBLISH.ALTER_HOTLOG_CHANGE_SOURCE(
     change_source_name  IN VARCHAR2,
     description         IN VARCHAR2 DEFAULT NULL,
     remove_description  IN CHAR DEFAULT 'N',
     enable_source       IN CHAR DEFAULT NULL);

Parameters

Exceptions

Usage Notes

• Properties supplied to this procedure with a NULL value are unchanged.

• This procedure can be used to change more than one property at a time.

• Enabling or disabling a Distributed HotLog change source starts or stops the Oracle Streams capture process that underlies the change source.

• This procedure cannot be used to alter the change source for the asynchronous HotLog mode of Change Database Capture. The change source for the asynchronous HotLog mode is the predefined change source, HOTLOG_SOURCE, which cannot be altered.

Syntax

DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
     change_source_name  IN VARCHAR2,
     description         IN VARCHAR2 DEFAULT NULL,
     source_database     IN VARCHAR2,
     first_scn           IN NUMBER,
     online_log          IN CHAR DEFAULT 'N');

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous Change Data Capture only.

• The publisher must take care when specifying a value for the source_database parameter. Change Data Capture does not validate this value when creating the change source. The publisher can query the GLOBAL_NAME column in the GLOBAL_NAME view at the source database for the source_database parameter value.

• The publisher must configure redo transport services to automatically copy the log files to the system on which the staging database resides.

  See Also:

  The section on performing asynchronous AutoLog publishing in Oracle Database Data Warehousing Guide for information on configuring redo transport services to automatically copy the log files to the system on which the staging database resides.

• An AutoLog change source must begin with an archived redo log file that contains a LogMiner dictionary. The CREATE_AUTOLOG_CHANGE_SOURCE first_scn parameter indicates the SCN for this dictionary extraction and is the point at which the change source can begin capturing changes. The publisher can determine the value for the first_scn parameter using either of the following methods:

  • Direct DBMS_CAPTURE_ADM.BUILD to return the value when the dictionary is built:

    SET SERVEROUTPUT ON
    VARIABLE FSCN NUMBER;
    BEGIN
     :FSCN := 0;
     DBMS_CAPTURE_ADM.BUILD(:FSCN);
     DBMS_OUTPUT.PUT_LINE('The first_scn value is ' || :FSCN);
    END;
    /
    The first_scn value is 207722
    

  • Make the following query on the source database. If this query returns multiple distinct values for first_change#, then the data dictionary has been extracted more than once and the publisher should choose the first_change# value that is the most appropriate to the change source.

    SELECT DISTINCT FIRST_CHANGE#, NAME 
       FROM V$ARCHIVED_LOG
       WHERE DICTIONARY_BEGIN = 'YES';
    

    See Also:

    The section on performing asynchronous AutoLog publishing in Oracle Database Data Warehousing Guide for information on archived redo log files and the LogMiner dictionary.

• For the asynchronous mode of Change Data Capture, the amount of change data captured is dependent on the level of supplemental logging enabled at the source database.

  See Also:

  Oracle Database Data Warehousing Guide for information about supplemental logging.

Syntax

DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
     change_set_name        IN VARCHAR2,
     description            IN VARCHAR2 DEFAULT NULL,
     change_source_name     IN VARCHAR2,
     stop_on_ddl            IN CHAR DEFAULT 'N',
     begin_date             IN DATE DEFAULT NULL,
     end_date               IN DATE DEFAULT NULL);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture. However, the default values for the following parameters are the only supported values for synchronous change sets: begin_date, end_date, and stop_on_ddl. The default values for the following parameters are the only supported values for asynchronous Distributed HotLog change sets: begin_date and end_date.

• When the change source is Distributed HotLog on a release of Oracle Database earlier than 10.2, Change Data Capture inserts rows into the CHANGE_PROPAGATION and CHANGE_PROPAGATION_SETS views on the staging database.

• An AutoLog online change source (created with online_log='Y') can only contain one change set.

• The begin_date and end_date parameters are optional. The publisher can specify neither of them, one of them, or both. The effect of these parameters is as follows:

  • When a begin_date is specified, changes from transactions that begin on or after that date are captured.

  • When a begin_date is not specified, capture starts with the earliest available change data.

  • When an end_date is specified, changes from transactions that are committed on or before that date are captured.

  • When an end_date is not specified, Change Data Capture continues indefinitely.

• The effect of the stop_on_ddl parameter is as follows:

  • When the stop_on_ddl parameter is set to 'Y', asynchronous Change Data Capture stops if DDL is encountered during change data capture. Some DDL statements can adversely affect capture, such as a statement that drops a source table column that is being captured. The publisher has an opportunity to analyze and adjust to DDL changes that may adversely affect change tables while capture is stopped, thereby preventing possible errors during capture.

    Because these statements do not affect the column data itself, Change Data Capture does not stop capturing change data when the stop_on_ddl parameter is set to 'Y' and any of the following statements is encountered:

    • ANALYZE TABLE

    • LOCK TABLE

    • GRANT privileges to access a table

    • REVOKE privileges to access a table

    • COMMENT on a table

    • COMMENT on a column

    These statements can be issued on the source database without concern for their impact on Change Data Capture processing.

  • When the stop_on_ddl parameter is set to 'N', Change Data Capture does not stop if DDL is encountered during change data capture. If a change set does not stop on DDL, but a DDL change occurs that affects capture, that change can result in a capture error.

    See Also:

    Oracle Database Data Warehousing Guide for information on the effects of, and how to recover from, a capture error.

  Whenever a DDL statement causes processing to stop, a message is written to the alert log indicating for which change set processing has been terminated and the DDL statement that caused it to be terminated. Similarly, whenever DDL statements are ignored by Change Data Capture and processing continues, a message is written to the alert log indicating which DDL statement was ignored.

Syntax

DBMS_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,
     ddl_markers            IN CHAR  DEFAULT 'Y');

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• A change table is a database table 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 change table is a database table that contains two types of columns:

  • User columns, which are copies of actual columns of source tables that reside in the change table.

  • Control columns, which maintain special metadata for each change row in the change table. Information such as the DML operation performed, the capture time (time stamp), and changed column vectors are examples of control columns. The publisher must not specify the name of the control columns in the user column list.

• If there are multiple publishers on the staging database for the Distributed HotLog mode of Change Data capture, and one publisher defines a change table in another publisher's Distributed HotLog change set, then Change Data Capture uses the database link established by the publisher who created the change set to access the source database. Therefore, the database link to the source database established by the publisher who created the change set must be intact for the change table to be successfully created. If the change set publisher's database link is not present when creating a change table, an error is returned indicating that the connection description for the remote database was not found.

• The publisher must not attempt to control a change table's partitioning properties. Change Data Capture automatically manages the change table partitioning as part of its change table management.

• When creating a change table for any mode of asynchronous Change Data Capture, the publisher must accept the default value or specify 'N' for the source_colmap and object_id parameters. In addition, for the asynchronous Distributed HotLog mode of Change Data Capture, the publisher also must accept the default value or specify 'N' for the row_id and username parameters when the change source is 9.2 or 10.1.

• When the publisher specifies the rs_id parameter, the RSID$ column is added to the change table. The RSID$ column value reflects an operation's capture order within a transaction, but not across transactions. The publisher cannot use the RSID$ column value by itself to order committed operations across transactions; it must be used in conjunction with the CSCN$ column value.

• 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, the publisher can set any option that is valid for the CREATE TABLE DDL statement (except for partitioning properties).

  Note:

  How the publisher defines 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 INSERT operation to fail and the transaction that contains the INSERT operation to be rolled back.

• Oracle recommends that change tables not be created in system tablespaces. This can be accomplished if the publisher's default tablespace is not the system tablespace or if the publisher specifies a tablespace in the options_string parameter. If a tablespace is not specified by the publisher, and the publisher's default table space is the system tablespace, then Change Data Capture creates change tables in the system tablespace.

  See Also:

  Oracle Database Data Warehousing Guide for more information on, and examples of, creating change tables in tablespaces managed by the publisher.

Syntax

DBMS_CDC_PUBLISH.CREATE_HOTLOG_CHANGE_SOURCE(
     change_source_name     IN VARCHAR2,
     description            IN VARCHAR2 DEFAULT NULL,
     source_database        IN VARCHAR2);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for the asynchronous Distributed HotLog mode of Change Data Capture only.

  This procedure cannot be used to create a change source for the asynchronous HotLog mode of Change Database Capture. The publisher must use the predefined change source, HOTLOG_SOURCE, for the asynchronous HotLog mode of Change Data Capture.

• A Distributed HotLog change source can contain one or more change sets, but they must all be on the same staging database.

• A staging database publisher cannot create multiple Distributed HotLog change sources with the same name, even when those change sources are on different source databases.

• When the publisher creates a change source on a release of Oracle Database earlier than 10.2, Change Data Capture:

  • Generates names for the Streams capture process, capture queue, and propagation based on the change source name. If a generated name is already in use, an error indicating that the capture process, queue, or propagation cannot be created is returned.

  • Inserts a row into the CHANGE_SOURCES view on the staging database where the SOURCE_TYPE column of the inserted row indicates that the source Oracle Database release is earlier than 10.2.

• Note that the database link indicated by the source_database parameter must exist when creating, altering, or dropping a Distributed HotLog change source and the change sets and change tables it contains. However, this database link is not required for change capture to occur. Once the required Distributed HotLog change sources, change sets and change tables are in place and enabled, this database link can be dropped without interrupting change capture. This database link would need to be recreated to create, alter, or drop Distributed HotLog change sources, change sets and change tables.

Syntax

DBMS_CDC_PUBLISH.DROP_CHANGE_SET(
     change_set_name     IN VARCHAR2);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• The change set to be dropped cannot contain any change tables.

• The predefined synchronous change set, SYNC_SET, cannot be dropped.

Syntax

DBMS_CDC_PUBLISH.DROP_CHANGE_SOURCE(
     change_source_name    IN VARCHAR2);

Parameters

Exceptions

Usage Notes

• The change source to be dropped cannot contain any change sets.

• The predefined change sources, HOTLOG_SOURCE and SYNC_SOURCE, cannot be dropped.

Syntax

DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE(
     owner              IN VARCHAR2,
     change_table_name  IN VARCHAR2,
     force_flag         IN CHAR);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• If the publisher wants to drop a change table while there are active subscriptions to that table, he or she must call the DROP_CHANGE_TABLE procedure using the force_flag => 'Y' parameter. This tells Change Data Capture to override its normal safeguards and allow the change table to be dropped despite active subscriptions. The subscriptions that include the dropped table will no longer be valid, and subscribers will lose access to the change data.

Syntax

DBMS_CDC_PUBLISH.DROP_SUBSCRIPTION(
     subscription_name  IN VARCHAR2);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• This procedure works the same way as the DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure.

• This procedure provides the publisher with a way to drop subscriptions that have not been dropped by the subscriber. It is possible that a subscription that is no longer needed still exists and is holding change data in a change table indefinitely. The publisher can use this procedure to remove such a subscription so that a purge operation can clean up its change data. Oracle recommends that the publisher attempt to verify that the subscription is not needed prior to dropping it. If that is not possible, the publisher should inform the subscription owner that the subscription has been dropped. Ideally, subscribers drop subscriptions that are no longer needed using the DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure and the publisher need not use the DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure.

Syntax

DBMS_CDC_PUBLISH.PURGE;

Exceptions

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

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• The publisher can run this procedure manually or automatically:

  • The publisher can run this procedure manually from the command line to purge data from change tables.

  • The publisher can run this procedure in a script to routinely perform a purge operation and control the growth of change tables.

• Note that the DBMS_CDC_PUBLISH.PURGE procedure (used by the publisher and the Change Data Capture default purge job) is distinct from the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure (used by subscribers). A call to the DBMS_CDC_PUBLISH.PURGE procedure physically removes unneeded rows from change tables. A call to the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure, logically removes change rows from a subscription window, but does not physically remove rows from the underlying change tables.

Syntax

DBMS_CDC_PUBLISH.PURGE_CHANGE_SET(

    change_set_name IN VARCHAR2,
    force           IN CHAR DEFAULT 'Y',
    purge_date      IN DATE DEFAULT NULL);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• The publisher can run this procedure manually from the command line or in a script to purge unneeded rows from change tables in a specific change set.

• Note that the DBMS_CDC_PUBLISH.PURGE_CHANGE_SET procedure (used by the publisher) is distinct from the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure (used by subscribers). A call to the DBMS_CDC_PUBLISH.PURGE_CHANGE_SET procedure physically removes unneeded rows from change tables in the specified change set. A call to the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure, logically removes change rows from a subscription window, but does not physically remove rows from the underlying change tables.

Syntax

DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE(
     owner               IN VARCHAR2,
     change_table_name   IN VARCHAR2,
     force               IN CHAR DEFAULT 'Y',
     purge_date          IN DATE DEFAULT NULL);

Parameters

Exceptions

Usage Notes

• The publisher can use this procedure for asynchronous and synchronous Change Data Capture.

• The publisher can run this procedure manually from the command line or in a script to purge unneeded rows from a specified change table.

• Note that the DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE procedure (used by the publisher) is distinct from the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure (used by subscribers). A call to the DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE procedure physically removes unneeded rows from the specified change table. A call to the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure, logically removes change rows from a subscription window, but does not physically remove rows from the underlying change tables.