Database Guide › Database Storage and Partitioning › Set Up Partitions and Purging › Set Up Partition-Based Purging › Set up Rolling Partitions

Set up Rolling Partitions

In a typical RANGE partitioning model, the purge operation periodically purges the specified partition and adds a new partition.

To define your rolling partitions, you must execute the wgn_roll_partitions procedure in the wgn_partition_util package. The prototype is shown below:

wgn_roll_partitions(
  new_partition_date_range IN VARCHAR2,
  date_range_to_purge IN VARCHAR2,
  perm_data_date_range IN VARCHAR2 := null,
  db_type IN NUMBER 
  pdebug IN BOOLEAN DEFAULT FALSE 
  );

Where:

new_partition_date_range

Specifies the end date for the new partition.

This parameter adds the new partition into the existing tablespace. In the Wgn3PurgeHistory table, this date is listed in the P1 column. You can also set this parameter to NULL to purge a specific partition without adding a new one—see the next section.

By default, the date format must be dd-mmm-yyyy (for example, 08-mar-2011).

date_range_to_purge

Specifies the end date for the partition that you want to purge. Date formats are as above.

This parameter purges all non‑permanent data from the partition specified by the date_range_to_purge parameter. In the Wgn3PurgeHistory table, this date is listed in the P2 column.

perm_data_date_range

(Optional) Specifies the end date for the historic partition. Date formats are as above.

If this parameter is not specified, permanent records stay in their original partition. In the Wgn3PurgeHistory table, this date is listed in the P3 column.

db_type

Specifies the database that you want to partition:

wgn_partition_util.wgn_db_type_CMS

(Default) Specifies the CMS database tables.

wgn_partition_util.wgn_db_type_DW

Specifies the data warehouse tables.

wgn_partition_util.wgn_db_type_BOTH

Specifies both databases. That is, you want to partition tables in the CMS database and the data warehouse.

pdebug

(Optional) Specifies whether a debug trace is produced. It defaults to false.

Example

The command below creates a new partition for the CMS, starting 31 January 2011 and purges the partition 31 January 2010:

SQL> EXEC wgn_partition_util.wgn_roll_partitions ( 
  new_partition_date_range =>'31-jan-2011', 
  date_range_to_purge =>'31-jan-2010', 
  db_type => Wgn_Partition_Util.wgn_db_type_CMS 
  ); 

This command adds and purges the same partitions for the data warehouse:

SQL> EXEC wgn_partition_util.wgn_roll_partitions ( 
  new_partition_date_range =>'31-jan-2011', 
  date_range_to_purge =>'31-jan-2010', 
  db_type => Wgn_Partition_Util.wgn_db_type_DW 
  );

More information:

Wgn3PurgeHistory

Historic Partitions

Example Purge Sequence