|Subject:||Changing tablespace during import|
|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
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE USERS LOGGING
(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
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.