oracle 数据库imp操作导入dmp文件时表空间问题

转：http://blog.csdn.net/zhuxiaowei716/article/details/44651465

一：

假设数据用户USER1使用默认表空间TS1，用户USER2使用默认表空间TS2，现在将USER1的数据导入到USER2，要求使用表空间TS2。通过IMP命令参数tablespaces指定表空间是不行的，其他复杂方法要修改配额什么的，麻烦。整理了一下，大致有以下几种方法可以：
1. 有一个简单的方法，适用于数据比较少的情况：直接使用UltraEdit打方DMP文件，默认是16进制，切换成文本模式，将里面的：TABLESPACE "TS1"全部替换成TABLESPACE "TS2"，然后再导入，不用加什么tablespaces=TS2之类的参数即可，当然加了也无所所谓。
C:\>IMP USER2/USER2 log=C:\plsimp.log file=D:\database\USER1.dmp fromuser=USER1 touser=USER2 ignore=yes tablespaces=TS2

不过有个缺点，如果DMP文件有几百M甚至几G，嘿嘿嘿嘿，不知道UltraEdit有啥反应？

2. 还有另外一种比较好的方法。基本思路都是先从USER1导出表结构（DMP或者SQL脚本），然后在USER2用户下创建空的数据库表（如果是DMP，则参见方法1；如果是SQL脚本，则稍作修改后以USER2用户执行即可），再将包括数据的DMP导入，注意要设IGNORE＝Y，并指定FROMUSER和TOUSER。具体实现方法为：
（1）先用工具导出USER1的表结构（不要导出CHECK和FK，特别是FK！），TOAD和PL/SQL都有此功能。PL/SQL Developer导出表结构：Tools-->Export User Objects（导出用户对象） -->选择要导出的表(包括Sequence等)-->.sql文件，导出的都为sql文件。或者使用imp的indexfile选项生成表结构的SQL脚本。
（2）在USER2用户下创建相同的表结构
（3）导入完整的数据

PS：
PL/SQL Developer工具操作步骤：
（1）导出表结构:
Tools-->Export User Objects（导出用户对象） -->选择要导出的表(包括Sequence等)-->.sql文件，导出的都为sql文件
（这一步也可以导出一个只包括表结构的DMP文件。）

（2）导出表数据:
Tools-->Export Tables-->选择表,选择SQL Inserts-->.sql文件
（这一步也可以导出一个包括表结构和数据或者只有数据没有表结构图的DMP文件。）

（3）导入表结构:
执行刚刚导出的sql文件，记住要删掉table前的用户名，比如以前这表名为sys.tablename，必须删除sys
（如果是只包括表结构的DMP文件，则使用UltraEdit修改表空间，再导入。如方法1）

（4）导入表数据:
执行刚刚导出的sql文件
（如果是DMP文件，则直接导入即可，注意要设IGNORE＝Y，并指定FROMUSER和TOUSER。）

IMP命令操作步骤：
首先：exp user/pw file=file.dmp owner=yourUser1
然后：imp user/pw file=file.dmp indexfile=xxxx.sql fromuser=yourUser1 touser=yourUser2
随后：修改xxxx.sql中的关于有表空间设置的地方为新的表空间,并去掉相关的注释(rem),然后执行xxxx.sql创建相关对象。
最后：imp user/pw file=file.dmp fromuser=yourUser1 touser=yourUser2 ignore=y

以上转自http://hi.baidu.com/e_ville/blog/item/61712cf7497cba25720eecec.html

一直以来，我都认为只要指定用户的默认表空间，向该用户导入数据时，会自动进入到默认表空间。后来发现从System导出的dmp文件在导入时，即使指定新用户的默认表空间，还是要往System表空间中导数据。

上网搜了一下，还是有解决方法的，常见的方法如下：

SQL> create user myhuang identified by myhuang default tablespace myhuang;

SQL> grant resource,connect to myhuang;

SQL> grant dba to myhuang;//赋DBA权限

SQL> revoke unlimited tablespace from myhuang;//撤销此权限

SQL> alter user myhuang quota 0 on system;//将用户在System表空间的配额置为0

SQL> alter user myhuang quota unlimited on myhuang;//设置在用户在myhuang表空间配额不受限。

经过上述设置后，就可以用imp导入数据，数据将会进入指定的myhuang表空间：

C:\Documents and Settings\myhuang>imp system/123456@vdb fromuser=lnxh tous

er=myhuang file=G:\myhuang\lnxh.dmp ignore=y grants=n

顺便说两个小问题：

（1）IMP-00003: 遇到 Oracle 错误 1658

ORA-01658: 无法为表空间 MYHUANG 中的段创建 INITIAL 区

通常这个问题可以通过Resize增加表空间数据文件大小来解决。

（2）删除表空间

SQL> drop tablespace myhuang including contents and datafiles;

在10g中实验，drop表空间之后，仍然需要手动去删除数据文件。

//2008-08-24补充————————————————————————
另一种比较好的方法：

Create tablespace {tbs_name} datafile ‘{file_path}’ size 500M autoextend on next 10M;

Create user {u_name} identified by {u_pwd} default tablespace {tbs_name} quota unlimited on {tbs_name};

Grant connect,imp_full_database to {u_name};

Imp {u_name}/{u_pwd}@{local_svrname} fromuser={from_user} touser={u_name} file={dmp_file_path} ignore=y tablespaces={tbs_name};

此方法不需要授予新用户DBA权限。
此方法的存在的问题是：可能导致包含BLOB、CLOB字段的表导入失败，这种情况下可以先用sql脚本将表结构建立起来，再导入相应的数据。

转自http://www.cnblogs.com/KissKnife/archive/2007/09/17/896459.html

方法二：

一直以来，我都认为只要指定用户的默认表空间，向该用户导入数据时，会自动进入到默认表空间。后来发现从System导出的dmp文件在导入时，即使指定新用户的默认表空间，还是要往System表空间中导数据。

上网搜了一下，还是有解决方法的，常见的方法如下：

SQL> create user myhuang identified by myhuang default tablespace myhuang;

SQL> grant resource,connect to myhuang;

SQL> grant dba to myhuang;//赋DBA权限

SQL> revoke unlimited tablespace from myhuang;//撤销此权限

SQL> alter user myhuang quota 0 on system;//将用户在System表空间的配额置为0

SQL> alter user myhuang quota unlimited on myhuang;//设置在用户在myhuang表空间配额不受限。

 

经过上述设置后，就可以用imp导入数据，数据将会进入指定的myhuang表空间：

C:\Documents and Settings\myhuang>imp system/123456@vdb fromuser=lnxh tous

er=myhuang file=G:\myhuang\lnxh.dmp ignore=y grants=n

 

顺便说两个小问题：

（1）IMP-00003: 遇到 ORACLE 错误 1658

ORA-01658: 无法为表空间 MYHUANG 中的段创建 INITIAL 区

通常这个问题可以通过Resize增加表空间数据文件大小来解决。

 

（2）删除表空间

SQL> drop tablespace myhuang including contents and datafiles;

在10g中实验，drop表空间之后，仍然需要手动去删除数据文件。

 
//2008-08-24补充————————————————————————
另一种比较好的方法：

Create tablespace {tbs_name} datafile ‘{file_path}’ size 500M autoextend on next 10M;

Create user {u_name} identified by {u_pwd} default tablespace {tbs_name} quota unlimited on {tbs_name};

Grant connect,imp_full_database to {u_name};

Imp {u_name}/{u_pwd}@{local_svrname} fromuser={from_user} touser={u_name} file={dmp_file_path} ignore=y tablespaces={tbs_name};

此方法不需要授予新用户DBA权限。
此方法的存在的问题是：可能导致包含BLOB、CLOB字段的表导入失败，这种情况下可以先用sql脚本将表结构建立起来，再导入相应的数据。

转：http://blog.itpub.net/9252210/viewspace-624829/

 今天同事问了一个问题，在imp的时候，为了加快速度，想先导入数据，最后再创建index或者启用约束，该如何操作？做了一些测试，得出如下结论：

1. 在imp的时候，是先imp数据，然后再创建index和创建约束的。(我以前一直认为先创建约束，禁用，然后imp完数据以后再启用)，结论是约束在imp完数据以后再创建，如果违反了约束则会报错。

2. 如果exp中的约束是通过
    alter table table1 add(constraint ck_name unique(name) deferrable novalidate); 来创建的，即不验证原始数据，那么在imp的时候会报错：
IMP-00003: ORACLE error 2299 encountered
ORA-02299: cannot validate (ECC_VIEW.CK_NAME) - duplicate keys found

3. exp时只定了tables，则其他对象(e.g.view,procedure)不会exp，但是indexes和constraint会exp.

4. 实验结果
--1. 创建表空间
SQL> conn sys/oracle@nfdb
SQL> CREATE TABLESPACE "LEIZ" LOGGING DATAFILE '/u02/oradata/nfdb/LEIZ.dbf' SIZE 5M 

--2. 创建测试用户    
SQL> create user zhanglei identified by ecc default tablespace leiz
SQL> create user ecc_view identified by ecc default tablespace data

--3. 授权
SQL> grant create session to zhanglei;
Grant succeeded
SQL> grant create table to zhanglei;
Grant succeeded
SQL> alter user ecc_view quota 1m on leiz ;
用户已更改。
SQL> alter user ecc_view quota 1m on data ;
用户已更改。

--4. user zhanglei exp 
SQL> conn zhanglei/ecc@nfdb 
SQL> create table table1 (id varchar2(10), name varchar2(10));
Table created
SQL> insert into table1 values(1,a);
SQL> insert into table1 values(1,b);
SQL> insert into table1 values(1,c);
SQL> insert into table1 values(1,d);
SQL> insert into table1 values(2,e);
SQL> insert into table1 values(2,f);
SQL> insert into table1 values(2,g);
SQL> insert into table1 values(3,h);
SQL> insert into table1 values(3,i);
SQL> insert into table1 values(3,j);
SQL> insert into table1 values(4,k);
SQL> commit;
Commit complete

SQL> select * from table1;
ID      NAME
---     ----
1 a
1 b
1 c
1 d
2 e
2 f
2 g
3 h
3 i
3 j
4 k
11 rows selected

SQL> create index index1 on table1(id) tablespace leiz;
Index created

SQL> create index index2 on table1(name) tablespace data;
INDEX created
 
SQL> ALTER TABLE "ZHANGLEI"."TABLE1" ADD (CONSTRAINT "CK_ID" CHECK(id<10))

[oracle@ntkdb ~]$ exp zhanglei/ecc@nfdb file=./table1.dmp 
Export: Release 10.2.0.1.0 - Production on Mon Jan 11 11:08:56 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZHANGLEI 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZHANGLEI 
About to export ZHANGLEI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZHANGLEI's tables via Conventional Path ...
. . exporting table                         TABLE1         11 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

--5. user ecc_view imp 
SQL> conn sys/oracle@nfdb as sysdba 
SQL> alter user ecc_view quota 1m on leiz ;
用户已更改。
SQL> alter user ecc_view quota 1m on data ; 
 
a. 正常导入
[oracle@ntkdb ~]$ imp ecc_view/ecc@nfdb file=./table1.dmp fromuser=zhanglei touser=ecc_view;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:16:47 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
About to enable constraints...
Import terminated successfully without warnings.

b. INDEXES=N
[oracle@ntkdb ~]$ imp ecc_view/ecc@nfdb file=./table1.dmp fromuser=zhanglei touser=ecc_view INDEXES=N;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:17:41 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
About to enable constraints...
Import terminated successfully without warnings.

c. CONSTRAINTS=N
[oracle@ntkdb ~]$ imp ecc_view/ecc@nfdb file=./table1.dmp fromuser=zhanglei touser=ecc_view CONSTRAINTS=N;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:19:20 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
About to enable constraints...
Import terminated successfully without warnings.
验证约束是否导入：
SQL> SELECT * FROM USER_CONSTRAINTS;
     0 rows selected

d. 异常情况

(1) 表空间不足
SQL> conn sys/oracle@nfdb as sysdba 
SQL> alter user ecc_view quota 0m on data ; 
用户已更改。

[oracle@ntkdb ~]$ imp ecc_view/ecc@nfdb file=./table1.dmp fromuser=zhanglei touser=ecc_view
......Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
IMP-00017: following statement failed with ORACLE error 1536:
 "CREATE INDEX "INDEX2" ON "TABLE1" ("NAME" )  PCTFREE 10 INITRANS 2 MAXTRANS"
 " 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAUL"
 "T)                    LOGGING"
IMP-00003: ORACLE error 1536 encountered
ORA-01536: space quota exceeded for tablespace 'DATA'
IMP-00017: following statement failed with ORACLE error 20000:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"INDEX2"',NULL,NULL,NULL,11,1,4,1,1"
 ",1,0,0); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: INDEX "ECC_VIEW"."INDEX2" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2121
ORA-06512: at "SYS.DBMS_STATS", line 5393
ORA-06512: at line 1
Import terminated successfully with warnings.

(2) 表中存在NOVALIDATE的约束
SQL> conn sys/oracle@nfdb as sysdba 
SQL> ALTER TABLE "ZHANGLEI"."TABLE1" ADD (CONSTRAINT "CK_NAME" UNIQUE("NAME") DEFERRABLE  NOVALIDATE) ;
用户已更改。
    
[oracle@ntkdb ~]$ imp ecc_view/ecc@nfdb file=./table1.dmp fromuser=zhanglei touser=ecc_view
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 11:12:43 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                       "TABLE1"         11 rows imported
IMP-00017: following statement failed with ORACLE error 2299:
 "ALTER TABLE "TABLE1" ADD  CONSTRAINT "CK_NAME" UNIQUE ("NAME") DEFERRABLE U"
 "SING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS"
 "TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "LEIZ" LOGGING ENABL"
 "E "
IMP-00003: ORACLE error 2299 encountered
ORA-02299: cannot validate (ECC_VIEW.CK_NAME) - duplicate keys found
About to enable constraints...
Import terminated successfully with warnings.

e. 说明：exp时指定table时，也会exp index和constraints
[oracle@ntkdb ~]$ exp zhanglei/ecc@nfdb file=./table1.dmp tables=table1;
Export: Release 10.2.0.1.0 - Production on Mon Jan 11 14:53:49 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                         TABLE1         12 rows exported
Export terminated successfully without warnings.

[oracle@ntkdb ~]$ imp ecc_view/ecc@nfdb file=./table1.dmp fromuser=zhanglei touser=ecc_view;
Import: Release 10.2.0.1.0 - Production on Mon Jan 11 14:53:59 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
CONNECTED to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZHANGLEI, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ZHANGLEI's objects into ECC_VIEW
. . importing table                       "TABLE1"         12 rows imported
About to enable constraints...
Import terminated successfully without warnings.