Over million interval partitions. --not

Here is an yet another SQL tale why you should _not_ store dates as a number.

--drop table F_INVOICE_L;

create table F_INVOICE_L(
       INVOICING_DATE_WID NUMBER (8)
      ,product_wid number(20)) 
partition by range (INVOICING_DATE_WID) 
interval (1) 
subpartition by hash (product_wid) 
subpartitions 32
(PARTITION P_MINPART VALUES LESS THAN (20100101))
;

insert into F_INVOICE_L values (20140101,1);

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

select count(*) from dba_tab_partitions where table_name = 'F_INVOICE_L';

1


Waat?

There should be room for 1048575 partitions. I have only one, the P_MINPART initial one. Well ok I have 32 subpartitions. There should be room for 32767 partitions.

select 1048575/32 + 20100101 from dual;

20132868,96875

We ran out of placeholders for numbers. If we would have used date datatype we would have some time still to go with this partitioning method.

select 1048575/32 + to_date('20100101','yyyymmdd') from dual;

2099-09-18 

Quick and dirty fix

alter table F_INVOICE_L set interval (100);

insert into F_INVOICE_L values (20140101,1);

1 row inserted.

rollback;

select high_value from user_tab_partitions where table_name = 'F_INVOICE_L';

20100101
20140201



And setup the timebomb again to be triggered at new years day 2018.

alter table F_INVOICE_L set interval (1);

select 1048575/32 + 20140201 from dual;

20172968,96875

insert into F_INVOICE_L values (20170101,1);

1 rows inserted.

insert into F_INVOICE_L values (20180101,1);

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