| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partition by month (NOT INCLUDING YEAR)
First of all, always post the code *and* the error message.
In range partitioning, the partitioning column must be of the same datatype as the partitioning value -- ie date. If you want to partition by month (number) then you need an additional column holding the month number only, and refer to that number in the values less than clause.
But this is probably a better candidate for hash partitioning.
>>> fred_fred_1_at_hotmail.com 04/12/01 04:40AM >>>
List,
I am attempting to partition a table based on the month, not the month and
the year. I want all January records to go into the January partition (even
if the records are from January 1998,1999,2000, or 2001). Any January record
from any year all goes into one partition, any February record from any year
goes into the February partition, etc...)
I tried the following code, but no luck. Ideas?? Thanks! -Fred S.
CREATE TABLE MEETING_ROOM_USAGE (
SEQ_MEETING_ROOM_USAGE NUMBER(18) NOT NULL,
POLL_ID NUMBER(10) NOT NULL,
PROPERTY_ID NUMBER(8) NOT NULL,
IP_ADDRESS VARCHAR2(15) NULL,
MAC_ADDRESS VARCHAR2(17) NULL,
ACCESS_CODE VARCHAR2(20) NULL,
CHARGE NUMBER(7,2) NULL,
DURATION NUMBER(9) NULL,
USAGE_DATE DATE NULL,
ROOM_NUMBER VARCHAR2(20) NULL,
COLLECTION_DATE DATE NULL,
CUSTOMER VARCHAR2(50) NULL,
USER_NAME VARCHAR2(50) NULL,
CONSTRAINT PK_MEETING_ROOM_USAGE
PRIMARY KEY (SEQ_MEETING_ROOM_USAGE))
PARTITION BY RANGE (USAGE_DATE)
(PARTITION JANUARY values less than (TO_CHAR('02','MM'))
TABLESPACE MONTH1_USAGE_TS,
PARTITION FEBRUARY values less than (TO_CHAR('03','MM'))
TABLESPACE MONTH2_USAGE_TS,
PARTITION MARCH values less than (TO_CHAR('04','MM'))
TABLESPACE MONTH3_USAGE_TS,
PARTITION APRIL values less than (TO_CHAR('05','MM'))
TABLESPACE MONTH4_USAGE_TS,
PARTITION MAY values less than (TO_CHAR('06','MM'))
TABLESPACE MONTH5_USAGE_TS,
PARTITION JUNE values less than (TO_CHAR('07','MM'))
TABLESPACE MONTH6_USAGE_TS,
PARTITION JULY values less than (TO_CHAR('08','MM'))
TABLESPACE MONTH7_USAGE_TS,
PARTITION AUGUST values less than (TO_CHAR('09','MM'))
TABLESPACE MONTH8_USAGE_TS,
PARTITION SEPTEMBER values less than (TO_CHAR('10','MM'))
TABLESPACE MONTH9_USAGE_TS,
PARTITION OCTOBER values less than (TO_CHAR('11','MM'))
TABLESPACE MONTH10_USAGE_TS,
PARTITION NOVEMBER values less than (TO_CHAR('12','MM'))
TABLESPACE MONTH11_USAGE_TS,
PARTITION DECEMBER values less than (maxvalue))
/
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fred Smith INET: fred_fred_1_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Binley Lim INET: Binley.Lim_at_ird.govt.nz Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Apr 11 2001 - 19:20:07 CDT
 |
 |