Thanks for the question, Rajeshwaran.
Asked: February 21, 2016 - 10:32 am UTC
Last updated: January 23, 2018 - 1:58 am UTC
Version: 12.1.0.2
Viewed 10K+ times! This question is
You Asked
Team,
We have an application, where we receive a set of files to be processed by application in 24/7.
Each time a file recieves 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). so thought of automating the "add partition" concept with interval partition, but now we hit this error.
rajesh@ORA12C> create table t(x int)
2 partition by range(x) interval(1)
3 ( partition p0 values less than (1));
Table created.
rajesh@ORA12C> insert into t values(1048574);
1 row created.
rajesh@ORA12C> insert into t values(1048575);
insert into t values(1048575)
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
For time being, we flipped this table to Range partition and we are able to proceed without any errors.
rajesh@ORA12C> alter table t set interval();
Table altered.
rajesh@ORA12C> alter table t add partition p_1048575
2 values less than ( 1048576 );
Table altered.
rajesh@ORA12C> insert into t values(1048575);
1 row created.
rajesh@ORA12C>
But the question, is
1) Why do we get this error message in Interval but not with Range partition?
2) Looked around the docs, but not able to find anything related to this. Could you help with documentation link specific to this?
and Connor said...
There is a cap of one million partitions, even if these partitions are not yet in existence.
To explain, let's say you have:
interval (10), values less then (100)
This has in effect pre-defined all of the *potential* partitions being,
100,110,120,130,140,..... 100000000
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 a partition covering 151-160, and when someone later inserts 133, we create *another* partition covering 131-140. And so forth...
A range partition is different. Its upper/lower bounds are defined precisely by the *existing* partitions.
It's the same reason when you do a full scan on an interval partition table, you'll PSTART=1, and PSTOP=1048576, even though not that many partitions are yet in existence.
Hope this helps.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment