1 Reply Latest reply: Jan 8, 2013 7:31 AM by user7425569 RSS

    Oracle 11g Change data Capture

    user9944600
      We have setup a change data capture on Oracle 11gR2. the records that get changed in the source tables will get captured in the change tables. I have some unnecessary rows in the source tables, which i would like to eliminate them without replicating the changes to target table. i know the key values to identify the rows that got populated in the change table and can manually delete it. Is there any known/unknown side affects to eliminate the records from the change table.
        • 1. Re: Oracle 11g Change data Capture
          user7425569
          Hi,

          you can use dbms_streams.set_tag to hide transactions from replication.

          see: http://docs.oracle.com/cd/E11882_01/server.112/e10705/rep_tags.htm#STREP390

          sample:

          BEGIN
          DBMS_STREAMS.SET_TAG( tag => HEXTORAW('1D'));

          delete from my_table where column1 = 1;
          commit;

          DBMS_STREAMS.SET_TAG( tag => null);
          END;
          /

          the apply tag has to be another than is set to your apply process.

          therefore first determine the actual tag for your session:

          SET SERVEROUTPUT ON
          DECLARE
          raw_tag RAW(2048);
          BEGIN
          raw_tag := DBMS_STREAMS.GET_TAG();
          DBMS_OUTPUT.PUT_LINE('Tag Value = ' || RAWTOHEX(raw_tag));
          END;
          /

          first try with a sample...

          kind regards
          johann.