The Oracle (tm) Users' Co-Operative FAQ

An import has just left a table in the wrong tablespace, Why ?

Author's name: Galen Boyer (from a newsgroup item by Sybrand Bakker)

Author's Email:

Date written: 7th Aug 2001

Oracle version(s): 8.1.7

I've tried to use export/import to move a table from one tablespace to another, but it's ended up in the wrong place.

Back to index of questions

Step one: read the FAQ item by Norman Dunbar How do I move a table from one tablespace to another..

If this hasn't answered your question then remember the following:

Import imports into:

	the tablespace of the create tablespace
	the default tablespace if:
		The orginal tablespace doesn't exist
		The user has no quota on the tablespace

To move a table by exp/imp make sure:

	The target tablespace is the default tablespace of the owner of the tables.
	This account doesn't have unlimited tablespace privilege.
	This account doesn't have quota on the original tablespace.

A "gotcha" from Niall Litchfield: If your table contains lob data which is stored out of row. i.e. the table is something like:

create table t1(
	id		number,
	binary_data	blob
lob (binary_data) store as (
		tablespace lob_data
		disable storage in row
tablespace users;

then an import into a new database which does not contain the tablespace lob_data will fail.

Further reading: N/A

Back to top

Back to index of questions