Tracking Streams Changes with V$STREAMS_MESSAGE_TRACKING

V$STREAMS_MESSAGE_TRACKING is one of the best new features of Oracle Streams 11g. For a full description and an example, check the "Tracking LCRs Through a Stream" section of the documentation. In this post, we'll be creating a procedure after setting a 1-way Streams replication and the message tracking. You'll see how easy to use it is. To shorten this post, I won't be describing how to configure the replication. The 2 scripts attached, should help you to quickly get to it:

• 05_StreamsSchema1WayReplication.sql, assuming you have 2 databases and you can connect with SQL*Plus as oracle, creates a 1-way streams replication of the DEMO schema
• 05_StreamsSchema1WayReplication_CleanUp.sql drops the whole configuration.

Connect to the source database (i.e. BLACK), set the label and create a procedure in the DEMO schema:

. oraenv
BLACK

sqlplus / as sysdba

set serveroutput on

declare
tracking_label  VARCHAR2(15);
begin
dbms_streams_adm.set_message_tracking(
tracking_label => 'ARKZOYD',
actions        => DBMS_STREAMS_ADM.ACTION_MEMORY);
END;
/

create or replace procedure demo.hizoyd is
begin
null;
end;
/

Check the view to see how the message has been managed by Streams:

set lines 120
col component_name format a19
col component_type format a18
col action format a23
col object_owner format a14
col object_name format a14
col command_type format a16
select component_name,
     component_type,
     action,
     object_owner,
     object_name,
     command_type
from V$STREAMS_MESSAGE_TRACKING
where tracking_label='ARKZOYD'
order by timestamp;

Here is the result:

COMPONENT_NAME     COMPONENT_TYPE      ACTION                     OBJECT_OWNER   OBJECT_NAME      COMMAND_TYPE
----------------- ----------------- ----------------------- ------------- -------------- ----------------
STREAMS_CAPTURE     CAPTURE             Created                    Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_CAPTURE     CAPTURE             Rule evaluation            Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_CAPTURE     CAPTURE             Enqueue                    Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_CAPTURE     CAPTURE             Created                                                   COMMIT
STREAMS_CAPTURE     CAPTURE             Enqueue                                                   COMMIT
STREAMS_PROPAGATION PROPAGATION SENDER  Dequeued                   Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION SENDER  Propagation Sender sent    Missing MVDD:0 Missing MVDD:0 CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION SENDER  Dequeued                                                  COMMIT
STREAMS_PROPAGATION PROPAGATION SENDER  Propagation Sender sent                                  COMMIT

The tracking is propagated to the destination database (i.e. WHITE) like you can see below :

exit

. oraenv
WHITE

sqlplus / as sysdba

set pages 20
set lines 120
col component_name format a19
col component_type format a20
col action format a28
col object_owner format a14
col object_name format a14
col command_type format a16
select component_name,
     component_type,
     action,
     object_owner,
     object_name,
     command_type
from V$STREAMS_MESSAGE_TRACKING
where tracking_label='ARKZOYD'
order by timestamp;

Here is the result:

COMPONENT_NAME      COMPONENT_TYPE       ACTION                       OBJECT_OWNER    OBJECT_NAME    COMMAND_TYPE
----------------- ------------------ ------------------------- ------------- -------------- ----------------
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue Missing MVDD:0 Missing MVDD:0  CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation              Missing MVDD:0 Missing MVDD:0  CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation              Missing MVDD:0 Missing MVDD:0  CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue Missing MVDD:0 Missing MVDD:0  CREATE PROCEDURE
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue                                 COMMIT
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation                                              COMMIT
STREAMS_PROPAGATION PROPAGATION RECEIVER Rule evaluation                                              COMMIT
STREAMS_PROPAGATION PROPAGATION RECEIVER Propagation receiver enqueue                                 COMMIT
STREAMS_APPLY       APPLY READER         Dequeued                      Missing MVDD:0 Missing MVDD:0  CREATE PROCEDURE
STREAMS_APPLY       APPLY READER         Dequeued                                                     COMMIT
STREAMS_APPLY       APPLY SERVER         Apply executed                Missing MVDD:0 Missing MVDD:0  CREATE PROCEDURE
STREAMS_APPLY       APPLY SERVER         Commit                                                       COMMIT

And you can check the procedure has been replicated to the WHITE database:

select text    
 from dba_source
 where owner='DEMO'
   and name='HIZOYD'
 order by line;

TEXT
-------------------
procedure hizoyd is
 begin
   null;
 end;