4

Can a table belong to multiple tablespaces?

Improve this question
2
  • No. An Oracle table must belong to one and only one tablespace. Two good links: Oracle Architecture, Tablespaces.
    – paulsm4
    Feb 12, 2013 at 9:19
  • 3
    Not true. Just think of partitioned tables and LOBs to start with.
    – Colin 't Hart
    Feb 12, 2013 at 11:30

2 Answers 2

Reset to default
12

A segment can only belong to one tablespace. So an ordinary table (plain heap table with no LOBs, varrays, nested tables or partitions) can only live in one tablespace.

Partitioned tables (or indexes, materialized views, ...) can have different partitions in different tablespaces though (with at least one restriction: all the tablespaces must have the same block size).

For tables with LOBs, LOBs storage can be out-of-line, in which case you can specify a different tablespace for each persistent LOB column. (Varrays can be stored as LOBs depending on size, so same applies for them.)

Index organized tables can have an overflow segment. When that's the case, the overflow segment can also be put on a different tablespace than the "main" part of the IOT.

Nested tables are also a special case (whether they're in heap tables or IOTs), and the main and nested parts don't have to live in the same tablespace.

There might be other specially handled types, but the main idea is: one segment lives in one tablespace. If you have an object with multiple segments (the most usual being partitions and LOB storage or overflow segments), then those segments need not all be in the same tablespace (with some limitations).

Improve this answer
4
  • 2
    Still not true: An "ordinary" table can contain LOBs, which can be stored in a separate tablespace at the whim of the DBA.
    – Colin 't Hart
    Feb 12, 2013 at 11:36
  • 2
    @Colin'tHart: right. If you wanted to go with exhaustivity, your answer is a bit on the short side too. (And my initial statement, AFAIK, holds: 1 segment <=> 1 tablespace.) Updated anyway.
    – Mat
    Feb 12, 2013 at 12:41
  • so in general ordinary table should have only one table space..'m i right?
    – Thiyagu ATR
    Feb 13, 2013 at 15:16
  • @Thiyagu: see the very first paragraph - an "ordinary" table (for ex a heap table with no LOBs, partitions, nested stuff, ...) will live in only one tablespace, yes.
    – Mat
    Feb 13, 2013 at 15:55
2

Yes. There are several cases when a single Oracle table can "live" in multiple tablespaces:

  • Partitioned table
  • Tables containing LOBs.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.