What If You Don't Prepare The Source Table For Instantiation?

As you know, the DBMS_STREAMS_ADM package provides shortcuts to setup Streams; one of this shortcut is that it prepares the table for instantiation on the source database when you use ADD_TABLE_RULES. Obviously this assumes the package connects to that source database if that's a downstream capture. To say it in another way, you must use a database link if you want the table to be prepared by ADD_TABLE_RULES in the case of a downstream capture; If that's not the case, don't forget to use DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION on the source database.

But what happens if you don't prepare the source table for instantiation?

There is probably no better way to check than to test it. I've tried to change my approach this time! I won't describe how to setup your environment; Instead you'll find attached a script that set Streams up with that exact case, i.e. it doesn't run prepare_table_instantiation! If you want to give it a try, you can download it from this link: streams-no-prepare.sql. Use Right-Click->Save As... Hopefully it won't be too buggy!

The script requires 1 only database: (1) the database should be 11g; that may work with 10g but I did not test it at all, (2) it must have the USERS and TEMP tablespaces created, (3) it has to be in ARCHIVELOG mode and (4) you should be able to connect with "/ as sysdba". To use the script, run:

sqlplus /nolog @streams-no-prepare.sql

If you get any issue, leave a comment on this post with your email; it won't appear and we'll discuss your issue offline if you find a bug. The script should:

• Create the whole configuration, except for the table preparation:
• Start the Apply and Capture
• Create a second script drop-no-prepare.sql to drop the configuration, once you are done with it

If you wait for a few minutes, Streams should work:

col capture_name format a16
col state format a30 wor wra
col capture_message_number format 999999999999999
col apply_name format a16
set lines 80

select capture_name,
      capture_message_number capture_scn,
      apply_name,
      state
 from v$streams_capture;

CAPTURE_NAME     CAPTURE_SCN APPLY_NAME       STATE
---------------- ----------- ---------------- ------------------------------
STREAMS_CAPTURE      1316028 STREAMS_APPLY    CAPTURING CHANGES

select apply_name,
      status
 from dba_apply;

APPLY_NAME  STATUS
---------------- --------
STREAMS_APPLY  ENABLED

So we'll perform our test:

insert into source.t1
  values (3,'Text 3');

commit;

col id format 99
col text format a10

select *
 from destination.t1;

ID TEXT
--- ----------
 1 Text 1
 2 Text 2

But even if you wait you'll get no apply error and the change will never be applied. Prepare the table for instantiation now and insert a new data:

exec dbms_capture_adm.prepare_table_instantiation('source.t1','keys');

insert into source.t1
  values (4,'Text 4');

commit;

If you check the destination table you'll see only changes after the table has been instantiated are captured:

col id format 99
col text format a10

select *
 from destination.t1;

ID TEXT
--- ----------
 4 Text 4
 1 Text 1
 2 Text 2

As you can see it's important to prepare the instantiation so that the capture can capture. There is many more things to understand from this example and we'll detail them in the coming weeks. For now, delete your Streams configuration:

sqlplus /nolog @drop-no-prepare.sql