Announcement

Collapse
No announcement yet.

How to keep just one weeks data in my inbound table partitions?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to keep just one weeks data in my inbound table partitions?

    Hi Folks,

    As the subject says, I'd like to keep just one weeks worth of data in my inbound table partitions, right now they're set up for monthly, but the outbound partitions show weekly. I'm not sure how to change this so that inbound matches outbound. I'm on OTM 5.5 cu5.

    Any insight would be appreciated.

    Thanks very much,

    -Adam

    Example

    SQL> Exec partit.print_table_info('I_TRANSMISSION');

    Table I_TRANSMISSION for inbound transactions is set up to purge monthly.
    ...
    ...
    Table I_TRANSMISSION for OUTBOUND transactions is set up to purge weekly.
    Last edited by avonnieda; April 12, 2010, 13:54.

  • #2
    Re: How to keep just one weeks data in my inbound table partitions?

    Create an automation agent of type "TRANSMISSION" that runs a direct SQL query to archive / delete the inbound data. We can also schedule this automation agent to run every week.
    Cheers,
    Murthy

    Comment


    • #3
      Re: How to keep just one weeks data in my inbound table partitions?

      Thanks very much for the reply MURTHYKO, but what I'm really looking for is to change the standard inbound partition rotation so that instead of each partition rotating monthly, to have it rotate weekly like the outbound partitions.

      Thanks,

      -Adam

      Comment


      • #4
        Re: How to keep just one weeks data in my inbound table partitions?

        ok, do you have any other idea, how to achieve this?
        Cheers,
        Murthy

        Comment


        • #5
          Re: How to keep just one weeks data in my inbound table partitions?

          No, I don't know how to achieve this, that's why I posted the question. I want to avoid doing purges via SQL deletes because it's inefficient, generates a lot of redo, and does not reset the HWM. It's much better (in my opinion) to rotate and truncate the partitions. All I want to do is figure out how to change the interval at which this is done on the inbound partitions.

          Thanks,

          -Adam

          Comment


          • #6
            Re: How to keep just one weeks data in my inbound table partitions?

            Adam,

            The partition used by a particular transmission is controlled by the CHK_BIU_I_TRANSMISSION trigger on the i_transmission table. If you look at the source to this trigger you will see a the call to the partit.i_transmission_key procedure that looks like this ":new.partition_key := partit.i_transmission_key(:new.is_inbound,:new.ins ert_date);". Looking in this package I see that inbound transmissions use a v_part_type of 'MM' while outbound have a type of 'WW' this is used to compute the partition key via the following logic "mod(to_number(to_char(v_date,v_part_type)),4) ". So I believe that changing the line in the i_transmission_key function that assigns the part_type passed to the calc_part_key_helper function should do the trick and make the inbound partitions weekly. In my version of the package I see this on line 316. I have 5.5.4 but you should find something similar in other versions.

            This is just my 5 minute review of the code and should not be considered to be a complete analysis. Of course you should do this in a test environment first and regression test throughly. You should also be aware that this code may revert to the previous behavior if you apply any patches or CU's in the future.

            if (v_is_inbound = 'Y') then
            v_part_type := v_inbound;
            return calc_part_key_helper(v_part_type, v_date);
            to
            if (v_is_inbound = 'Y') then
            v_part_type := v_outbound;
            return calc_part_key_helper(v_part_type, v_date);

            -Alan

            Comment


            • #7
              Re: How to keep just one weeks data in my inbound table partitions?

              Thanks very much for that Alan, that's exactly what I was looking for.

              Best regards,

              -Adam vonNieda

              Comment

              Working...
              X