Page 1 of 4

Subject: Synchronous Change Data Capture (CDC) - Example SQL to Set-up in version 9.2
Doc ID: 246551.1 Type: BULLETIN
Modified Date: 23-APR-2009 Status: PUBLISHED

@ Checked for relevance on 23-APR-2009 by Faye Todd

Purpose
=======

This document provides step-by-step examples for setting up a Change Data

Capture system to capture and publish data from one or more source tables.

Create a user named CDC to run the examples.

connect / as sysdba
create user cdc identified by cdc;
grant connect, resource, dba to cdc;
connect cdc/cdc

===============================
Step #1 - Create a Change Table
===============================

This example will create a Change Table named CDC.EMP_CDC that will capture
changes to the source table SCOTT.EMP.

begin
dbms_cdc_publish.create_change_table (
owner => 'cdc',
change_table_name => 'emp_cdc',
change_set_name => 'SYNC_SET',
source_schema => 'scott',
source_table => 'emp',
column_type_list => 'empno number, ename varchar2(10),
job varchar2(9), mgr number, hiredate date, deptno number',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'y',
target_colmap => 'y',
options_string => null);
end;
/

SQL> begin
2 dbms_cdc_publish.create_change_table (
3 owner => 'cdc',
4 change_table_name => 'emp_cdc',
5 change_set_name => 'SYNC_SET',
6 source_schema => 'scott',
7 source_table => 'emp',
8 column_type_list => 'empno number, ename varchar2(10),
9 job varchar2(9), mgr number, hiredate date, deptno number',
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 => 'y',
17 target_colmap => 'y',
18 options_string => null);
19 end;
20 /

PL/SQL procedure successfully completed.

======================================
Step #2 - Obtain a Subscription Handle
======================================

set serveroutput on
variable subhandle number;
begin
dbms_cdc_subscribe.get_subscription_handle(
change_set => 'sync_set',
description => 'Change for scott.emp',
subscription_handle => :subhandle);
dbms_output.put_line('Subhandle = ' || :subhandle);
end;
/

SQL> set serveroutput on

SQL> variable subhandle number;
SQL> begin
2 dbms_cdc_subscribe.get_subscription_handle(
3 change_set => 'sync_set',
4 description => 'Change for scott.emp',
5 subscription_handle => :subhandle);
6 dbms_output.put_line('Subhandle = ' || :subhandle);

https://metalink.oracle.com/CSP/main/article?cmd=show&type=NOT&id=246551.1 9/24/2009
 Page 2 of 4

7 end;
8 /
Subhandle = 21

PL/SQL procedure successfully completed.

Note: You will need this Subscription Handle for the next set of procedures.
This example uses the SQL*Plus variable of "subhandle" to make the value
available during this session.

=====================================================================
Step #3 - Subscribe to a Source Table and Columns in the Source Table
=====================================================================

begin
dbms_cdc_subscribe.subscribe (
subscription_handle => :subhandle,
source_schema => 'scott',
source_table => 'emp',
column_list => 'empno, ename, job, mgr, hiredate, deptno');
end;
/

SQL> begin
2 dbms_cdc_subscribe.subscribe (
3 subscription_handle => :subhandle,
4 source_schema => 'scott',
5 source_table => 'emp',
6 column_list => 'empno, ename, job, mgr, hiredate, deptno');
7 end;
8 /

PL/SQL procedure successfully completed.

===================================
Step #4 - Activate the Subscription
===================================

begin
dbms_cdc_subscribe.activate_subscription (
subscription_handle => :subhandle);
end;
/

SQL> begin
2 dbms_cdc_subscribe.activate_subscription (
3 subscription_handle => :subhandle);
4 end;
5 /

PL/SQL procedure successfully completed.

==========================================
Step #5 Set the Boundaries to See New Data
==========================================

begin
dbms_cdc_subscribe.extend_window (
subscription_handle => :subhandle);
end;
/

SQL> begin
2 dbms_cdc_subscribe.extend_window (
3 subscription_handle => :subhandle);
4 end;
5 /

PL/SQL procedure successfully completed.

===================================
Step #6 - Prepare a Subscriber View
===================================

declare
v_name varchar2(30);
begin
dbms_cdc_subscribe.prepare_subscriber_view (
subscription_handle => :subhandle,
source_schema => 'scott',
source_table => 'emp',
view_name => v_name);
dbms_output.put_line('View Name = ' || v_name);
end;
/

SQL> declare
2 v_name varchar2(30);
3 begin
4 dbms_cdc_subscribe.prepare_subscriber_view (
5 subscription_handle => :subhandle,
6 source_schema => 'scott',
7 source_table => 'emp',
8 view_name => v_name);
9 dbms_output.put_line('View Name = ' || v_name);
10 end;
11 /

https://metalink.oracle.com/CSP/main/article?cmd=show&type=NOT&id=246551.1 9/24/2009
 Page 3 of 4

View Name = CDC#CV$21345247

PL/SQL procedure successfully completed.

==========================================================
Step #7 - Read and Query the Contents of the Change Tables
==========================================================

Use the newly created Subscriber View CDC#CV$21345247 (created in Step #6)

SQL> desc CDC#CV$21345247

Name Null? Type
----------------------------------------- -------- -------------------------
OPERATION$ CHAR(2)
CSCN$ NUMBER
COMMIT_TIMESTAMP$ DATE
TARGET_COLMAP$ RAW(128)
SOURCE_COLMAP$ RAW(128)
RSID$ NUMBER
DEPTNO NUMBER
EMPNO NUMBER
ENAME VARCHAR2(10)
HIREDATE DATE
JOB VARCHAR2(9)
MGR NUMBER

SQL> select * from CDC#CV$21345247;

no rows selected

==================================
Step #8 - Drop the Subscriber View
==================================

begin
dbms_cdc_subscribe.drop_subscriber_view (
subscription_handle => :subhandle,
source_schema => 'scott',
source_table => 'emp');
end;
/

SQL> begin
2 dbms_cdc_subscribe.drop_subscriber_view (
3 subscription_handle => :subhandle,
4 source_schema => 'scott',
5 source_table => 'emp');
6 end;
7 /

PL/SQL procedure successfully completed.

=========================================================
Step #9 - Empty the Old Data from the Subscription Window
=========================================================

begin
dbms_cdc_subscribe.purge_window (
subscription_handle => :subhandle);
end;
/

SQL> begin
2 dbms_cdc_subscribe.purge_window (
3 subscription_handle => :subhandle);
4 end;
5 /

PL/SQL procedure successfully completed.

=====================================
Step #10 - Repeat Steps #5 through #9
=====================================

Repeat where you are interested in additional change data.

===============================
Step #11 - End the Subscription
===============================
Use the DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure to
end the subscription.
This is necessary to prevent the change tables from growing without bound.
For example:

begin
sys.dbms_logmnr_cdc_subscribe.drop_subscription (
subscription_handle => :subhandle);
end;
/

SQL> begin
2 dbms_logmnr_cdc_subscribe.drop_subscription (
3 subscription_handle => :subhandle);
4 end;
5 /

PL/SQL procedure successfully completed.

https://metalink.oracle.com/CSP/main/article?cmd=show&type=NOT&id=246551.1 9/24/2009
 Page 4 of 4

Usage Notes
=====
where relevant ,
1. dbms_cdc_subscribe.create_subscription() can be used to create a
named subscription;
2. dbms_cdc_subscribe.subscribe() can be used to to create a named
view.

For more details refer to the Data Warehousing Guide.

Feature Information
=====

1) Change tables for synchronous mode of Change Data Capture must reside locally
in the source database. (this is the only mode available in Oracle9i)
2) In order to use Change Data Capture, Java must be installed in the database.
3) The script initcdc.sql is provided to install CDC system triggers and Java
classes needed by CDC, should CDC need to be reinstalled.

Related Dictionary Views

========================

CHANGE_SOURCES
CHANGE_SETS
CHANGE_TABLES
DBA_SOURCE_TABLES
DBA_SOURCE_TAB_COLUMNS
DBA_PUBLISHED_COLUMNNS
DBA_SUBSCRIPTIONS
DBA_SUBSCRIBED_TABLES
DBA_SUBSCRIBED_COLUMNS

References
==========
Oracle10g Data Warehousing Guide Release 2 (10.2)
Oracle10g Supplied PL/SQL Packages and Types Reference Release 2 (10.2)
Note 359416.1 How to enable use of Synchronous Change Data Capture in a
Logical Standby Database
Note 313268.1 Synchronous Change Data Capture Does Not Work With Clob Columns ORA-31513

https://metalink.oracle.com/CSP/main/article?cmd=show&type=NOT&id=246551.1 9/24/2009