玉面飞龙的BLOG

为此，今天和Xfan打赌输了一顿饭。
在export import的时候我们经常有这样的需求。表在Source上是在dictionary tablespace “dict_data”上，我们想将表导入在Target上的local management tablespace “lmt_data”上，但target上已经存在了一个叫”dict_data”的tablespace，而且还是dict的。怎么办，我们想将表导入到其他表空间中。

方法有很多，比方说先在LMT上建立表的structure（ indexfile）,再导入数据（rows=y ignore=y)。
又或者改变用户的default tablespace 和 tablespace quota:设置用户在dict tablespace上的quota为0，default tablespace为lmt tablespace，这样原先存储在source上dict tablespace的对象，将会被存储在用户指定的默认表空间上。
如下测试实例，
SQL> conn /
Connected.
SQL> show user
USER is “ORACLE”
SQL> create table t tablespace cr_data as select * from dba_objects;
Table created.

ORACLE_TEST$> exp / file=t_cr_data.dmp tables=t
About to export specified tables via Conventional Path …
. . exporting table T 18267 rows exported
Export terminated successfully without warnings.

SQL> show user
USER is “ORACLE”
SQL> drop table t;
Table dropped.
SQL> alter user oracle default tablespace users01;
User altered.
SQL> conn / as sysdba
Connected.
SQL> revoke unlimited tablespace from oracle;
Revoke succeeded.
SQL> alter user oracle quota 0 on cr_data;
User altered.

ORACLE_TEST$> imp / file=t_cr_data.dmp full=y
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing ORACLE’s objects into ORACLE
IMP-00017: following statement failed with ORACLE error 1950:
“CREATE TABLE “T” (”OWNER” VARCHAR2(30), “OBJECT_NAME” VARCHAR2(128), “SUBOB”
“JECT_NAME” VARCHAR2(30), “OBJECT_ID” NUMBER, “DATA_OBJECT_ID” NUMBER, “OBJE”
“CT_TYPE” VARCHAR2(18), “CREATED” DATE, “LAST_DDL_TIME” DATE, “TIMESTAMP” VA”
“RCHAR2(19), “STATUS” VARCHAR2(7), “TEMPORARY” VARCHAR2(1), “GENERATED” VARC”
“HAR2(1), “SECONDARY” VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN”
“S 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) ”
” LOGGING NOCOMPRESS”
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace ‘USERS01′
Import terminated successfully with warnings.
ORACLE_TEST$> ora squ
SQL> conn / as sysdba
Connected.
SQL> alter user oracle quota unlimited on users01;
User altered.

ORACLE_TEST$> imp / file=t_cr_data.dmp full=y
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing ORACLE’s objects into ORACLE
. . importing table “T” 18267 rows imported
Import terminated successfully without warnings.

SQL> select owner,segment_name,tablespace_name from dba_segments where segment_name=’T';
OWNER SEGMENT_NA TABLESPACE_NAME
—————————— ———- ——————————
ORACLE T USERS01

如上测试，import后表的存储空间改变了。但为什么我输了一顿饭哪？ 如上的方法，不支持partiton table和lob segment的export/import转换表空间。想当然，myth.

测试包括lob segment的导入导出
SQL> create table t(id number,description clob ) tablespace cr_data
lob(description) store as
testlob(tablespace cr_data disable storage in row);
Table created.
SQL> insert into t select rownum,username from dba_users where rownum<2;
1 row created.
SQL> commit;
Commit complete.

SQL> !exp / tables=t file=binzhang.test.dmp
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
. . exporting table T 1 rows exported
Export terminated successfully without warnings.

SQL> drop table t;
Table dropped.
SQL> revoke unlimited tablespace from oracle;
Revoke succeeded.
SQL> alter user oracle quota 0 on cr_data;
User altered.
SQL> alter user oracle default tablespace users01;
User altered.

SQL> !imp / file=binzhang.test.dmp full=y
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing ORACLE’s objects into ORACLE
IMP-00017: following statement failed with ORACLE error 1536:
“CREATE TABLE “T” (”ID” NUMBER, “DESCRIPTION” CLOB) PCTFREE 10 PCTUSED 40 I”
“NITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS ”
“1) TABLESPACE “CR_DATA” LOGGING NOCOMPRESS LOB (”DESCRIPTION”) STORE AS “TE”
“STLOB” (TABLESPACE “CR_DATA” DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION ”
“10 NOCACHE STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1))”
IMP-00003: ORACLE error 1536 encountered
ORA-01536: space quota exceeded for tablespace ‘CR_DATA’
Import terminated successfully with warnings.

测试partition table，结果相同。

可见，使用export & import 无法改变lob segment & partition table的tablespace.
不知道10g中的data dump如何？不了解。