Stale default tablespace for oracle partitioned tables

Great friends for free… uuhh?

Oracle partitioned tables offer a variety of options to actually specify where to store the data, that is, the table-, index- and lob-partition-segments. However, iff you want to keep things simple and generic, you may leave all this tablespace spec stuff alone, just relying on the fact that the schema/user default tablespace will always also serve as the default tablespace for generated (interval partitioning) or added (range partitioning) or whatever partitions. Behaving in such a way becomes more and more attractive these days where spreading load- and failover can be done transparantly at other storage layers like asm or san.

That said, still there are potholes to knock over. As I was pleased to learn now, Oracle does not seem to store tablespace names with table (and partition) definitions but tablespace id’s. I do not know of any arcane dictionary view to prove that, but being trapped i digged down into the trap logic until I was shure about the meaning of the question I was asking.

What one might see, knock knock, in a 11g2 installation, may look like so (notice the empty cause and action sections):

--SQL-Fehler: ORA-00959: tablespace '_$deleted$37$0' does not exist
--00959. 00000 -  "tablespace '%s' does not exist"
--*Cause:    
--*Action:

A method of add partition for maxvalue range partitions

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.