| General Information |
| Purpose |
Public interface for the Change Data Capture Publishers |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmscdcp.sql |
| First Available |
9.0.1 |
| Dependencies |
| CDC_CHANGE_SETS$ |
DBMS_CDC_IPUBLISH |
DBMS_SYS_ERROR |
| CHANGE_SETS |
DBMS_CDC_SYS_IPUBLISH |
DBMS_UTILITY |
| DBA_QUEUE_PUBLISHERS |
DBMS_CDC_UTILITY |
V$BUFFERED_PUBLISHERS |
| DBMS_CDCPUB_LIB |
DBMS_LOGMNR_CDC_PUBLISH |
WRH$_STREAMS_POOL_ADVICE
|
|
Exceptions |
| Exception |
Description |
| ORA-31401 |
Specified change source is not an existing change source |
| ORA-31402 |
Unrecognized parameter specified
|
| ORA-31403 |
Specified change table already contains the specified column
|
| ORA-31406 |
Specified change source is referenced by a change set |
| ORA-31407 |
The end_date must be greater than the begin_date |
| ORA-31408 |
Invalid value specified for begin_scn or end_scn |
| ORA-31409 |
One or more values for input parameters are incorrect |
| ORA-31410 |
Specified change set is not an existing change set |
| ORA-31411 |
Specified change set is referenced by a change table |
| 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 |
Specified owner schema does not exist |
| ORA-31423 |
Specified change table does not contain the specified column |
| ORA-31424 |
Change table has active subscriptions |
| ORA-31425 |
Subscription does not exist |
| ORA-31432 |
Invalid source table |
| ORA-31436 |
Duplicate change source specified |
| ORA-31437 |
Invalid value specified for first_scn |
| ORA-31438 |
Duplicate change table |
| ORA-31441 |
Table is not a 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-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-31467 |
No column found in the source table |
| ORA-31468 |
Cannot process DDL change record |
| ORA-31469 |
Cannot enable Change Data Capture for change set |
| ORA-31471 |
Invalid OBJECT_ID value |
| 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-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-31487 |
Cannot support begin dates or end dates in this configuration |
| ORA-31488 |
Cannot support change set in this configuration |
| 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-31503 |
Invalid date supplied for begin_date or end_date |
| ORA-31504 |
Cannot alter or drop predefined change source |
| 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 |
| ORA-31532 |
Cannot enable change source |
| ORA-31534 |
Change Data Capture publisher is missing DBA role |
| ORA-31535 |
Cannot support change source in this configuration |
|
| Security Model |
Execute is granted to the
EXECUTE_CATALOG_ROLE. Runs under AUTHID CURRENT_USER |
| |
| ALTER_AUTOLOG_CHANGE_SOURCE |
Changes the properties of an existing AutoLog change source
10.1 Publisher Interface |
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); |
| TBD |
| |
| ALTER_CHANGE_SET |
Changes the properties of an existing change set that was created with the CREATE_CHANGE_SET procedure |
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); |
| See CDC Demo 2 |
| |
| ALTER_CHANGE_TABLE
(new 11g parameter) |
Adds columns to, or drops columns from, or changes the properties
of, a change table that was created with the CREATE_CHANGE_TABLE procedure |
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); |
| See CDC Demo 2 |
| |
| ALTER_HOTLOG_CHANGE_SOURCE |
Changes the properties of an existing Distributed HotLog change source
10.2 Publisher Interface |
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); |
| exec
dbms_cdc_publish.alter_hotlog_change_source('HOTLOG_SOURCE', 'CDC Demo 2 Change
Set', 'N', 'Y'); |
| |
| CREATE_AUTOLOG_CHANGE_SOURCE |
Creates an AutoLog change source based on of a set of
redo log files automatically copied by redo transport services to the system on which the staging database resides
10.1 Publisher Interface |
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'); |
| See CDC Demo 3 |
| |
| CREATE_CHANGE_SET |
Allows the publisher to create a change set |
dbms_cdc_publish.create_change_set(
change_set_name IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
change_source_name IN VARCHAR2, -- 'SYNC_SOURCE'
stop_on_ddl IN CHAR DEFAULT 'N',
begin_date IN DATE DEFAULT NULL,
end_date IN DATE DEFAULT NULL); |
| See CDC Demo 2 |
| |
| CREATE_CHANGE_TABLE
(new 11g parameter) |
Creates a change table in a specified
schema |
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'); |
| See Streams Demo 2 |
| |
| CREATE_HOTLOG_CHANGE_SOURCE |
Creates a Distributed HotLog change source on the source database when the publisher runs this procedure from the staging database
10.2 Publisher Interface |
dbms_cdc_publish.create_hotlog_change_source (
change_source_name IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
source_database IN VARCHAR2); -- database link name |
| exec
dbms_cdc_publish.create_hotlog_change_source('HOTLOG_SOURCE', 'CDC Demo 2 Change
Set', 'remotedb'); |
| |
| DROP_CHANGE_SET |
|
Drops an existing change set that was created with the CREATE_CHANGE_SET procedure |
dbms_cdc_publish.drop_change_set(change_set_name IN VARCHAR2); |
| See Streams Demo 2 |
| |
| DROP_CHANGE_SOURCE |
| Drops an existing AutoLog change source that was created with
CREATE_AUTOLOG_CHANGE_SOURCE |
dbms_cdc_publish.drop_change_source(
change_source_name IN VARCHAR2); |
| exec
dbms_cdc_publish.drop_change_source('HOTLOG_SOURCE'); |
| |
| DROP_CHANGE_TABLE |
| Drops an existing change table that was created with CREATE_CHANGE_TABLE |
dbms_cdc_publish.drop_change_table(
owner IN VARCHAR2,
change_table_name IN VARCHAR2,
force_flag IN CHAR); |
| See Streams Demo 2 |
| |
| DROP_SUBSCRIBER_VIEW |
| Drops the view
created by dbms_cdc_subscribe.subscribe |
dbms_cdc_publish.drop_subscriber_view(
subscription_handle IN NUMBER,
source_schema IN VARCHAR2,
source_table IN VARCHAR2); |
| Deprecated |
| |
| DROP_SUBSCRIPTION |
Allows a publisher to drop a
subscriber created subscription
10g Version |
dbms_cdc_publish.drop_subscription(subscription_name IN VARCHAR2); |
| exec dbms_cdc_publish.drop_subscription('CDC_DEMO_SUB'); |
| 9i Version / Deprecated |
dbms_cdc_publish.drop_subscription(subscription_handle IN NUMBER); |
| Deprecated |
| |
| GET_DDLOPER (new
in 11g) |
| Translates the DDLOPR$ value
into text |
dbms_cdc_publish.get_ddloper(ddloper IN BINARY_INTEGER)
RETURN VARCHAR2; |
| TBD |
| |
| PURGE |
| Monitors change table usage by all subscriptions, determines which rows are no longer needed
and removes them |
dbms_cdc_publish.purge; |
| exec dbms_cdc_publish.purge; |
| |
| PURGE_CHANGE_SET
(new 11g parameters ?) |
| Removes unneeded rows from all change tables in the named change set |
dbms_cdc_publish.purge_change_set(
change_set_name IN VARCHAR2,
force IN CHAR
DEFAULT 'Y',
purge_date IN DATE DEFAULT NULL); |
| exec dbms_cdc_publish.purge_change_set('CDC_DEMO_SET'); |
| |
| PURGE_CHANGE_TABLE
(new 11g parameter ?) |
Removes unneeded rows from the named change table |
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); |
| exec dbms_cdc_publish.purge_change_table('CDC_DEMO_CT'); |
