René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

August 8, 2005: On tracking data changes in a table

Oracle has a feature called Change Data Capture (CDC) which allows to track changes (coming from inserts, updates, deletes) in a table or in multiple tables. CDC uses a publisher-subscriber model. That is, a publisher prepares kind of a channel to which interested subscribers can subscribe and then track the changes.
So, I create three users, one being the publisher, one being the subscriber and one being the owner of the table whose changes the subscriber wants to track: 
connect / as sysdba

create user table_owner     identified by own default tablespace data temporary tablespace temp;
create user subscriber_user identified by sub default tablespace data temporary tablespace temp;
create user publish_user    identified by pub default tablespace data temporary tablespace temp;
Some grants are necessary. All three users need the create session privilege. The table owner needs to be able to create tables and the publisher needs the predefined role execute_catalog_role. 
grant execute_catalog_role, create session to publish_user;
grant                       create session to subscriber_user;
grant create table,         create session to table_owner;

--grant execute on sys.dbms_cdc_publish to publish_user;
Of course, the table owner needs a quota to store the table data. Less apparently, the publisher needs a quota as well, to store data within the change table (see below). 
alter user publish_user quota 10 M on data;
alter user table_owner  quota 10 M on data;
The table owner creates a table and fills some values into it. 
connect table_owner/own

create table cdc_test (a number, b varchar2(10));

insert into cdc_test values (1, 'one'  );
insert into cdc_test values (3, 'three');
insert into cdc_test values (7, 'seven');
The publisher creates a change table that will hold the observed changes. The name of the created change table is controlled by the parameter change_table_name.
SYNC_SET is a predefined change set name. I won't discuss change sets here. I choose SYNC_SET because it's already here and I don't have to create a change set. 
connect publish_user/pub

begin

  dbms_cdc_publish.create_change_table (
    owner             => 'publish_user',
    change_table_name => 'change_table',
    change_set_name   => 'SYNC_SET',
    source_schema     => 'table_owner',
    source_table      => 'cdc_test',
    column_type_list  => 'a number, b varchar2(10)',  
    capture_values    => 'both',                      -- could also be 'new' and 'old'
    rs_id             => 'y',
    row_id            => 'n',
    user_id           => 'n',
    timestamp         => 'n',
    object_id         => 'n',
    source_colmap     => 'y',
    target_colmap     => 'y',
    options_string    => null);
end;
/
Ultimately, the subscriber will see the data within this change table. Therefore, the subscriber must be granted the select right on the change table: 
grant select on change_table to subscriber_user;
The subscriber is now ready to subscribe to the change table. First, he has to create a subscription (create_subscription) which names the subscription and then he can subscribe to that named subscrption (subscribe). 
connect subscriber_user/sub

begin

  dbms_cdc_subscribe.create_subscription(
    change_set_name   => 'SYNC_SET',
    description       => 'This subscription is used for testing',
    subscription_name => 'TEST_SUBSCRIPTION'
  );

  dbms_cdc_subscribe.subscribe(
    subscription_name => 'TEST_SUBSCRIPTION',
    source_schema     => 'table_owner',
    source_table      => 'CDC_TEST',
    column_list       => 'A,B',
    subscriber_view   => 'cdc_view'
  );
end;
/
Subscribing creates a view (whose name is determined by the subscriber_view parameter): 
set tab off
desc cdc_view

Name                        Null?    Type
--------------------------- -------- ------------
OPERATION$                           CHAR(2)
CSCN$                                NUMBER
COMMIT_TIMESTAMP$                    DATE
RSID$                                NUMBER
SOURCE_COLMAP$                       RAW(128)
TARGET_COLMAP$                       RAW(128)
A                                    NUMBER
B                                    VARCHAR2(10)
For our purposes, operation$, commit_timestamp$, a and b are the interesting columns.
The subscription must be activated: 
begin
  dbms_cdc_subscribe.activate_subscription('TEST_SUBSCRIPTION');
end;
/
Let's see what we find in cdc_view: 
connect subscriber_user/sub
select operation$, commit_timestamp$, a, b from cdc_view;
Not very much. Maybe, doing some dml helps: 
connect table_owner/own

insert into cdc_test values (4,'four');
insert into cdc_test values (5,'five');
update cdc_test set b = 'SEVEN' where a = 7;
delete from cdc_test where a = 3;
delete from cdc_test where a = 5;
commit;
Selecting again: 
connect subscriber_user/sub
select operation$, commit_timestamp$, a, b from cdc_view;
Still nothing to see. The view must be populated with the last changes by calling extend_window: 
connect subscriber_user/sub

begin
  dbms_cdc_subscribe.extend_window('TEST_SUBSCRIPTION');
end;
/
Now, the changes are here: 
connect subscriber_user/sub
select operation$, commit_timestamp$, a, b from cdc_view;

OP COMMIT_TI          A B
-- --------- ---------- ----------
I  08-AUG-05          4 four
I  08-AUG-05          5 five
UU 08-AUG-05          7 seven
UN 08-AUG-05          7 SEVEN
D  08-AUG-05          3 three
D  08-AUG-05          5 five
As can be seen, an update results in two actual changes: an update delete (UU) and an update insert (UN).

Cleaning up

connect / as sysdba

drop user subscriber_user cascade;
drop user publish_user    cascade;
drop user table_owner     cascade;
Permalink: http://www.adp-gmbh.ch/blog/2005/august/8.html

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.