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

Add a Series of Partitions Using Dates

You can also add a series of partitions using dates. As before, you must execute the wgn_partition_tables procedure as the schema owner. The prototype is shown below:

wgn_partition_tables( 
  partition_dates IN WGN_PARTITION_DATES_TAB , 
  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_Part, 
    3 = wgn_opt_Convert_to_NonPart */ , 
  new_tablespace IN VARCHAR2 DEFAULT NULL , 
  new_indextablespace IN VARCHAR2 DEFAULT NULL , 
  pdebug IN BOOLEAN DEFAULT FALSE 
  ); 

Where:

partition_dates

Is a WGN_PARTITION_DATES_TAB table of partition dates.

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 two partitions for CMS tables, with dates 25 June 2011 and 29 June 2011.

SQL> EXEC Wgn_Partition_Util.wgn_partition_tables( 
  partition_dates => 
    WGN_PARTITION_DATES_TAB( 
      WGN_PARTITION_DATE(TO_DATE('25 Jun 2011'), NULL, NULL) , 
      WGN_PARTITION_DATE(TO_DATE('29 Jun 2011'), NULL, NULL) 
      ) , 
  db_type => Wgn_Partition_Util.wgn_db_type_CMS , 
  pdebug => True 
  ); 

This command uses the Wgn_Partition_Util.get_partition_dates function to return the CMS partition dates. The command then uses these dates to partition the data warehouse using dates that match the CMS partition dates:

SQL> EXEC Wgn_Partition_Util.wgn_partition_tables( 
  partition_dates => Wgn_Partition_Util.get_partition_dates(Wgn_Partition_Util.wgn_db_type_cms)
  , 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'
  pdebug => True 
  );