Changing tablespace during import

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. Received on Fri Oct 21 2005 - 21:25:16 CDT