Oracle-DB: Run a rolling window over interval partitioned tables / avoid ORA-14300, ORA-14758
A problem using interval partitions may be that the number of partitions may not exceed 1.048.575 (1024*1024-1). If using interval partitions not only the physical partitions count. Instead, the number of possible partitions between the required first range partiton and the last interval partition counts for the limit (in consideration of the used interval). If you exceed the limit, ORA-14300 (partitioning key maps to a partition outside maximum permitted number of partitions) is raised . So if you have a rolling window with interval partitions, where new interval partitions at the upper bound are created and older partitions are dropped if no longer needed, you may run into ORA-14300 if your initially created range partition becomes to far away from the upper bound of interval partitions. A possible solution should be to increase the high value of the initial range partition to lower the distance to the highest interval partition. But how to do that? ...