Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can a table belong to multiple tablespaces?

share|improve this question

migrated from Feb 12 '13 at 11:14

This question came from our site for professional and enthusiast programmers.

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

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).

share|improve this answer
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 '13 at 11:36
@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 '13 at 12:41
so in general ordinary table should have only one table space..'m i right? – Thiyagu ATR Feb 13 '13 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 '13 at 15:55

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

  • Partitioned table
  • Tables containing LOBs.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.