Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

Can a table belong to multiple tablespaces?

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

migrated from stackoverflow.com Feb 12 at 11:14

2 Answers

up vote 9 down vote

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
1  
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 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 at 12:41
so in general ordinary table should have only one table space..'m i right? – Thiyagu Feb 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 at 15:55
up vote 1 down vote

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

 
discard

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