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.
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 only takes a minute to sign up.Sign up to join this community
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);
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.