Friday, September 4, 2009

Streams Heartbeat example

#######################################

create table heartbeat4 (last_update date primary key);

ALTER TABLE strmadmin.heartbeat4 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.heartbeat4_queue_table',
queue_name => 'strmadmin.heartbeat4_queue',
queue_user => 'strmadmin');
END;
/


# create the capture process


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmadmin.heartbeat4',
streams_type => 'capture',
streams_name => 'heartbeat4_capture',
queue_name => 'strmadmin.heartbeat4_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

# create the propagation process


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'strmadmin.heartbeat4',
streams_name => 'heartbeat4_propagate',
source_queue_name => 'strmadmin.heartbeat4_queue',
destination_queue_name => 'strmadmin.heartbeat4_queue@p01cfd',
include_dml => true,
include_ddl => true,
source_database => 'p01dwh',
inclusion_rule => true);
END;
/

# set the instantiation number


DECLARE
source_scn NUMBER;
BEGIN
source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@p01cfd(
source_object_name => 'strmadmin.heartbeat4',
source_database_name => 'p01dwh',
instantiation_scn => source_scn);
END;
/

# create the apply rule


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'strmadmin.heartbeat4',
streams_type => 'apply',
streams_name => 'heartbeat4_apply',
queue_name => 'strmadmin.heartbeat4_queue',
include_dml => true,
include_ddl => true,
source_database => 'p01dwh',
inclusion_rule => true);
END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'heartbeat4_apply',
parameter => 'disable_on_error',
value => 'n');
END;
/

# start the capture process

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'heartbeat4_capture');
END;
/

# start the apply process

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'heartbeat4_apply');
END;
/