What About Changes Uncommitted when A Streams Capture Is Instantiated?

That may sound stupid but imagine now the following scenario: "You have uncommitted changes pending for a while before you started a Streams Capture on one of the table involved by the changes. We could even suppose the redo log containing the changes has been archived and the archive log deleted! How does Streams capture handle that case?".

The answer is pretty simple though: "That is not possible!". That's one of the purposes of the dbms_capture_adm.prepare_xxxx_instantiation procedures. They lock the tables to be captured with a Shared (S) TM enqueue which ensure there is no uncommitted changes in the table. To be more precise, they actually wait for those changes to be committed.

Note that a Shared TM enqueue doesn't prevent a transaction involving the table to be pending at the time the capture begins. But it insures no DML has been applied to the table. The reason is Shared (S or 4) Enqueues and Row-X Enqueues (RX or 3) are not compatible as you can see in the summary Summary of Table locks in the Concept Guide

In the following examples, I'll show some of the impact

Prepare a table instantiation when it has pending changes

Obviously the first example consists in checking what happens when you want to prepare the instantiation of a table that has pending changes. To do it, you can open 2 sessions and run the following test case:

• In the first session, create a table, insert data in it and leave that session opened without commiting the transaction:

create table T1 (col1 number);

insert into T1 values (1);

• In the second session, run dbms_capture_adm.prepare_table_instantiation

exec dbms_capture_adm.prepare_table_instantiation('T1','none');

• The second session waits. You can check the enqueues from the GV$LOCK fixed view like below. You'll see the session preparing the instantiation is waiting for the TM enqueue hold by the first session to be released:

select sid,
      type,
      lmode,
      request,
      ctime,
      block
 from gv$lock
where id1 in (
      select object_id
        from user_objects
       where object_name='T1');

SID TY LMODE REQUEST CTIME BLOCK
--- -- ----- ------- ----- -----
125 TM     0       4   469     0
170 TM     3       0   480     1

• You can roll back (or commit) the changes in the first session, dbms_capture_adm.prepare_table_instantiation will finish.

rollback;

• We can now drop the table and go to the next section

drop table t1 purge;

Prepare a table instantiation when it has a pending transaction but no change

In this second example, I'll show that a pending change is different from a pending transaction involving the table; open 2 sessions and run the following test case:

• In the first session, create a table, lock the table in shared mode and check there is a TM enqueue for the table; leave that session opened without commiting the transaction:

create table T1 (col1 number);

lock table T1 in share mode;

select sid,
      type,
      lmode,
      request,
      ctime,
      block
 from gv$lock
where id1 in (
      select object_id
        from user_objects
       where object_name='T1');

SID TY LMODE REQUEST CTIME BLOCK
--- -- ----- ------- ----- -----
170 TM     4       0    25     0

• In the second session, run dbms_capture_adm.prepare_table_instantiation

exec dbms_capture_adm.prepare_table_instantiation('T1','none');

• You see that in that case, the dbms_capture_adm.prepare_table_instantiation second has finished. We can now drop the table and go to the next section.

drop table t1 purge;

Row-X TM Enqueue doesn't imply pending changes...

This last example shows Row-X TM Enqueue doesn't actually imply any row change. Like in the first example, we'll open 2 sessions and run a small scenario to illustrate the point and the impact on preparing the instantiation of the table.

• In the first session, create a table with data. Then run an update changing NO rows and leave that session opened without commiting the transaction; you can check there is a Row-X TM enqueue on the table:

create table T1 (col1 number);

insert into T1 values (1);

commit;

update T1 set col1=1 where 0=1;

select sid,
      type,
      lmode,
      request,
      ctime,
      block
 from gv$lock
where id1 in (
      select object_id
        from user_objects
       where object_name='T1')
   and sid=sys_context('USERENV','SID');

SID TY LMODE REQUEST CTIME BLOCK
--- -- ----- ------- ----- -----
170 TM     3       0    80     0

• In the second session, run dbms_capture_adm.prepare_table_instantiation

exec dbms_capture_adm.prepare_table_instantiation('T1','none');

• As expected, the second session waits, though no change have been actually done, because of the WHERE 1=0 clause of the update; You can roll back (or commit) the changes in the first session, dbms_capture_adm.prepare_table_instantiation will finish.

rollback;

• Drop the table to finish that section

drop table t1 purge;

Conclusion

Think twice before you run dbms_capture_adm.prepare_xxxx_instantiation!