Fwd: Oracle Streams and Uniqueness

  • From: "Nigel Thomas" <nigel.cl.thomas@xxxxxxxxxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 10 Nov 2008 16:05:56 +0000

 From Substitute Key Columns in the Streams Replication Administrators Guide
(10gR2)

"In the absence of substitute key columns, primary key constraints, and
unique key constraints, an apply process uses all of the columns in the
table as the key columns, excluding LOB, LONG, and LONG RAW columns. In this
case, you must create an unconditional supplemental log group containing
these columns at the source database. Using substitute key columns is
preferable when there is no primary key constraint for a table because fewer
columns are needed in the row LCR."

It seems only fair - the apply process has to know to which row to apply a
change described in an LCR... In effect, it has to make an update statement

update table set d1=, d2=, d3=, d4= ...
where k1= and k2= and k3=

where d1-n are the "data" columns (all columns if the key is updateable) and
k1-n are the "key" columns. Same for delete - inserts aren't a problem of
course.

Obviously the ROWID is no use, as there is no feasible mapping from rowid in
the source database to rowid in the target.

However, although I wrote my own cheap and cheerful replication tools in
1993 for a client, I have no IRL experience of Oracle Streams... perhaps it
can do magic?

Regards Nigel

2008/11/10 Niall Litchfield <niall.litchfield@xxxxxxxxx>

 All,
>
> I am being told by a support analyst at Oracle re Streams that
>
> Oracle must be able to identify uniquely and match corresponding rows at
> different
> databases.
> So each table in a Streams environment should have a primary key, unique
> key constraints or a substitute key.
>
> This in addition to supplemental logging. Is this correct? If so, surely
> this implies that streams is only useful in environments where tables are
> guaranteed (by Oracle or the application(s) running against it) to be
> unique. This seems a somewhat significant real-world disadvantage to me,
> based on the applications that I see. I guess I sort of hoped that Oracle
> might have used the ROWID to generated the LCR (in a way that I fully admit
> I have spend no time at all thinking about).
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
>

Other related posts:

  • » Fwd: Oracle Streams and Uniqueness - Nigel Thomas