how to partition by week a table

Interval Partitioning By Week [ID 757754.1]

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
Oracle partitioning
***Checked for relevance on 21-May-2012 ***

Goal

How to create an interval partitioned table with the interval of one week.

Fix

For interval partitioning, the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE type. The optional STORE IN clause lets you specify one or more tablespaces into which the database will store interval partition data using a round-robin algorithm for subsequently created interval partitions.

The following example specifies that above the transition point of January 1, 2007, partitions are created with a width of one week.

CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2) )
PARTITION BY RANGE (time_id)
INTERVAL(numtodsinterval(7,'day'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );

The high bound of partition p0 represents the transition point. p0 is in the range section while all partitions above it fall into the interval section.

Further examples can be found in the documents referenced in the end of this article as well in MOS Datawarehousing Community in document Example for Interval partitioning - new feature in 11g.