DBMS_CDC find only the columns that are changed along with the primary key in the table [message #602414] |
Wed, 04 December 2013 04:14 |
novice1
Messages: 15 Registered: September 2007
|
Junior Member |
|
|
Hello,
We are having a requirement to find the change data in the production environment.
We are planning to use the DBMS_CDC utility.
But for example in the create change table 1 picked emp_id,ename, address, salary,dob.
I have a sample data of
empid ename address salary DOB
1 test1 24 test street 2000 20-Jan-98
2 test2 25 test street 2500 15-Aug-97
if ename for empid 1 is changed to test1_test3 from test1.
My CDC is capturing the values in the old and new values in the 5 columns.
But i need to get only the empid (primary key of the source table) and the ename column as only that's been updated not the rest of the 3 columns.
Can i accomplish this.
Please advice.
|
|
|
|
|
|
Re: DBMS_CDC find only the columns that are changed along with the primary key in the table [message #602455 is a reply to message #602441] |
Wed, 04 December 2013 07:13 |
novice1
Messages: 15 Registered: September 2007
|
Junior Member |
|
|
source table: 10 columns
change table: 6 columns ( excluded 4 who columns)
so if i ran my CDC , i am able to get the 6 columns every time irrespective of the column changed.
but i am looking for some thing more like if as per my previous example only ename is changed, then i should only be getting empid and ename only not the other column values.
ex:
empid ename address salary dob operation
1 test1 24 test street 2000 20-Jan-98 UO
1 test1_test3 24 test street 2000 20-Jan-98 UN
but what i am expecting is emp id and ename only as ename is only changed.
if for example address is changed for empid=2
empid ename address salary dob operation
2 test2 24 test street 2000 20-Jan-98 UO
2 test2 24 test_test2 street 2000 20-Jan-98 UN
in this case i shouls be only getting empid and address only but not all the columns in the emp table.
Please advice.
|
|
|
|
Re: DBMS_CDC find only the columns that are changed along with the primary key in the table [message #602467 is a reply to message #602455] |
Wed, 04 December 2013 07:49 |
John Watson
Messages: 8930 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If you want to limit the capture to only changed columns, you could create multiple change tables: each consisting of just one column and the primary key.
I disagree with the Black Swan (not something I do often) on the future of CDC. I would expect CDC and Streams to exist for ever, but with no further enhancements. That is what has happened to Advanced Replication. No matter how much Larry would like us to start using GoldenGate instead, I cannot imagine him removing functionality that so many sites rely upon.
|
|
|