Database Guide › Database Storage and Partitioning › Set Up Partitions and Purging › Create Database Partitions › Delete a Specific Partition

Delete a Specific Partition

A partitioned database cannot have gaps in the partition date range values. Therefore, in order to delete a specific partition, you must purge the partition you want to delete and then merge this partition with the previous partition. The previous partition acquires the high value of the purged partition.

To do this, execute the wgn_roll_partition procedure, but also specify the perm_data_date_range as the previous partition date range. The prototype is:

EXEC wgn_roll_partitions(
  new_partition_date_range IN VARCHAR2,
  date_range_to_purge IN VARCHAR2,
  perm_data_date_range IN VARCHAR2,
  db_type IN NUMBER 
  );

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.

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

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

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.

Example

The following command purges a CMS partition with an end date of 31 January 2011. It then merges the 31 December partition into this partition:

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

More information:

Purge a Specific Partition