25
DBMS_LOGMNR_CDC_PUBLISH

Oracle Change Data Capture identifies new data that has been added to, modified, or removed from relational tables and publishes the changed data in a form that is usable by an application.

This chapter describes how to use the DBMS_LOGMNR_CDC_PUBLISH supplied package to set up an Oracle 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.

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

See Also: Oracle9i Data Warehousing Guide for more information about the Oracle Change Data Capture publish and subscribe model.

This chapter discusses the following topics:

• Publishing Change Data

• Summary of DBMS_LOGMNR_CDC_PUBLISH Subprograms

Publishing Change Data

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. Determine which source table changes need to be published.

2. Use the procedures in the DBMS_LOGMNR_CDC_PUBLISH package to capture change data and makes it available from the source tables by creating and administering the change source, change set, and change table objects.

3. Allow 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, usually applications, to use the DBMS_LOGMNR_CDC_SUBSCRIBE procedure to subscribe to the change data.

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 25-1 describes the procedures in the DBMS_LOGMNR_CDC_PUBLISH supplied package.

Table 25-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.