1 Reply Latest reply: Mar 29, 2013 12:15 PM by Seedyman RSS

    Oracle 9i CDC purge question

    Seedyman
      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
          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.