This discussion is archived
1 Reply Latest reply: Mar 29, 2013 10:15 AM by Seedyman RSS

Oracle 9i CDC purge question

Seedyman Newbie
Currently Being Moderated
Does anyone know of anyplace I can get more detailed information on how the CDC process works in 9i?
I had no trouble with 1 capture table, but as soon as I added a second I noticed that even after I did a manual purge the entries in the capture table were not going away until much later.
Oracle version is 9.2.0.6.0
after creating capture table and subscribing
I am following the process of

as subscriber: (for each table)
DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW
DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW
select from CDCview
DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW
DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW

as publisher: (after all tables processed)
DBMS_LOGMNR_CDC_PUBLISH.PURGE

It seems to eventually purge the capture tables, but I recently added a rather high traffic table to the list and would like it to purge more often than it seems to do by default.

There is only one publisher and one subscriber
  • 1. Re: Oracle 9i CDC purge question
    Seedyman Newbie
    Currently Being Moderated
    Seedyman wrote:
    Does anyone know of anyplace I can get more detailed information on how the CDC process works in 9i?
    I had no trouble with 1 capture table, but as soon as I added a second I noticed that even after I did a manual purge the entries in the capture table were not going away until much later.
    Oracle version is 9.2.0.6.0
    after creating capture table and subscribing
    I am following the process of

    as subscriber: (for each table)
    DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW
    DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW
    select from CDCview
    DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW
    DBMS_LOGMNR_CDC_SUBSCRIBE.PURGE_WINDOW

    as publisher: (after all tables processed)
    DBMS_LOGMNR_CDC_PUBLISH.PURGE

    It seems to eventually purge the capture tables, but I recently added a rather high traffic table to the list and would like it to purge more often than it seems to do by default.

    There is only one publisher and one subscriber
    I appears that if the subscriber does the above for all tables it works as expected but if the /extend window/prepare view/select/drop view/purge window/ process is only done for one table no amount of calling the DBMS_LOGMNR_CDC_PUBLISH.PURGE procedure will remove any rows from the capture table. But as soon as all of the subscribed tables are processed, the DBMS_LOGMNR_CDC_PUBLISH.PURGE procedure purges all of the appropriate rows from all of the capture tables.

    It was my understanding that this is not how it is supposed to work.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points