Change Data capture

Introduction
Capturing the changed records in tables is crucial for ETL applications. Comparing yesterday's table with today's table the records that are updated, deleted or inserted has to be identified.

Finding the changes in data records in Oracle is achieved using SQL statements given below.

SQL for CDC

Using simple SQL commands it is possible to identify the modified records. Consider the employee table with empno as primary key. We have to get the empno and the flag which indicates either 'I' for Insert, 'U' for update and 'D' for delete.

First create a table which stores the above information

create table employee_cdc(
        empno         number
        flag          char(1));

We need to compare employee_old with employee_new table.

insert into employee_cdc
select empno, min(flg) flg from
(
select empno, 'D' flg from employee_old
union
select empno, 'I' flg from employee_new
) GROUP BY empno having count(*)=1;

insert into employee_cdc
select empno, 'U' from(
select empno from employee_old
MINUS
(
 select empno from
 (select * from employee_old intersect select * from employee_new)
)
MINUS
select empno from employee_cdc;

Now the employee_cdc table can be used to identify records that have been modified.

Example to demonstrate Changed Records Identification

Create a test table which can be dropped during the demo.

SQL> create table test_tab1 as select * from user_objects;

Table created.

Table to record Change Records

SQL> create table myobj_cdc_old(object_id number, operation char(1));

Table created.

Create table with sample data - Old Table

SQL> create table myobj_old as select object_id, object_name, object_type, created, last_ddl_time from user_objects;

Table created.

Make changes to Schema


SQL> drop table test_tab1;

Table dropped.

SQL> create table test_tab2 as select * from user_objects;

Table created.

SQL> rename myobj_cdc_old to myobj_cdc;

Table truncated.

Table with sample data - New Table

SQL> create table myobj_new as select object_id, object_name, object_type, created, last_ddl_time from user_objects; Table created.

Identify deletes and inserts and Populate the CDC table

SQL> insert into myobj_cdc
  2  select object_id, min(flg) from
  3  (
  4  select object_id, 'D' flg from myobj_old
  5  union
  6  select object_id, 'I' flg from myobj_new
  7  ) GROUP BY object_id having count(*)=1;

2 rows created.

Identify Updates

SQL> insert into myobj_cdc
  2  select object_id, 'U' from(
  3  select object_id from myobj_old
  4  MINUS
  5  (
  6   select object_id from
  7   (select * from myobj_old intersect select * from myobj_new)
  8  )
  9  MINUS
 10  select object_id from myobj_cdc);

1 row created.

Query for Displaying Changed Records

SQL> select 'Object '||rpad(nvl(a.object_name,b.object_name),30,' ')||' '||decode(c.operation, 'I', 'Created', 'D', 'Deleted', 'Altered')
  2  from myobj_old a, myobj_new b, myobj_cdc c
  3  where a.object_id(+)=c.object_id and
  4  b.object_id(+)=c.object_id;
Object MYOBJ_CDC_OLD                  Altered
Object TEST_TAB2                      Created
Object MYOBJ_NEW                      Created
Object TEST_TAB1                      Deleted

4 rows selected.

SQL> spool off

Change Data capture

My Oracle Debug Guide