introduction
this article discusses a selection of methods, as well as their prerequisites and consequences, of adding a partition to the high end of an oracle range partitioned table, where the high end is dedicatedly denoted by maxvalue. it also regards questions arising when the partitioned table contains storage optimized lob-colums, a text-domain-index and, for size and failover reasons, every table and several index partitions reside in dedicated tablespaces.
range partitioned tables and add partition
literally seen, adding a partition to the high end of an oracle range partitioned table should easily be possible with the alter table <table> add partition ... statement. the common issue with alter table <table> add partition ... is, however, the fact that the statement will only succeed if the high end is a constant, a fixed value, may it be a number or a date or whatever. if you try to add a partition to a table, that has an upper limit of maxvalue, you may receive the following error:
ORA-14074: partition bound must collate higher than that of the last partition
Cause: Partition bound specified in ALTER TABLE ADD PARTITION statement did not collate higher than that of the table’s last partition, which is illegal.
Action: Ensure that the partition bound of the partition to be added collates higher than that of the table’s last partition.
the point goes without speaking. you cannot specify a newer upper limit that is in fact larger than maxvalue, which is infinite by definition.
real world business does hardly allow to preestimate a meaningful fixed value for the high end of a range partitioned table. business rules may change quite often such that an immediate adjustment of a fixed high end value may pose considerable efforts or may even be impossible.
if, on the other hand, one uses maxvalue to denote the partitioned table high end, what other methods are available to evolve a partitioning scheme, a time windowing technique for example, and what will be the prerequisites and consequences of such a declaration? the following presents a fully functional test case that aims to simulate the execution of a split partition action upon a rather complex table layout having lob-colums, a text-domain-index and partition-related tablespaces. quite a couple of views are provided to follow each and every change in terms of table- and index-storage and object operation validity. just playing around with the test case should smootly introduce a way to some dedicated production site implementation.
Bitbach's Blog 