PURGE_CHANGE_TABLE is deleting more rows than expected
edited Jul 2, 2013 9:58AM in GoldenGate, Streams and Distributed Database (MOSC) 6 commentsAnswered ✓
Hello,
we need to purge our change tables, deleting rows with commit_timestamp less than a certain date. According Oracle documentation, purge_change_table procedure should
do that, but for some reason, is deleting more rows than we expected.
In the next scenario:
select count(*) from my_change_table where commit_timestamp$ > '05/10/2013 23:59:00';
COUNT(*)
----------
1063
after the next execution:
execute DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE('CDCPUB','MY_CHANGE_TABLE','Y','05/10/2013 23:59:00')
we expected to keep 1063 rows in change table, all rows with commit_timestamp$ >= 05/11/2013 but:
SQL> select count(*) from my_change_table;
COUNT(*)
----------
112
Only rows with commit_timestamp$ >= '05/20/2013 12:07:13' were kept.
we need to purge our change tables, deleting rows with commit_timestamp less than a certain date. According Oracle documentation, purge_change_table procedure should
do that, but for some reason, is deleting more rows than we expected.
In the next scenario:
select count(*) from my_change_table where commit_timestamp$ > '05/10/2013 23:59:00';
COUNT(*)
----------
1063
after the next execution:
execute DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE('CDCPUB','MY_CHANGE_TABLE','Y','05/10/2013 23:59:00')
we expected to keep 1063 rows in change table, all rows with commit_timestamp$ >= 05/11/2013 but:
SQL> select count(*) from my_change_table;
COUNT(*)
----------
112
Only rows with commit_timestamp$ >= '05/20/2013 12:07:13' were kept.
0