CODEWITHSUNDEEP
oracle11g
user1463065
user1463065

Reputation: 595

How to set interval for exactly for a month on partitoned table

I want auto create partition on my table month wise.

suppose i have created partition upto 28-feb-2018. after that i have set interval for a month using

alter table TBL_PARTITION set interval(NUMTOYMINTERVAL(1,'MONTH'));

when i am inseting march month data into my table, then march month partion auto created but partition created till 28-mar-2018.

My requirement is to create auto partition for exactly a month.In my example auto partition to be created till 31-march-218 as it is last day of month.

Please help me.

Upvotes: 0

Views: 1016

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

1) If your table already has interval future you have to turn it off. If not go point 2 

 alter table TBL_PARTITION set interval();

2) Add head partition when values are less than 1st of next month. In your case this partition will be only 2 day range 

 alter table TBL_PARTITION add partition xxxxx values less than (TO_DATE('2018-04-01','YYYY-MM-DD'))

3) Turn on interval partitioning 

alter table TBL_PARTITION set interval(NUMTOYMINTERVAL(1,'MONTH'));

Upvotes: 1

Related Questions