Create Change Data Publisher Oracle Tips by Burleson

Oracle 10g Creating and granting privileges to the publisher

In the next step, the source database DBA creates a Change Data Capture publisher (for example, cdcproj) and grants the necessary privileges to the publisher, so that he or she can perform the required Streams operations needed to create Change Data Capture change sources, change sets, and change tables in the staging database.

The source database DBA must grant the global privilege ALL to the publisher on the tables for which the publisher will be creating change tables. For this example, it is assumed that a change table will be created for the pl.project_history table only:

SQL> CREATE USER cdcproj IDENTIFIED BY cdcproj
QUOTA UNLIMITED ON SYSTEM
QUOTA UNLIMITED ON SYSAUX;

User created.

SQL> GRANT CREATE SESSION TO cdcproj;
Grant succeeded.

SQL> GRANT CREATE TABLE TO cdcproj;
Grant succeeded

SQL> GRANT SELECT_CATALOG_ROLE TO cdcproj;
Grant succeeded

SQL> GRANT EXECUTE_CATALOG_ROLE TO cdcproj;
Grant succeeded

SQL> EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcproj');
PL/SQL procedure successfully completed.

SQL> GRANT ALL ON pl.project_history TO cdcproj;
Grant succeeded

Oracle 10g Preparing the source tables

Next, the source database DBA prepares the source tables on the source database for asynchronous Change Data Capture by instantiating each source table so that the underlying Streams environment for Change Data Capture can capture their changes (in our example only one table is instantiated, this step is repeated for all tables where change data capture is to be used):

SQL> BEGIN
2 DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
3 TABLE_NAME => 'pl.project_history');
4 END;
5 /

Oracle 10g Creating the change sets

After instantiating the tables, the publisher executes the dbms_cdc_publish.create_change_set procedure on the staging database to create change sets. Note that when Change Data Capture creates a change set, the Streams capture and apply processes are created, but not started.

The following example creates a change set called project_daily that captures changes starting today, and stops capturing change data 7 days from now.

SQL> CONNECT cdcproj/cdcproj
Connected.
SQL> BEGIN
     2 DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
     3 set_name => 'PROJECT_DAILY',
     4 change_source_name => 'HOTLOG_SOURCE',
     5 begin_date => sysdate,
     6 end_date => sysdate+7,
     7 tablespace_name=>'USERS',
     8 ignore_ddl_events => 'y',
     9 rollback_seg_name=>'\_SYSSMU1$');
    10 END;
/

While the above example works in the beta refresh version of Oracle Database 10g, Oracle assures me that the procedure will not use rollback segments with the introduction of automated undo in 9i and 10g. Instead the format will resemble:

PROCEDURE CREATE_CHANGE_SET

 Argument Name          Type                In/Out Default?
---------------------   ------------------- ------ --------CHANGE_SET_NAME         VARCHAR2            IN
DESCRIPTION             VARCHAR2            IN     DEFAULT
CHANGE_SOURCE_NAME      VARCHAR2            IN
STOP_ON_DDL             CHAR                IN     DEFAULT
SUPPLEMENTAL_PROCESSES  NUMBER              IN     DEFAULT
BEGIN_DATE              DATE                IN     DEFAULT
END_DATE                DATE                IN     DEFAULT

Creating the change tables that will contain the changes to the source tables

Once the change set is created, the publisher uses the dbms_cdc_publish.create_change_table procedure in the staging database to create the change tables.

The publisher can create one or more change tables for each source table to be published, specify which columns should be included, and specify the combination of before and after images of the change data to capture.

Also, the publisher can set the options_string field of the dbms_cdc_publish.create_change_table procedure to exercise more control over the physical, partitioning, and tablespace properties of the change tables. The options_string field can contain any option available in the CREATE TABLE statement.

The next example creates a change table in the staging database that captures changes made to a source table in the source database. The example uses the table pl.project_history.

SQL> BEGIN
2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3 owner => 'cdcproj',
4 change_table_name => 'project_hist_ct',
5 change_set_name => 'PROJECT_DAILY',
6 source_schema => 'PL',
7 source_table => 'PROJECT_HISTORY',
8 column_type_list => 'EMPLOYEE_ID NUMBER(6), START_DATE DATE, END_DATE DATE,
9 PROJ_ID VARCHAR(10), DEPARTMENT_ID NUMBER(4)',
10 capture_values => 'both',
11 rs_id => 'y',
12 row_id => 'n',
13 user_id => 'n',
14 timestamp => 'n',
15 object_id => 'n',
16 source_colmap => 'n',
17 target_colmap => 'y',
18 options_string => NULL);
19 END;
20 /

Oracle 10g Creating the change tables that will contain the changes to the source tables

PL/SQL procedure successfully completed.

This statement creates a change table named project_history_ct within change set project_daily. The column_type_list parameter identifies the columns captured by the change table. The source_schema and source_table parameters identify the schema and source table that reside on the source database.

The capture_values setting in the example indicates that for update operations, the change data will contain two separate rows for each row that changed: one row will contain the before update row values and the other row will contain the after update row values.

Oracle 10g Enabling the change set

Next, the publisher must enable the change set, since asynchronous change sets are always disabled when they are created. Enabling the change set will start the Streams capture and apply processes automatically.

SQL> BEGIN
2 DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
3 change_set_name => 'PROJECT_DAILY',
4 enable_capture => 'y');
5 END;
6 /

PL/SQL procedure successfully completed.