14.1 Partition Maintenance

Partition Maintenance is performed using three database stored procedures.

The procedures used for partition maintenance are as follows:

• SP_OAA_ADD_MONTHLY_PARTITION
• SP_OAA_ADD_WEEKLY_PARTITION
• SP_OAA_DROP_PARTITION

The DBMS_SCHEDULER package runs preconfigured jobs, which in turn execute the procedures SP_OAA_ADD_MONTHLY_PARTITION and SP_OAA_ADD_WEEKLY_PARTITION against the relevant tables in order to create partitions for new data entries.

The scheduler runs both of these procedures periodically to add table partitions. Each table partition will store data whose creation_time are lower than the high value of the table partition. The high value of the table partition is the maximum value allowed for the creation_time column in one data entry.

The SP_OAA_ADD_MONTHLY_PARTITION stored procedure adds partitions for tables with a monthly frequency. The script runs at the end of each month to create partitions for the following month.

The SP_OAA_ADD_WEEKLY_PARTITION stored procedure adds partitions for tables with a weekly frequency. The script runs at the end of each week to create partitions for the following week.

The SP_OAA_DROP_PARTITION procedure is run manually by the Adminstrator to drop the table partitions for tables whose high value is smaller than current_date-retention_days. This procedure is usually run after old data is purged and archived. See Archiving and Purging.