To quickly recap the problem, if you have a table like:
SQL> create table tpk (n number primary key); Table created SQL> insert into tpk values (1); 1 row inserted SQL> insert into tpk values (2); 1 row inserted SQL> commit; Commit completeThen executing the following statement...
update tpk set n=n+1...will result in a transient PK problem since the replication solution will have to decompose it into the following two statements:
update tpk set n=2 where n=1; update tpk set n=3 where n=2;There are two immediate (and major) problems with the above statements. The first problem is that we can't execute the first statement without violating the primary key constraint. Another problem is, even if we somehow could execute the first statement, the second statement will result in updating both rows since they now have the same value!
Oracle Streams historically dealt with that problem using internal mechanism which you could leverage by executing a specially constructed LCR. Other (third-party) replication solution were pretty much out of luck and had to rely on elaborate tricks in order to work around the problem.
This is all about to change.
The necessity to better integrate GoldenGate and bring it feature set up has driven quite a bit of exciting innovation. One of these innovations solves the transient PK problem not only for GoldenGate but for everybody else.
The above package has been available at least since 220.127.116.11 and has two procedures which are directly relevant to the problem described above. I'm talking about enable_tdup_workspace and disable_tdup_workspace. Here is a quick demonstration of how they work:
SQL> --this will result in PK violation SQL> update tpk set n=2 where n=1; update tpk set n=2 where n=1 ORA-00001: unique constraint (ROOT.SYS_C005031) violated SQL> exec dbms_xstream_gg.enable_tdup_workspace; PL/SQL procedure successfully completed SQL> --this is now works! SQL> update tpk set n=2 where n=1; 1 row updated SQL> update tpk set n=3 where n=2; 1 row updated SQL> exec dbms_xstream_gg.disable_tdup_workspace; PL/SQL procedure successfully completed SQL> commit; Commit completeAs you can see, the procedure allows us to avoid classical transient PK problem! Indeed, that's what GoldenGate uses internally to avoid getting in troubles as well. The implementation seems to be leveraging the same delete+insert trick Oracle Streams did:
SQL> exec dbms_xstream_gg.enable_tdup_workspace; PL/SQL procedure successfully completed SQL> select n, rowid from tpk; N ROWID ---------- ------------------ 1 AAAO25AAFAAClWFAAA 2 AAAO25AAFAAClWFAAB SQL> update tpk set n=2 where n=1; 1 row updated SQL> select n, rowid from tpk; N ROWID ---------- ------------------ 2 AAAO25AAFAAClWFAAB SQL> update tpk set n=3 where n=2; 1 row updated SQL> select n, rowid from tpk; N ROWID ---------- ------------------ 2 AAAO25AAFAAClWFAAA 3 AAAO25AAFAAClWFAABNote how the row mysteriously disappears after the first update and then suddenly comes back after the second one?
I think anybody who were into any sort of replication and its problems will find this to be one of the most significant new features made available. The only caveat is that the above package is not documented so anyone thinking about leveraging it needs to carefully think about the way it behaves.