MySQL Forums
Forum List  »  Partitioning

MYSQL - CDC Capturing
Posted by: Akshath Hegde
Date: January 31, 2016 11:48PM

In oracle I can track the CDC on table level by using ORA_ROWSCN field.

In oracle by default table is created with NOROWDEPENDENCIES attribute i.e, it disables row level tracking and we can track till block level. i.e, we can track data is changed in which block.

Example :


SQL> create table test(id int);
SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403623165 1
8445403623165 2

In the above output we can see both rows have same value.. because they belong to same data block in oracle.

Now iam updating only one row as below

SQL> update test set id=3 where id = 1;

1 row updated.

SQL> commit;

SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403629328 3
8445403629328 2


It is updated ROW SCN value value for both rows because they belongs same data block.
But to extract only the row which got updated i can do row level tracking in oracle by using ROWDEPENDENCIES attribute and it is as below.

SQL> create table test(id int) ROWDEPENDENCIES;

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403646389 1
8445403646389 2

SQL> update test set id=3 where id = 1;

1 row updated.

SQL> commit;


SQL> select ORA_ROWSCN,id from test;

ORA_ROWSCN ID
-------------------- --------------------
8445403647584 3
8445403646389 2

Now in the above output i can see ROW SCN value is updated only for thr row which is updated but not for whole block.

How we can achieve in MYSQL ???


Please suggest solution for above.

Options: ReplyQuote


Subject
Views
Written By
Posted
MYSQL - CDC Capturing
1419
January 31, 2016 11:48PM
729
February 01, 2016 08:07PM
689
February 01, 2016 09:43PM
630
February 02, 2016 09:29PM
665
February 03, 2016 12:23AM
574
February 04, 2016 10:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.