Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

21 DBMS_CDC_PUBLISH

The DBMS_CDC_PUBLISH package, one of a set of Change Data Capture packages, is used by a publisher to set up an Oracle Change Data Capture system to capture and publish change data from one or more Oracle relational source tables.

Change Data Capture captures and publishes only committed data. Oracle Change Data Capture identifies new data that has been added to, updated in, or removed from relational tables, and publishes the change data in a form that is usable by subscribers.

Typically, a Change Data Capture system has one publisher who captures and publishes changes for any number of Oracle relational source tables. The publisher then provides subscribers (applications or individuals) with access to the published data. Subscribers access the published data using the DBMS_CDC_SUBSCRIBE package.

Note:

In previous releases, this package was named DBMS_LOGMNR_CDC_PUBLISH. Beginning with Oracle Database 10g, the LOGMNR string has been removed from the name, resulting in the name DBMS_CDC_PUBLISH. Although both variants of the name are still supported, the variant with the LOGMNR string has been deprecated and may not be supported in a future release.

See Also:

Oracle Database Data Warehousing Guide for information regarding Oracle Change Data Capture

This chapter contains the following topics:


Using DBMS_CDC_PUBLISH

This section contains the following topics, which relate to using the DBMS_CDC_PUBLISH package:


Overview

Through the DBMS_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.

The publisher, typically a database administrator, is concerned primarily with the source of the data and with creating the schema objects that describe the structure of the capture system: change sources, change sets, and change tables.

Most Change Data Capture systems have one publisher and many subscribers. The publisher accomplishes the following main objectives:

  1. Determines which source table changes need to be published.

  2. Decides whether to capture changes asynchronously or synchronously.

  3. Uses the subprograms in the DBMS_CDC_PUBLISH package to capture change data from the source tables and make it available by creating and administering the change source, change set, and change table objects.

  4. 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. (This is necessary to allow the subscribers to subscribe to the change data using the DBMS_CDC_SUBSCRIBE package.)

    See Also:

    Chapter 22, "DBMS_CDC_SUBSCRIBE" for information on the package used to subscribe to published change data

Deprecated Subprograms

Note:

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

The following subprograms are deprecated with Oracle Database 10g:


Security Model

You must have the EXECUTE_CATALOG_ROLE role to use the DBMS_CDC_PUBLISH package. Additional privileges and roles are required depending on the publishing mode and whether the publisher is on the source or staging database. See the section on Granting Privileges and Roles to the Publisher in Oracle Database Data Warehousing Guide for details.


Views

The DBMS_CDC_PUBLISH package uses the views listed in the section on Getting Information About the Change Data Capture Environment in Oracle Database Data Warehousing Guide.


Summary of DBMS_CDC_PUBLISH Subprograms

Table 21-1 describes the subprograms in the DBMS_CDC_PUBLISH supplied package and the mode or modes with which each can be used. A value of All in the Mode column indicates that the subprogram can be used with synchronous and all modes of asynchronous Change Data Capture, a value of Asynchronous in the Mode column indicates that the subprogram can be used with all modes of asynchronous Change Data Capture (HotLog, Distributed HotLog, and AutoLog).

Table 21-1 DBMS_CDC_PUBLISH Package Subprograms

Subprogram Mode Description
ALTER_AUTOLOG_CHANGE_SOURCE Procedure
Asynchronous AutoLog Changes one or more properties of an existing AutoLog change source
ALTER_CHANGE_SET Procedure
All Changes one or more of the properties of an existing change set
ALTER_CHANGE_TABLE Procedure
All Adds or drops columns for an existing change table, or changes the properties of an existing change table
ALTER_HOTLOG_CHANGE_SOURCE Procedure
Asynchronous Distributed HotLog Changes one or more properties of an existing Distributed HotLog change source
CREATE_AUTOLOG_CHANGE_SOURCE Procedure
Asynchronous AutoLog Creates an AutoLog change source
CREATE_CHANGE_SET Procedure All Creates a change set
CREATE_CHANGE_TABLE Procedure
All Creates a change table in a specified schema
CREATE_HOTLOG_CHANGE_SOURCE Procedure
Asynchronous Distributed HotLog Creates a Distributed HotLog change source
DROP_CHANGE_SET Procedure
All Drops an existing change set
DROP_CHANGE_SOURCE Procedure
Asynchronous Autolog and Asynchronous Distributed Hotlog Drops an existing AutoLog or Distributed HotLog change source
DROP_CHANGE_TABLE Procedure
All Drops an existing change table
DROP_SUBSCRIPTION Procedure
All Allows a publisher to drop a subscription that was created by a subscriber
PURGE Procedure
All Removes unneeded rows from all change tables in the staging database
PURGE_CHANGE_SET Procedure
All Removes unneeded rows from all change tables in a specified change set
PURGE_CHANGE_TABLE Procedure
All Removes unneeded rows from a specified change table


ALTER_AUTOLOG_CHANGE_SOURCE Procedure

This procedure changes the properties of an existing AutoLog change source.

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

Table 21-2 ALTER_AUTOLOG_CHANGE_SOURCE Procedure Parameters

Parameter Description
change_source_name Name of an existing AutoLog change source. Change source names follow Oracle schema object naming rules.
description New description of the change source. The description must be specified using 255 or fewer characters.
remove_description A value of 'Y' or 'N'.

If the value is 'Y', then the current description is changed to NULL. If the value is 'N', then the current description is unchanged.

Do not specify the description parameter with this parameter.

first_scn New first SCN.

Exceptions

Table 21-3 ALTER_AUTOLOG_CHANGE_SOURCE Procedure Exceptions

Exception Description
ORA-31401 Specified change source is not an existing change source
ORA-31452 Invalid value for parameter, expecting: Y or N
ORA-31455 Nothing to ALTER
ORA-31497 Invalid value specified for first_scn
ORA-31498 The description and remove_description parameters cannot both be specified
ORA-31499 Null value specified for required parameter
ORA-31501 Specified change source is not an AutoLog change source
ORA-31504 Cannot alter or drop predefined change source
ORA-31507 Specified parameter value longer than maximum length

Usage Notes

See Also:

The section on asynchronous Change Data Capture and redo log files in Oracle Database Data Warehousing Guide for information on how the publisher can use the ALTER_AUTOLOG_CHANGE_SOURCE procedure in making SCN adjustments after determining which redo logs are no longer needed for an asynchronous AutoLog change set.

ALTER_CHANGE_SET Procedure

This procedure changes the properties of an existing change set that was created with the CREATE_CHANGE_SET procedure.

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

Table 21-4 ALTER_CHANGE_SET Procedure Parameters

Parameter Description
change_set_name Name of an existing change set. Change set names follow the Oracle schema object naming rules.
description New description of the change set. Specify using 255 or fewer characters.
remove_description A value of 'Y' or 'N'.

If the value is 'Y', then the current description is changed to NULL. If the value is 'N', then the current description is unchanged.

Do not specify the description parameter with this parameter.

enable_capture A value of 'Y' or 'N'.

If the value is 'Y', then change data capture is enabled for this change set.

If the value is 'N', then change data capture is disabled for this change set.

Synchronous change sets are created with change data capture enabled and cannot be disabled.

Asynchronous change sets are created with change data capture disabled.

recover_after_error A value of 'Y' or 'N'.

If the value is 'Y', then Change Data Capture will attempt to recover from earlier capture errors.

If the value is 'N', then Change Data Capture will not attempt to recover from earlier capture errors.

remove_ddl A value of 'Y' or 'N'.

If the value is 'Y' and the value of the recover_after_error parameter is 'Y', then any DDL records that may have caused capture errors will be filtered out during recovery.

If the value is 'N', then DDL records that may have caused capture errors will not be filtered out during recovery.

This parameter has meaning only when the recover_after_error parameter is specified with a value of 'Y'.

stop_on_ddl A value of 'Y' or 'N'.

If the value is 'Y', then Change Data Capture stops when a DDL event is detected.

If the value is 'N', then Change Data Capture continues when a DDL event is detected.

See the Usage Notes for additional information about this parameter.


Exceptions

Table 21-5 ALTER_CHANGE_SET Procedure Exceptions

Exception Description
ORA-31410 Specified change set is not an existing change set
ORA-31452 Invalid value for parameter, expecting: Y or N
ORA-31455 Invalid lock handle while acquiring lock
ORA-31468 Cannot process DDL change record
ORA-31469 Cannot enable Change Data Capture for change set
ORA-31485 Invalid database link
ORA-31498 The description and remove_description parameters cannot both be specified
ORA-31499 Null value specified for required parameter
ORA-31505 Cannot alter or drop predefined change set
ORA-31507 Specified parameter value longer than maximum length
ORA-31508 Invalid parameter value for synchronous change set
ORA-31514 Change set disabled due to capture error

Usage Notes


ALTER_CHANGE_TABLE Procedure

This procedure adds columns to, or drops columns from, or changes the properties of, a change table that was created with the CREATE_CHANGE_TABLE procedure.

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);

Parameters

Table 21-6 ALTER_CHANGE_TABLE Procedure Parameters

Parameter Description
owner The schema that owns the change table.
change_table_name The change table that is being altered. Change table names follow the Oracle schema object naming rules.
operation Either the value ADD or DROP to indicate whether to add or drop the user columns specified with the column_list parameter and any control columns specified by other parameters.
column_list User column names and datatypes for each column of the source table that should be added to, or dropped from, the change table. The list is comma-delimited.
rs_id

row_id

user_id

timestamp

object_id

source_colmap

target_colmap

Each listed parameter specifies a particular control column, as follows:
  • The rs_id parameter specifies the RSID$ control column.

  • The row_id parameter specifies the ROW_ID$ control column.

  • The user_id parameter specifies the USERNAME$ control column.

  • The timestamp parameter specifies the TIMESTAMP$ control column.

  • The object_id parameter specifies the SYS_NC_OID$ control column.

  • The source_colmap parameter specifies the SOURCE_COLMAP$ control column.

  • The target_colmap parameter specifies the TARGET_COLMAP$ control column.

Each parameter must have a value of either 'Y' or 'N', where:

  • 'Y': Adds the specified control column to, or drops it from the change table, as indicated by the operation parameter.

  • 'N': Neither adds the specified control column, nor drops it from the change table.


See Also:

Oracle Database Data Warehousing Guide for a complete description of control columns.

Exceptions

Table 21-7 ALTER_CHANGE_TABLE Procedure Exceptions

Exception Description
ORA-31403 Specified change table already contains the specified column
ORA-31409 One or more values for input parameters are incorrect
ORA-31415 Specified change set does not exist
ORA-31416 Invalid SOURCE_COLMAP value
ORA-31417 Column list contains control column control-column-name
ORA-31421 Change table does not exist
ORA-31422 Specified owner schema does not exist
ORA-31423 Specified change table does not contain the specified column
ORA-31454 Invalid value specified for operation parameter, expecting ADD or DROP
ORA-31455 Nothing to alter
ORA-31456 Error executing a procedure in the DBMS_CDC_UTILITY package
ORA-31459 System triggers for DBMS_CDC_PUBLISH package are not installed
ORA-31471 Invalid OBJECT_ID value

Usage Notes


ALTER_HOTLOG_CHANGE_SOURCE Procedure

This procedure changes the properties of an existing Distributed HotLog change source.

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

Table 21-8 ALTER_HOTLOG_CHANGE_SOURCE Procedure Parameters

Parameter Description
change_source_name Name of an existing Distributed HotLog change source. Change source names follow Oracle schema object naming rules.
description New description of the change source. The description must be specified using 255 or fewer characters.
remove_description A value of 'Y' or 'N'.

If the value is 'Y', then the current description is changed to NULL. If the value is 'N', then the current description is unchanged.

Do not specify the description parameter with this parameter.

enable_source A value of 'Y' or 'N'.

If the value is 'Y', then the change source is enabled. If the value is 'N', then the change source is disabled.


Exceptions

Table 21-9 ALTER_HOTLOG_CHANGE_SOURCE Procedure Exceptions

Exception Description
ORA-31401 Change source is not an existing change source
ORA-31455 Nothing to ALTER
ORA-31480 Staging database and source database cannot be the same
ORA-31481 Change source is not a HotLog change source
ORA-31482 Invalid option for non-distributed HotLog change source
ORA-31484 Source database must be at least 9.2.0.6 or greater
ORA-31485 Invalid database link
ORA-31498 The description and remove_description parameters cannot both be specified
ORA-31499 Null value specified for required parameter
ORA-31504 Cannot alter or drop predefined change source
ORA-31507 Parameter value longer than maximum length
ORA-31532 Cannot enable change source
ORA-31534 Change Data Capture publisher is missing DBA role

Usage Notes


CREATE_AUTOLOG_CHANGE_SOURCE Procedure

This procedure creates an AutoLog change source. An AutoLog change source is based on of a set of redo log files automatically copied by redo transport services to the system on which the staging database resides.

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

Table 21-10 CREATE_AUTOLOG_CHANGE_SOURCE Procedure Parameters

Parameter Description
change_source_name Name of the change source. Change source names follow the Oracle schema object naming rules.
description Description of the change source. Specify using 255 or fewer characters.
source_database Global name of the change source's source database instance.
first_scn The SCN of the start of a LogMiner dictionary that is in the change source's archived redo log files.
online_log A value of 'Y' or 'N' If the value is 'Y', then the change source uses the AutoLog online option to hot-mine the source database online redo log to gather change data. There can only be one change source with online_log='Y' on a given staging database.

If the value is 'N', then the change source uses the AutoLog archive option to get change data from archived redo log files. There can be one or more change sources with online_log='N' on a given staging database.


Exceptions

Table 21-11 CREATE_AUTOLOG_CHANGE_SOURCE Procedure Exceptions

Exception Description
ORA-31436 Duplicate change source specified
ORA-31497 Invalid value specified for first_scn
ORA-31499 Null value specified for required parameter
ORA-31507 Specified parameter value is longer than the maximum length
ORA-31508 Invalid parameter value for synchronous change set
ORA-31535 Cannot support change source in this configuration

Usage Notes


CREATE_CHANGE_SET Procedure

This procedure allows the publisher to create a change set. For asynchronous HotLog and AutoLog Change Data Capture, the publisher can optionally provide beginning and ending date values at which to begin and end change data capture.

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

Table 21-12 CREATE_CHANGE_SET Procedure Parameters

Parameter Description
change_set_name Name of the change set. Change set names follow the Oracle schema object naming rules.
description Description of the change set. Specify using 255 or fewer characters.
change_source_name Name of the existing change source to contain this change set.
stop_on_ddl A value of 'Y' or 'N'.

If the value is 'Y', then Change Data Capture stops when a DDL event is detected.

If the value is 'N', then Change Data Capture continues when a DDL event is detected.

See the Usage Notes for additional information about this parameter.

begin_date Date on which the publisher wants the change set to begin capturing changes. A value for this parameter is valid for the asynchronous HotLog and AutoLog modes of Change Data Capture only.
end_date Date on which the publisher wants the change set to stop capturing changes. A value for this parameter is valid for the asynchronous HotLog and AutoLog modes of Change Data Capture only.

Exceptions

Table 21-13 CREATE_CHANGE_SET Procedure Exceptions

Exception Description
ORA-31401 Specified change source is not an existing change source
ORA-31407 The end_date must be greater than the begin_date
ORA-31408 Invalid value specified for begin_scn or end_scn
ORA-31437 Duplicate change set specified
ORA-31452 Invalid value for parameter, expecting: Y or N
ORA-31483 Cannot have spaces in the parameter
ORA-31485 Invalid database link
ORA-31487 Cannot support begin dates or end dates in this configuration
ORA-31488 Cannot support change set in this configuration
ORA-31499 Null value specified for required parameter
ORA-31503 Invalid date supplied for begin_date or end_date
ORA-31507 Specified parameter value longer than maximum length
ORA-31508 Invalid parameter value for synchronous change set

Usage Notes


CREATE_CHANGE_TABLE Procedure

This procedure creates a change table in a specified schema.

Note:

Oracle recommends that the publisher be certain that the source table that will be referenced in a CREATE_CHANGE_TABLE procedure has been created prior to calling this procedure, particularly if the change set that will be specified in the procedure has the stop_on_ddl parameter set to 'Y'.

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);

Parameters

Table 21-14 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 table names follow the Oracle schema object naming rules.
change_set_name Name of the change set in which this change table resides.
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 The user columns and datatypes that are being tracked. Specify using a comma-delimited list.
capture_values 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

row_id

user_id

timestamp

object_id

source_colmap

target_colmap

Each listed parameter specifies a particular control column as follows:
  • The rs_id parameter specifies the RSID$ control column.

  • The row_id parameter specifies the ROW_ID$ control column.

  • The user_id parameter specifies the USERNAME$ control column.

  • The timestamp parameter specifies the TIMESTAMP$ control column.

  • The object_id parameter specifies the SYS_NC_OID$ control column.

  • The source_colmap parameter specifies the SOURCE_COLMAP$ control column.

  • The target_colmap parameter specifies the TARGET_COLMAP$ control column.

Each parameter can have a value of 'Y' or 'N', where:

  • 'Y': Adds the specified control column to the change table.

  • 'N': Does not add the specified control column to the change table.

options_string The 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.

See Also:

Oracle Database Data Warehousing Guide for a complete description of control columns

Exceptions

Table 21-15 CREATE_CHANGE_TABLE Procedure Exceptions

Exception Description
ORA-31402 Unrecognized parameter specified
ORA-31409 One or more values for input parameters are incorrect
ORA-31415 Specified change set does not exist
ORA-31416 Invalid SOURCE_COLMAP value
ORA-31417 Column list contains control column control-column-name
ORA-31418 Specified source schema does not exist
ORA-31419 Specified source table does not exist
ORA-31420 Unable to submit the purge job
ORA-31421 Change table does not exist
ORA-31422 Owner schema does not exist
ORA-31438 Duplicate change table
ORA-31447 Cannot create change tables in the SYS schema
ORA-31450 Invalid value for change_table_name
ORA-31451 Invalid value for capture_values, expecting: OLD, NEW, or BOTH
ORA-31452 Invalid value for parameter, expecting: Y or N
ORA-31459 System triggers for DBMS_CDC_PUBLISH package are not installed
ORA-31467 No column found in the source table
ORA-31471 Invalid OBJECT_ID value

Usage Notes


CREATE_HOTLOG_CHANGE_SOURCE Procedure

This procedure creates a Distributed HotLog change source on the source database when the publisher runs this procedure from the staging database. A Distributed HotLog change source is based on data in the online redo log files that is automatically transferred to the staging database by Oracle Streams propagation.

Syntax

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

Parameters

Table 21-16 CREATE_HOTLOG_CHANGE_SOURCE Procedure Parameters

Parameters Description
change_source_name Name of the Distributed HotLog change source to be created. Each change source name must be unique and must follow the Oracle schema object naming rules.
description Description of the change source. Specify using 255 or fewer characters.
source_database The name of the database link defined from the staging database to the source database, where the source database is Oracle9i Database, Database 10g Release 1, or Oracle Database 10gRelease 2. See Oracle Database Data Warehousing Guide for information on creating database links for the Distributed HotLog mode of Change Data Capture.

Exceptions

Table 21-17 CREATE_HOTLOG_CHANGE_SOURCE Procedure Exceptions

Exception Description
ORA-31436 Duplicate change source
ORA-31480 Staging database and source database cannot be the same
ORA-31483 Cannot have spaces in the parameter
ORA-31484 Source database must be at least 9.2.0.6 or greater
ORA-31485 Invalid database link
ORA-31499 Null value specified for required parameter
ORA-31507 Parameter value longer than the maximum length
ORA-31534 Change Data Capture publisher is missing DBA role

Usage Notes


DROP_CHANGE_SET Procedure

This procedure drops an existing change set that was created with the CREATE_CHANGE_SET procedure.

Syntax

DBMS_CDC_PUBLISH.DROP_CHANGE_SET( 
     change_set_name     IN VARCHAR2);

Parameters

Table 21-18 DROP_CHANGE_SET Procedure Parameters

Parameter Description
change_set_name Name of the change set to be dropped. Change set names follow the Oracle schema object naming rules.

Exceptions

Table 21-19 DROP_CHANGE_SET Procedure Exceptions

Exception Description
ORA-31410 Specified change set is not an existing change set
ORA-31411 Specified change set is referenced by a change table
ORA-31485 Invalid database link
ORA-31499 Null value specified for required parameter
ORA-31505 Cannot alter or drop predefined change set
ORA-31507 Specified parameter value is longer than maximum length

Usage Notes


DROP_CHANGE_SOURCE Procedure

This procedure drops an existing AutoLog change source that was created with the CREATE_AUTOLOG_CHANGE_SOURCE procedure or an existing Distributed HotLog change source that was created with the CREATE_HOTLOG_CHANGE_SOURCE procedure.

Syntax

DBMS_CDC_PUBLISH.DROP_CHANGE_SOURCE(
     change_source_name    IN VARCHAR2);

Parameters

Table 21-20 DROP_CHANGE_SOURCE Procedure Parameters

Parameter Description
change_source_name Name of the change source to be dropped. Change source names follow the Oracle schema object naming rules.

Exceptions

Table 21-21 DROP_CHANGE_SOURCE Procedure Exceptions

Exception Description
ORA-31401 Specified change source is not an existing change source
ORA-31406 Specified change source is referenced by a change set
ORA-31499 Null value specified for required parameter
ORA-31504 Cannot alter or drop predefined change source
ORA-31507 Specified parameter value longer than maximum length

Usage Notes


DROP_CHANGE_TABLE Procedure

This procedure drops an existing change table that was created with the CREATE_CHANGE_TABLE procedure.

Syntax

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

Parameters

Table 21-22 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 to be dropped. Change table names follow the Oracle schema object naming rules.
force_flag Drops the change table, depending on whether or not there are subscriptions to it, as follows:
  • 'Y': Drops the change table even if there are subscriptions to it.

  • 'N': Drops the change table only if there are no subscriptions to it.


Exceptions

Table 21-23 DROP_CHANGE_TABLE Procedure Exceptions

Exception Description
ORA-31421 Change table does not exist
ORA-31422 Specified owner schema does not exist
ORA-31424 Change table has active subscriptions
ORA-31441 Table is not a change table

Usage Notes


DROP_SUBSCRIPTION Procedure

This procedure allows a publisher to drop a subscription that was created by a subscriber with a prior call to the DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION procedure.

Syntax

DBMS_CDC_PUBLISH.DROP_SUBSCRIPTION(
     subscription_name  IN VARCHAR2);

Parameters

Table 21-24 DROP_SUBSCRIPTION Procedure Parameters

Parameter Description
subscription_name Name of the subscription that was specified by a previous call to the DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION procedure. Subscription names follow the Oracle schema object naming rules.

Exceptions

Table 21-25 DROP_SUBSCRIPTION Procedure Exceptions

Exception Description
ORA-31409 One or more values for input parameters are incorrect
ORA-31425 Subscription does not exist
ORA-31432 Invalid source table

Usage Notes


PURGE Procedure

This procedure monitors change table usage by all subscriptions, determines which rows are no longer needed by any subscriptions, and removes the unneeded rows to prevent change tables from growing indefinitely. When called, this procedure purges all change tables on the staging database.

Syntax

DBMS_CDC_PUBLISH.PURGE;

Exceptions

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

Usage Notes


PURGE_CHANGE_SET Procedure

This procedure removes unneeded rows from all change tables in the named change set. This procedure allows a finer granularity purge operation than the basic PURGE procedure.

Syntax

DBMS_CDC_PUBLISH.PURGE_CHANGE_SET(
change_set_name IN VARCHAR2);

Parameters

Table 21-26 PURGE_CHANGE_SET Procedure Parameters

Parameter Description
change_set_name Name of an existing change set. Change set names follow the Oracle schema object naming rules.

Exceptions

Table 21-27 PURGE_CHANGE_SET Procedure Exceptions

Exception Description
ORA-31410 Change set is not an existing change set

Usage Notes


PURGE_CHANGE_TABLE Procedure

This procedure removes unneeded rows from the named change table. This procedure allows a finer granularity purge operation than the basic PURGE procedure or the PURGE_CHANGE_SET procedure.

Syntax

DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE(
     owner               IN VARCHAR2,
     change_table_name   IN VARCHAR2);

Parameters

Table 21-28 PURGE_CHANGE_TABLE Procedure Parameters

Parameter Description
owner Owner of the named change table.
change_table_name Name of an existing change table. Change table names follow the Oracle schema object naming rules.

Exceptions

Table 21-29 PURGE_CHANGE_TABLE Procedure Exceptions

Exception Description
ORA-31421 Change table does not exist

Usage Notes