2 Replies Latest reply: Apr 17, 2012 10:25 AM by amin_adatia RSS

    SYNC(EVERY <interval>) deployment for Partitioned Tables

      I am wondering if anyone has deployed the SYNC(EVERY "SYSDATE + interval") style in a Partition table environment where there is a large number of partitions but only a few the most recent 7days (lets say 30 days) have any inserts/updates. As I understand it, the SYNC(EVERY interval) creates a DBMS_SCHEDULER job for each partition and the job will submit as per the interval. We have about 5000 partitions on any given day; partitions get dropped and we add a new one for the fututre (we keep 30 days ahead).

      1. Is it possible to modify the interval in these auto created Jobs? For Partitions older thasn X days i would want to run the job every Z hours
      2. Disable the future jobs until the day required (enable 10 minutes before the partition becomes "active" -- gets data

        • 1. Re: SYNC(EVERY <interval>) deployment for Partitioned Tables
          Herald ten Dam

          you can read about this in the manual (http://docs.oracle.com/cd/E11882_01/text.112/e24436/csql.htm#i997677). The manual says: "Each partition of a locally partitioned index can have its own type of sync (ON COMMIT, EVERY, or MANUAL). The type of sync specified in master parameter strings applies to all index partitions unless a partition specifies its own type." So it is possible to set for each partition its own sync type. This is possible with an ALTER statement:
          ALTER INDEX index_name MODIFY PARTITION partition_name PARAMETER (paramstring)
          1. With the above alter you can change it
          2. You can put a partition to MANUAL and then synchronize when necessary.

          So if you know when the partition needs to change, you can change it possible with another job or procedure.

          Herald ten Dam
          • 2. Re: SYNC(EVERY <interval>) deployment for Partitioned Tables

            I was thinking of writing a routine that would check in the CTX_USER_PENDING view for partitions that are not in the current week and then run the dbms_scheduler job for the partition(s). In my case the partition naming is standard so I can get the partition and job for a given date.