CODEWITHSUNDEEP
oracle-databaseoracle12c
Kiran
Kiran

Reputation: 21

How to create monthly partition in oracle?

I have a table with 2017 and 2018 year data. Need to create monthly partition on that table.

So I created one non partitioned table and loaded all the data from original table. now I am converting the new table to a monthly partitioned table.

When I am altering getting error as

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

My Script is 

ALTER TABLE ORDERHDR_PART MODIFY 
PARTITION BY RANGE (LASTUPDATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  (
   PARTITION ORDERHDR_PART_JAN VALUES less than (TO_DATE('01-02-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_FEB VALUES less than (TO_DATE('01-03-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_MAR VALUES less than (TO_DATE('01-04-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_APR VALUES less than (TO_DATE('01-05-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_MAY VALUES less than (TO_DATE('01-06-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_JUN VALUES less than (TO_DATE('01-07-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_JUL VALUES less than (TO_DATE('01-08-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_AUG VALUES less than (TO_DATE('01-09-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_SEP VALUES less than (TO_DATE('01-10-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_OCT VALUES less than (TO_DATE('01-11-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_NOV VALUES less than (TO_DATE('01-12-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_DEC VALUES less than (TO_DATE('01-01-2019','DD-MM-YYYY'))
   )ONLINE;

Upvotes: 0

Views: 14816

Answers (2)

lgonyer
lgonyer

Reputation: 1

This error can occur when there are NULL values in column you are attempting to partition by. Check LASTUPDATE for NULLs.

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59563

I think your approach is wrong.

First create a partitioned table, e.g. 

CREATE TABLE ORDERHDR_PART (....)
PARTITION BY RANGE (LASTUPDATE) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  (
  PARTITION ORDERHDR_INITIAL VALUES less than (DATE '2000-01-01')
  );

Then transfer existing data to the new table.

Either you use a simple INSERT INTO ORDERHDR_PART SELECT * FROM ORDERHDR_2017; Oracle will create monthly partitions automatically based on LASTUPDATE value. With this methods you would duplicate (temporary) your data and/or you may face a performance issue.

The other method is to use Exchanging Partitions, should be like this

ALTER TABLE ORDERHDR_PART 
  EXCHANGE PARTITION FOR (DATE '2017-01-01')
  WITH TABLE ORDERHDR_2017
  INCLUDING INDEXES;

I don't know whether "PARTITION FOR (DATE '2017-01-01')" is created automatically, perhaps you have to run INSERT INTO ORDERHDR_PART (LASTUPDATE) VALUES (DATE '2017-01-01'); ROLLBACK; in order to create it first.

You will get one partition for all months, afterwards you can split the partition with Splitting into Multiple Partitions. Should be like this:

ALTER TABLE ORDERHDR_PART SPLIT PARTITION FOR (DATE '2017-01-01') INTO ( 
   PARTITION ORDERHDR_PART_JAN VALUES less than (TO_DATE('01-02-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_FEB VALUES less than (TO_DATE('01-03-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_MAR VALUES less than (TO_DATE('01-04-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_APR VALUES less than (TO_DATE('01-05-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_MAY VALUES less than (TO_DATE('01-06-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_JUN VALUES less than (TO_DATE('01-07-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_JUL VALUES less than (TO_DATE('01-08-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_AUG VALUES less than (TO_DATE('01-09-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_SEP VALUES less than (TO_DATE('01-10-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_OCT VALUES less than (TO_DATE('01-11-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_NOV VALUES less than (TO_DATE('01-12-2018','DD-MM-YYYY')),
   PARTITION ORDERHDR_PART_DEC VALUES less than (TO_DATE('01-01-2019','DD-MM-YYYY'))
);

Note, by default you cannot drop the inital partition of a RANGE partitioned table. If you face this problem execute:

ALTER TABLE ORDERHDR_PART SET INTERVAL ();
ALTER TABLE ORDERHDR_PART DROP PARTITION ORDERHDR_INITIAL;
ALTER TABLE ORDERHDR_PART SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

Upvotes: 0

Related Questions