As mentioned in Chapter 1, Synchronous Capture allows you to capture a DML data change (insert, update, delete, and merge) at the source when it is committed, rather than having LogMiner capture the change from the redo. While this can provide performance gains, it does so at the cost of some flexibility; as seen in the restrictions listed in Chapter 1. But remember, Synchronous Capture is NOT Synchronous replication as we explain in Chapter 1.

A Synchronous Capture process is a Streams client that uses internal mechanisms (think table triggers) to capture DML changes at the time they are committed to a table. The DML change is converted to an LCR format and enqueued to the capture queue. To avoid duplicating the capture of the same DML, it is highly recommended that you do not configure Synchronous Capture and regular Capture on the same table.

Synchronous Capture rules can only be added to a ruleset via DBMS_STREAMS_ADM.ADD_TABLE_RULES or DBMS_STREAMS_ADM.ADD_TABLE_RULES. Attempts to create or add Synchronous Capture rules with any other package procedure will cause the rules to be ignored. For a Synchronous Capture rule, you specify sync_capture as the value for the streams_type parameter.

Synchronous Capture rules can only be added to positive rulesets (inclusion_rule = TRUE).

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hr.employees',
streams_type => 'sync_capture',
streams_name => 'capture_sync1',
queue_name => 'strm_admin.HR_CAPTURE_Q',
include_dml => TRUE,
inclusion_rule => TRUE --default
);
END;
/

The above creates a Synchronous Capture Ruleset with the name of RULESET$_122 and Synchronous Capture with the name CAPTURE_SYNC1. Please note that in your environment, you may have a different numeric value for your ruleset. You can view information for Synchronous Capture using the DBA_SYNC_CAPTURE view:

SQL> select * from dba_sync_capture;
CAPTURE_NAME QUEUE_NAME QUEUE_OWNER
------------------------------ ----------------------------- -----------------------
RULE_SET_NAME RULE_SET_OWNER CAPTURE_USER
------------------------------ ----------------------------- -----------
CAPTURE_SYNC1 HR_CAPTURE_Q STRM_ADMIN
RULESET$_122 STRM_ADMIN STRM_ADMIN

We also see a new rule, EMPLOYEES121 associated with the ruleset in DBA_RULE_SET_RULES:

SQL> select rule_set_owner, rule_set_name, rule_owner,
2 rule_name, rule_set_rule_enabled, rule_set_rule_comment
3 from dba_rule_set_rules
4 where rule_set_name = 'RULESET$_122'
5 ;
RULE_SET_OWNER RULE_SET_NAME RULE_OWNER
--------------- --------------- ---------------
RULE_NAME RULE_SET
------------------------------ --------
RULE_SET_RULE_COMMENT
-----------------------------------------------
STRM_ADMIN RULESET$_122 STRM_ADMIN
EMPLOYEES121 ENABLED
"STRM_ADMIN"."RULESET$_122"

And the EMPLOYEES121 rule information in DBA_RULES

SQL> select rule_owner, rule_name, rule_condition
2 from dba_rules where rule_name = 'EMPLOYEES121';
RULE_OWNER RULE_NAME
--------------- ----------------------
RULE_CONDITION
--------------------------------------
STRM_ADMIN EMPLOYEES121
(((:dml.get_object_owner() = 'HR' and
:dml.get_object_name() = 'EMPLOYEES'))
and :dml.is_null_tag() = 'Y' )

Rulesets can be assigned to one or more Oracle Streams Clients. If you wish to assign a Synchronous Capture ruleset to additional Oracle Synchronous capture clients, once it has been created using one of the two above-mentioned DBMS_STREAMS_ADM and rule procedures, you can use the DBMS_CAPTURE_ADM. ALTER_SYNC_CAPTURE to add that ruleset to the specified Synchronous Capture client as well.

BEGIN
DBMS_CAPTURE_ADM.ALTER_SYNC_CAPTURE(
capture_name => 'CAPTURE_SYNC2',
rule_set_name => 'RULESET$_122'),
END;
/

Once a Synchronous Capture ruleset is created, its conditions can be modified using the DBMS_RULE_ADM package.

As with regular capture, there can be only one capture user associated with a Synchronous Capture client. That capture user for Synchronous Capture requires explicit execute privileges to perform any custom rule-based transformations (this includes all transformation functions and all packages/procedures/functions invoked by any transformation functions), execute privilege on the synchronous capture rule set, and enqueue privileges on the associated capture queue.

If you decide that you wish to remove a Synchronous Capture rule from a ruleset, you can do so by using the DBMS_STREAMS_ADM.REMOVE_RULE procedure.

BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'EMPLOYEES121',
streams_type => 'sync_capture',
streams_name => 'capture_sync2',
);
END;
/

You can convert a regular Capture process to a Synchronous Capture process and vice versa, but restrictions do apply. For information on these restrictions, please refer to the Switching From a Capture Process to a Synchronous Capture and Switching from a Synchronous Capture to a Capture Process sections of Chapter 15, in the Oracle Streams Concepts and Administration Guide.