Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Does anyone know the maximum number of interval partitions permitted in Oracle 11g? didnt find anything in Oracle documentation -perhaps missed something. Thanks in advance!

share|improve this question

migrated from Mar 18 '14 at 2:59

This question came from our site for professional and enthusiast programmers.

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.

share|improve this answer

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);
share|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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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