Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, chakri.

Asked: September 18, 2015 - 4:34 am UTC

Last updated: September 18, 2015 - 11:47 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I am working on oracle streams replication.
Currently the LCR is containing all Columns old values irrespective of the new values.
even if only one column is modified the all the columns coming as part of LCR.
all old column values and only modified columns in the new values are coming.

I dont want all the old values to come as part of LCR.
I want the columns only that are changed as part of DML.

Sample LCR
print_error('2319.12.149830');
*************************************************
----- ERROR #1

--message: 1
type name: SYS.LCR$_ROW_RECORD
source database: DB1
owner: OWNER
object: TABLE1
command_type: UPDATE
old(1): COL1
XXXXXXXXXX
old(2): COL3
XXXXXXXXXX
old(3): COL4
15
17532675
old(18): COL5
old(19): COL6
old(20): COL7
old(21): COL8
old(22): COL9
28822565021
new(1): COL1



PL/SQL procedure successfully completed.

Only COL1 is updated to NULL. But all the other columns coming in the LCR.

Please help


and Chris said...

You can use the dbms_stream_adm.delete_column to specify rules to remove columns from an LCR:

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_streams_adm.htm#ARPLS888

Why do you care about the old values though? What exactly are you trying to achieve?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

chakri m, September 18, 2015 - 2:38 pm UTC


A reader, September 18, 2015 - 4:24 pm UTC

Isn't streams deprecated in favor for Golden Gate ?

chakri m, September 18, 2015 - 4:45 pm UTC

Thanks for the response.

We are doing update on the source and the streams should take care of the replication in target.
As the data is not consitent in source and target, we are seeing the conflicts.
The real issue here is, even though only one column is updated, all the old values are coming in the LCR.
If the old values are not matching with the target data, its erroring out as apply error. I think there is some thing to do with supplemental logging, which is adding all the columns as old values in the LCR even though they are not modified.

dbms_stream_adm.delete_column has to be used at the target db in the conflict handler.

If we can avoid having all the columns as Old values in LCR it will be better while applying the stream.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library