Exceeding 1 million partitions

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.