Changing tablespace during import

Giganews Newsgroups
Subject: Changing tablespace during import
Posted by:  dbapluspl…@hotmail.com
Date: 21 Oct 2005

I am using Oracle 10.1.0.4 on HP UNIX 11i.

I have always read that during import one can change the tablespace of
tables. For example, if table t1 is in
users tablespace, one can place t1 to app_data. This works fine in
majority of the cases. However, I came across a case today where it
does not work.

I have a table which  has a blob column:
CREATE TABLE HOSTINV (COMP_CODE NVARCHAR2(6),
  COPIES NUMBER(3, 0),
  DATE_LOADED DATE, ORD_NO NVARCHAR2(45),
  PAGE_DATA BLOB, PAGE_NO NUMBER(5, 0))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE USERS LOGGING
NOCOMPRESS LOB
(PAGE_DATA) STORE AS  (TABLESPACE USERS ENABLE STORAGE IN ROW CHUNK
8192 PCTVERSION 10 NOCACHE

STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT))
;

full export of database was done which contained this table as well as
other tables in some schema.

When I did import I did not have users tablespace in my database. The
default tablespace for my
database user was app_data. When I imported - user level, all tables
except this table
moved from users to app_data. Oracle however gave error for this  table
with message  that USERS
tablespace does not exist. Seems like a bug?

The way around was to create this table with app_data tablespace
explicitly before doing import.

Replies