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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s