Database Guide › Database Storage and Partitioning › Set Up Partitions and Purging › Create Database Partitions › Add a Series of Partitions

Add a Series of Partitions

To add a series of partitions, you must execute the wgn_partition_tables procedure as the schema owner. The prototype is shown below:

wgn_partition_tables(
  date_range IN VARCHAR2 , 
  num_partitions IN NUMBER , 
  num_days_per_partition IN NUMBER , 
  db_type IN NUMBER DEFAULT wgn_db_type_CMS , 
  Partition_Options IN NUMBER DEFAULT 0 /* 
    1 = wgn_opt_Partition_NonPart_Tabs,
    2 = wgn_opt_Convert_to_Partitioned, 
    3 = wgn_opt_Convert_to_NonPartition */ , 
  new_tablespace IN VARCHAR2 DEFAULT NULL , 
  new_indextablespace IN VARCHAR2 DEFAULT NULL , 
  pdebug IN BOOLEAN DEFAULT FALSE 
  ); 

Where:

date_range

Is the end date of the earliest partition.

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

num_partitions

Specifies the number of partitions you want to create.

num_days_per_partition

Is the number of days in each partition.

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.

Partition_Options

Specifies how you want to partition the database:

0

(Default) Creates partitions only in existing partitioned tables.

wgn_partition_util.wgn_opt_Partition_NonPart_Tabs

Converts all non-partitioned tables to partitioned tables.

wgn_partition_util.wgn_opt_Convert_to_Part

Converts existing partitioned tables to partitioned tables based on the new partitioning scheme.

wgn_partition_util.wgn_opt_Convert_to_NonPart

Converts existing partitioned tables to non-partitioned tables.

Important! If the Partition_Options parameter is set to a non-default value, existing tables are copied and replaced with new tables partitioned as specified. The old copies of the tables are renamed BK<x>_<TableName>. This operation creates a complete copy of the data and consumes a significant amount of storage!

new_tablespace

Specifies which tablespace to use. (If a custom tablespace is specified, the custom tablespace takes precedence.)

new_indextablespace

Specifies which tablespace to use for new indexes.

pdebug

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

Examples

This command creates 12 partitions for the CMS tables, starting on 31 January 2011, and each 30 days long:

SQL> EXEC wgn_partition_util.wgn_partition_tables( 
  date_range =>'31-jan-2011' , 
  num_partitions =>12 , 
  num_days_per_partition =>30 , 
  db_type =>wgn_partition_util.wgn_db_type_cms 
  ); 

This command creates the same partition structure for the data warehouse tables:

SQL> EXEC wgn_partition_util.wgn_partition_tables( 
  date_range =>'31-jan-2011' , 
  num_partitions =>12 , 
  num_days_per_partition =>30 , 
  db_type =>wgn_partition_util.wgn_db_type_dw 
  ); 

This command creates the same partition structure for the data warehouse tables, but also copies and replaces the existing tables with new partitioned tables. This command specifies WGNDWTSF tabelspace for the tables and WGNDWTSFIDX tablespace for the indexes:

SQL> EXEC wgn_partition_util.wgn_partition_tables( 
  date_range =>'31-jan-2011' , 
  num_partitions =>12 , 
  num_days_per_partition =>30 , 
  db_type =>wgn_partition_util.wgn_db_type_DW , 
  Partition_Options =>wgn_partition_util.wgn_opt_Convert_to_Partitioned , 
  new_tablespace =>'WGNDWTSF' , 
  new_indextablespace =>'WGNDWTSFIDX' 
  );