Modify the partition

Database
N/A

Modify the partition

Hi Everyone,

I have a table which has a partition by a date field as it follows:

PARTITION BY RANGE_N(RestoreDate  BETWEEN DATE '2008-04-01' AND DATE '2012-12-31' EACH INTERVAL '1' DAY ,
NO RANGE);

And I would like to extend the partition, so the RestoreDate would start from '2005-01-01' to '2013-12-31',  How can I achieve this? Do I need to recreate the table? Do I need to drop the partition first and then create the new one? Any suggestions?

Thanks in advance!

2 REPLIES
N/A

Re: Modify the partition

Hi Kbos,

Make a copy of your table and then try this (prior replace db_name and table_name ) :

DROP STAT    db_name.table_name  COLUMN(PARTITION);

ALTER TABLE  db_name.table_name  MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE '2005-01-01' AND DATE '2008-03-31' EACH INTERVAL '1' DAY;

ALTER TABLE  db_name.table_name  MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE '2013-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY;         

COLLECT STATISTICS ON db_name.table_name   COLUMN (PARTITION) ;

Regards.

N/A

Re: Modify the partition

Dixxie, thanks so much for your response :) it worked :)