Does anyone know the maximum number of interval partitions permitted in Oracle 11g?

I couldn't find anything in Oracle's documentation. Perhaps I missed something.


Alex Poole's answer is correct, but it is important to note that interval partitions are constrained by the range, not just the number of partitions.

This example shows a table that appears to only support 2 partitions. This implies that Oracle uses some sort of fixed algorithm for defining partitions. Choose the first partition carefully.

create table test_table(a number)
partition by range (a) interval (1)
(partition p1 values less than (2));

insert into test_table values(1);

insert into test_table values(1048575);

--Raises exception: ORA-14300: partitioning key maps to a partition outside
--maximum permitted number of partitions.
insert into test_table values(1048576);
| improve this answer | |
  • Isn't your interval(1) clause the deciding factor here, once the fixed range partitions have been counted? The legal intervals are determined by that combination, even if the interval partitions are only theoretical at this point - they don't really exist until data is inserted. But yes, it's an important distinction, that the number of intervals and the interval size effectively constrain the maximum value allowed in the partition key column. – Alex Poole Mar 18 '14 at 7:38

From the create table documentation:

Notes on Partitioning in General The following notes pertain to all types of partitioning:

  • You can specify up to a total of 1024K-1 partitions and subpartitions.
  • ...

So quite a lot... but as jonearles shows, you still need to pick your interval size, fixed ranges and transition point carefully as the combination of those effectively constains the maximum value of the partition key. Every interval exists logically as soon as the table is created, though they aren't created physically until relevant data is inserted. You can't 'skip' intervals.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.