ORA-14300 on Interval partition

Partitions can be a wonderful feature in Oracle; they allow faster access to data through partition pruning and they allow for easier archiving and removal of old data. There's not much that can go wrong with partitions, until you want to create an interval-partitioned table.

Say we have an application, where we receive a set of files to be processed by application in 24/7. Each time a file receives we generate a file key in master table using sequence, and take that file key to load the file contents into this table. This table will be accessed in the context of file key (once the processing is done, we drop the partition using that file key). 

Thought of automating the “add partition” concept with Interval-partition, Let's look at why that plan won't work, but first let's look at some logical database limits Oracle imposes on partitions and sub partitions.

The maximum number of (sub) partitions allowed per table or index is 1048575 ( ie, 2^20 -1 )

demo@ORA12C> create table t(x int)
  2  partition by range(x) interval(1)
  3  ( partition p1 values less than (2) );

Table created.

demo@ORA12C>
demo@ORA12C> insert into t(x) values(1048575);

1 row created.

demo@ORA12C> insert into t(x) values(1048576);
insert into t(x) values(1048576)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions


demo@ORA12C>

For interval partitioned tables Oracle sets the partition count to the maximum limit, 1048575.

To explain let say we have: 

Interval(10)  values less than (100)

This has in-effect predefined all of the potential “partitions” being, 100, 110, 120, 130, 140… 1048570

The reason for this is that if you insert values (say) 155 we could not simply create one partition spanning the current lower bound (100) up to the next needed upper bound (160), because it breaks the interval rule.  We have to create only the partition covering 151-160, and when someone later inserts   133, we create another partition covering 131-140 and so forth.

The workaround in this case would be to covert this interval partitioned table into a range partitioned table and move forward.

demo@ORA12C> alter table t set interval();

Table altered.

demo@ORA12C> alter table t add partition p_1048576
  2  values less than (1048577) ;

Table altered.

demo@ORA12C> insert into t(x) values(1048576);

1 row created.


demo@ORA12C>