Month: October 2015

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"