Can a table belong to multiple tablespaces?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Feb 12 '13 at 11:14
This question came from our site for professional and enthusiast programmers.
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).
Yes. There are several cases when a single Oracle table can "live" in multiple tablespaces: