oracle 9i中imp导入另外一个表空间

　　版本：oracle 9i
有关具体的操作步骤，oracle官方文档（Oracle9i Database Utilities，import）说的很清楚：
----------------------------------------------------------------------------------------------------
Reorganizing Tablespaces
If a user's quota allows it, the user's tables are imported into the same tablespace from which they were exported. However, if the tablespace no longer exists or the user does not have the necessary quota, the system uses the default tablespace for that user as long as the table is unpartitioned, contains no LOB or VARRAY columns, is not a type table, and is not an index-only table with an overflow segment. This scenario can be used to move a user's tables from one tablespace to another.
For example, you need to move joe's tables from tablespace A to tablespace B after a full database export. Follow these steps:
1.     If joe has the UNLIMITED TABLESPACE privilege, revoke it. Set joe's quota on tablespace A to zero. Also revoke all roles that might have such privileges or quotas.
Role revokes do not cascade. Therefore, users who were granted other roles by joe will be unaffected.
2.     Export joe's tables.
3.     Drop joe's tables from tablespace A.
4.     Give joe a quota on tablespace B and make it the default tablespace for joe.
5.     Import joe's tables. (By default, Import puts joe's tables into
tablespace B.)
----------------------------------------------------------------------------------------------------
具体实例参见eygle的文章：http://www.eygle.com/archives/2005/04/ecineeeeiaeioae.html
上 面的官方文档说到了，不针对含有blob字段等的表，如果导入，会报“ORA-00959: 表空间'EXAMPLE'不存在”的错误。那么，下面主要介绍如何导入含有blob字段的表到不同的表空间的一种方法（针对oracle 9i，10g可以用impdp来解决，其中有个参数remap_tablespace=as:bs）。
1、获取含有blob字段表的DDL：可以使用oracle pl/sql包获取，也可以使用第三方工具toad、pl/sql developer。
------------------------------------------------------------------------------
SQL> set long 4000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('TABLE','T_RES_PATIENT') from dual;
CREATE TABLE "WHU"."T_RES_PATIENT"
( "ONECARD_NO" VARCHAR2(20) NOT NULL ENABLE,
"IDENTITYCARD_NO" VARCHAR2(30),
"ADDRESS" VARCHAR2(100),
"WORKPLACE" VARCHAR2(100),
"PATIENT_NAME" VARCHAR2(64) NOT NULL ENABLE,
"BIRTHDAY" VARCHAR2(10),
"CASTE" VARCHAR2(10),
"GENDER" VARCHAR2(4),
"NOTE" VARCHAR2(128),
"PHOTO" BLOB,
"MARRY" VARCHAR2(10),
"BADDRESS" VARCHAR2(128),
"NATION" VARCHAR2(20),
"COUNTRY" VARCHAR2(20),
"RNAME" VARCHAR2(20),
"RELATION" VARCHAR2(20),
"PADDRESS" VARCHAR2(128),
"HADDRESS" VARCHAR2(128),
"TYPE" VARCHAR2(10) NOT NULL ENABLE,
"TEL" VARCHAR2(20),
"ZYTYPE" VARCHAR2(10),
"CASE_NO" VARCHAR2(20) NOT NULL ENABLE,
"CARD_ID" VARCHAR2(3),
"GENDER_NO" VARCHAR2(1),
"TITLE_NAME" VARCHAR2(16),
"COL_NAME" VARCHAR2(32),
CONSTRAINT "PK_RES_PATIENT" PRIMARY KEY ("CASE_NO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "WHU" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "WHU"
LOB ("PHOTO") STORE AS (
TABLESPACE "WHU" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
------------------------------------------------------------------------------
2、修改获取的DDL中表空间名称为新的表空间名称，实例中，修改原有表空间“WHU”为“WOLF”，然后在要导入数据的数据库中创建表。
------------------------------------------------------------------------------
SQL> CREATE TABLE "T_RES_PATIENT"
2 ( "ONECARD_NO" VARCHAR2(20) NOT NULL ENABLE,
3 "IDENTITYCARD_NO" VARCHAR2(30),
4 "ADDRESS" VARCHAR2(100),
5 "WORKPLACE" VARCHAR2(100),
6 "PATIENT_NAME" VARCHAR2(64) NOT NULL ENABLE,
7 "BIRTHDAY" VARCHAR2(10),
8 "CASTE" VARCHAR2(10),
9 "GENDER" VARCHAR2(4),
10 "NOTE" VARCHAR2(128),
11 "PHOTO" BLOB,
12 "MARRY" VARCHAR2(10),
13 "BADDRESS" VARCHAR2(128),
14 "NATION" VARCHAR2(20),
15 "COUNTRY" VARCHAR2(20),
16 "RNAME" VARCHAR2(20),
17 "RELATION" VARCHAR2(20),
18 "PADDRESS" VARCHAR2(128),
19 "HADDRESS" VARCHAR2(128),
20 "TYPE" VARCHAR2(10) NOT NULL ENABLE,
21 "TEL" VARCHAR2(20),
22 "ZYTYPE" VARCHAR2(10),
23 "CASE_NO" VARCHAR2(20) NOT NULL ENABLE,
24 "CARD_ID" VARCHAR2(3),
25 "GENDER_NO" VARCHAR2(1),
26 "TITLE_NAME" VARCHAR2(16),
27 "COL_NAME" VARCHAR2(32),
28 CONSTRAINT "PK_RES_PATIENT" PRIMARY KEY ("CASE_NO")
29 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
30 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
31 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
32 TABLESPACE "WOLF" ENABLE
33 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
34 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
35 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
36 TABLESPACE "WOLF"
37 LOB ("PHOTO") STORE AS (
38 TABLESPACE "WOLF" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
39 NOCACHE
40 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
41 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT));
表已创建。
SQL> desc t_res_patient;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ONECARD_NO NOT NULL VARCHAR2(20)
IDENTITYCARD_NO VARCHAR2(30)
ADDRESS VARCHAR2(100)
WORKPLACE VARCHAR2(100)
PATIENT_NAME NOT NULL VARCHAR2(64)
BIRTHDAY VARCHAR2(10)
CASTE VARCHAR2(10)
GENDER VARCHAR2(4)
NOTE VARCHAR2(128)
PHOTO BLOB
MARRY VARCHAR2(10)
BADDRESS VARCHAR2(128)
NATION VARCHAR2(20)
COUNTRY VARCHAR2(20)
RNAME VARCHAR2(20)
RELATION VARCHAR2(20)
PADDRESS VARCHAR2(128)
HADDRESS VARCHAR2(128)
TYPE NOT NULL VARCHAR2(10)
TEL VARCHAR2(20)
ZYTYPE VARCHAR2(10)
CASE_NO NOT NULL VARCHAR2(20)
CARD_ID VARCHAR2(3)
GENDER_NO VARCHAR2(1)
TITLE_NAME VARCHAR2(16)
COL_NAME VARCHAR2(32)
------------------------------------------------------------------------------
3、从原有数据库中导出这张表数据
------------------------------------------------------------------------------
E:\dbTest>exp xxxx/xxxx@xxxx tables=(t_res_patient) file=e:\dbTest\expPatient.dmp log=e:\dbTest\expPatinet.log
Export: Release 9.2.0.1.0 - Production on 星期四 8月 13 12:08:04 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径 ...
. . 正在导出表 T_RES_PATIENT 63443 行被导出
在没有警告的情况下成功终止导出。
------------------------------------------------------------------------------
4、导入数据：使用ignore=Y参数导入。
------------------------------------------------------------------------------
E:\dbTest>imp ****/**** tables=(t_res_patient) ignore=Y file=e:\dbTest\expPatient.dmp log=e:\dbTest\impPatient.log
Import: Release 9.2.0.1.0 - Production on 星期四 8月 13 12:16:39 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
经由常规路径导出由EXPORT:V09.02.00创建的文件
警告: 此对象由 ****导出, 而不是当前用户
已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将****的对象导入到 ****
. . 正在导入表 "T_RES_PATIENT" 63443行被导入
成功终止导入，但出现警告。
------------------------------------------------------------------------------
查询导入数据记录：
------------------------------------------------------------------------------
SQL> select count(*) from t_res_patient;
COUNT(*)
----------
63443
------------------------------------------------------------------------------
由此可见导入成功。