In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions. As we saw, the partition numbers are preordained based on the low boundary and the interval size.
That also creates an interesting scenario that can catch people out – you might exceed the allowable number of partitions, with an empty table !
SQL> create table t ( x int )
2 partition by range(x) interval(1)
3 ( partition p0 values less than (1) );
Table created.
SQL> insert into t values (2000000);
insert into t values (2000000)
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
A value 2,000,000 is attempting to instantiate the “2,000,000th partition” even though all the preceeding partitions do not exist, which exceeds limit for partitions on a table.
So be careful when choosing your interval sizes.
Just a work around for anyone finding this via google. If you know you are going to be skipping lots of values between your latest partition key and your next (and you’re not planning on filling out the gaps), you can temporarily disable the interval, add a manual partition that covers the range and then reenable the interval.
alter table x set interval ();
alter table x add partition p_workaround values less than (1048574);
alter table x set interval (1);
This will mean that no new partitions will be automatically created for partition keys less than 1048574, which hopefully isn’t a problem. If it is a problem, then automatic list partitioning might be a better fit.