Oracle's use of tablespaces

Why does Oracle use tablespaces? On the balance, they seem unnecessarily complicated given the advantages they offer.

Tablespaces are reminiscent (to me) of the IBM mainframe world: allocate a file and then create it. It would make more sense to me to create the file with some quota mechanism to limit it size or the number of extents, but not actually tie up disk space that's not used, which is what happens with tablespaces (or am I not using them correctly?). If a query needs a lot of disk space, say for a sort or temporary tables, let the server grab the space for the duration of the operation and then free it to the OS when the operation ends. Oracle seems to need permanently allocated space, however. For instance, I recently had to create a 50M TEMP tablespace for a query joining a 10k-row table with a 1k-row table. Why should this space persist after I'm done this query? For comparison, Ingres allows the DBA to specify that certain tables or working spaces get put on certain defined devices, and to assign different users to different devices, advantages that tablespaces offer, without allocating space first and tying it up permanently. (By permanently, I mean that the allocation persists until the DBA alters it manually.) (Ingres has resource-usage limits, but I don't know if they extend to disk space. I also don't know what Sybase or Informix or others do on this score.) Then there's the case where a tablespace is filled and has used the maximum number of extents. The DBA has to "export, drop, and re-create ... the object" [Oracle 7 Server Administrator's Guide] instead of just upping the quotas. "For an extremely large table, this can take as much as 14 hours, or more."

The only reason I can think of why Oracle does things with permanent instead of as needed spaces is to make the software more portable. That is, permanent tablespaces is a lowest-common-denominator tactic, bringing functions inside Oracle that are present in most but not all operating systems to account for the OSes where the disk-space-control functions are not present. Maybe tablespace allocation is a relic of Oracle's design, something that should have been rewritten as Unix and VMS became Oracle's preeminent platforms.

Is my understanding of tablespace behavior accurate? Are there justifications I haven't thought of? What is the reasoning behind tablespaces?

                                Seth Received on Mon Aug 30 1993 - 12:43:08 CEST