Take the 2-minute tour ×
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.

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 stackoverflow.com Mar 18 at 2:59

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

2 Answers 2

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 at 7:38

Your Answer

 
discard

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