Oracle Streams: SET_TAG and Capture Rules (Skip DDL & DML)

If you ever tried to use dbms_streams.set_tag function to avoid replicating DDL or DML in your Streams environment, we’ll you unpleasantly surprised that it doesn’t work (I hope you didn’t try it on production right way ).

This post deals with steps you have to implement to get it working.

First, every entry in the redo log files has a tag associated with it. The default value for this tag is null. The capture process default behavior is not to check for tags at all, in other words, it will read and process every entry in the redo log regardless of it’s tag value. But what we want is for the capture process to read and process null tags only and ignore non-null tags (these are the DML or DDL that will be skipped).

So the question, how to change the capture process behavior so it will ignore non-null tags?
The answer is by adjusting the capture rules for DML & DDL (depends on whether you need to ignore both operations).

The following scripts assume you have the default Streams configuration without adding any additional custom rules.

Get Capture Rule name and rule condition.
select rule_name, rule_condition
from dba_streams_rules
where rule_set_owner = ‘STRMADMIN’
and streams_type = ‘CAPTURE’;

It should return 2 rows, 1 rule for DML and the other for DDL.

Here how the DDL capture rule looks like before changing:

((((:ddl.get_object_owner() = ‘schema name’ or :ddl.get_base_table_owner() = ‘schema name’) and :ddl.get_source_database_name() = ‘database name’ )) and (:ddl.get_compatible() <= dbms_streams.compatible_10_2))

Now add condition to capture null tags only

rule_name => ‘CWSAPP66’,
condition => ‘((((:ddl.get_object_owner() = ”CWSAPP” or :ddl.get_base_table_owner() = ”CWSAPP”) and :ddl.get_source_database_name() = ”CWSRD” ))
and (:ddl.get_compatible() <= dbms_streams.compatible_10_2) and (:ddl.is_null_tag() = ”Y”))’,
evaluation_context => NULL);

Now from a new session


DDL or DML changes will not get replicated

dbms_streams.set_tag(null); — go back to replicating everything

Remember if using bi-directional replication, you have to adjust capture rules in every site.

Hazem Ameen
Senior Oracle DBA


2 thoughts on “Oracle Streams: SET_TAG and Capture Rules (Skip DDL & DML)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s