EXPDP/IMPDP与EXP/IMP在不同用户和表空间之间迁移数据的实现方法，expdpimpdp
1. EXPDP/IMPDP方式SQL> create user zlm identified by zlm;
User created.
SQL> grant connect,resource to zlm;
Grant succeeded.
SQL> create tablespace ts_zlm datafile '/u01/app/oracle/oradata/ora10g/zlm01.dbf' size 100M reuse;
Tablespace created.
SQL> alter user zlm default tablespace ts_zlm;
User altered.
SQL> select username,default_tablespace from dba_users where username='ZLM';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZLM TS_ZLM
SQL> col name for a50
SQL> select name,bytes/1024/1024 from v$datafile where name like '%users01.dbf';
NAME BYTES/1024/1024
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/users01.dbf 5
SQL> set lin 120 pages 120
SQL> col username for a8
SQL> col name for a45
SQL> select a.username,a.default_tablespace,b.name from dba_users a,v$datafile b,v$tablespace c where a.default_tablespace=c.name and b.ts#=c.ts# and a.username='SCOTT';
USERNAME DEFAULT_TABLESPACE NAME
-------- ------------------------------ ---------------------------------------------
SCOTT USERS /u01/app/oracle/oradata/ora10g/users01.dbf
SQL> alter database datafile '/u01/app/oracle/oradata/ora10g/users01.dbf' resize 100M;
Database altered.
SQL> select name,bytes/1024/1024 from v$datafile where name like '%users01.dbf';
NAME BYTES/1024/1024
--------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/users01.dbf 100
SQL> select owner,directory_name from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
SYS DATA_PUMP_DIR
SYS SUBDIR
SYS XMLDIR
SYS MEDIA_DIR
SYS LOG_FILE_DIR
SYS DATA_FILE_DIR
SYS WORK_DIR
SYS ADMIN_DIR
SQL> create directory zlm_pump as '/u01/expdp';
Directory created.
SQL> !mkdir /u01/expdp
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> grant connect,resource to scott;
Grant succeeded.
SQL> grant read,write on directory zlm_pump to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create table zlm1 as select * from dba_objects;
Table created.
SQL> insert into zlm1 select * from dba_objects;
50318 rows created.
SQL> /
50318 rows created.
SQL> select count(*) from zlm1;
COUNT(*)
----------
150954
SQL> select table_name from user_tables where tablespace_name='USERS';
TABLE_NAME
------------------------------
ZLM1
SALGRADE
BONUS
EMP
DEPT
[oracle@ora10g ~]$ expdp scott/tiger parallel=2 directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp tablespaces=users
Export: Release 10.2.0.1.0 - Production on Sunday, 31 August, 2014 14:35:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLESPACE_01": scott/******** parallel=2 directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp tablespaces=users
Estimate in progress using BLOCKS method...--可以通过estimate=block(默认值)指定，还有一个是statistic
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 17.18 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."ZLM1" 14.06 MB 150954 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLESPACE_01 is:
/u01/expdp/scott01.dmp
/u01/expdp/scott02.dmp
Job "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully completed at 14:36:10
[oracle@ora10g ~]$ impdp zlm/zlm parallel=2 remap_schema=scott:zlm remap_tablespace=users:ts_zlm directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp
Import: Release 10.2.0.1.0 - Production on Sunday, 31 August, 2014 14:41:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ZLM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ZLM"."SYS_IMPORT_FULL_01": zlm/******** parallel=2 remap_schema=scott:zlm remap_tablespace=users:ts_zlm directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZLM"."DEPT" 5.656 KB 4 rows
. . imported "ZLM"."EMP" 7.820 KB 14 rows
. . imported "ZLM"."SALGRADE" 5.585 KB 5 rows
. . imported "ZLM"."BONUS" 0 KB 0 rows
. . imported "ZLM"."ZLM1" 14.06 MB 150954 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ZLM"."SYS_IMPORT_FULL_01" successfully completed at 14:41:43
[oracle@ora10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 31 14:43:12 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn zlm/zlm
Connected.
SQL> select table_name from user_tables where tablespace_name='TS_ZLM';
TABLE_NAME
------------------------------
ZLM1
SALGRADE
BONUS
EMP
DEPT
SQL> select object_name from user_objects;
OBJECT_NAME
--------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
ZLM1
PK_DEPT
PK_EMP
SCHEMA SCOTT的表空间USERS中的全部对象已经被导入到SCHEMA ZLM的表空间，包括表和索引
2. EXP/IMP方式
SQL> conn / as sysdba
Connected.
SQL> drop user zlm cascade;
User dropped.
SQL> create user zlm identified by zlm;
User created.
SQL> grant connect,resource to zlm;
Grant succeeded.
SQL> alter user zlm default tablespace ts_zlm;
User altered.
SQL> conn zlm/zlm
Connected.
SQL> select * from cat;
no rows selected
SQL> !
[oracle@ora10g ~]$ exp scott/tiger owner=scott file=/u01/scott01.dmp
Export: Release 10.2.0.1.0 - Production on Sun Aug 31 15:42:00 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table ZLM1 150954 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 with warnings.
[oracle@ora10g ~]$ imp zlm/zlm fromuser=scott touser=zlm file=/u01/exp/scott01.dmp
Import: Release 10.2.0.1.0 - Production on Sun Aug 31 15:43:01 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
IMP-00002: failed to open /u01/exp/scott01.dmp for read --路径指定错了
Import file: expdat.dmp > /u01/scott01.dmp
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "SALGRADE" 5 rows imported
. . importing table "ZLM1" 150954 rows imported
About to enable constraints...
Import terminated successfully with warnings.
[oracle@ora10g ~]$ exit
SQL> col object_name for a15
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------- -------------------
BONUS TABLE
DEPT TABLE
PK_DEPT INDEX
EMP TABLE
PK_EMP INDEX
SALGRADE TABLE
ZLM1 TABLE
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BONUS USERS
DEPT USERS
EMP USERS
SALGRADE USERS
ZLM1 USERS
注意，用imp导入后的对象，都是存放在exp导出时的表空间的，如果要迁移到别的表空间，就要使用alter table move tablespace来实现，先通过下面的SQL拼接语句来获得批量move的语句：
'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACETS_ZLM;'
------------------------------------------------------------------
alter table BONUS MOVE TABLESPACE ts_zlm;
alter table DEPT MOVE TABLESPACE ts_zlm;
alter table EMP MOVE TABLESPACE ts_zlm;
alter table SALGRADE MOVE TABLESPACE ts_zlm;
alter table ZLM1 MOVE TABLESPACE ts_zlm;
SQL> select 'ALTER INDEX ' ||index_name || ' REBUILD TABLESPACE ts_zlm;' from user_indexes;
'ALTERINDEX'||INDEX_NAME||'REBUILDTABLESPACETS_ZLM;'
---------------------------------------------------------------------
ALTER INDEX PK_EMP REBUILD TABLESPACE ts_zlm;
ALTER INDEX PK_DEPT REBUILD TABLESPACE ts_zlm;
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SALGRADE TS_ZLM
EMP TS_ZLM
ZLM1 TS_ZLM
DEPT TS_ZLM
BONUS TS_ZLM
SQL> select index_name,tablespace_name from user_indexes;
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_EMP TS_ZLM
PK_DEPT TS_ZLM
可以看到，zlm用户imp导入的数据库对象已经全部迁移到TS_ZLM表空间去了
总结：
EXPDP/IPMDP只能在server上运行，其运行效率完全取决于磁盘I/O，而EXP/IMP不仅可以运行在server上，也可以运行在client上，所以除了磁盘I/O的制约因素，还有网络方面的因素。
EXPDP/IMPDP比EXP/IMP性能有很大的提高，其中影响最大的就是paralle，通常设置为CPU的个数，通过该参数可以并行导出，大大提高导出速度，而EXP/IMP是没有这个功能的，最多也就只有一个DIRECT=Y,使导出不用经过SELECT到SGA的BUFFER中，提高的速度是有限的，注意他们之间的语法也有不小的区别，参数不可互相替代。
EXPDP/IMPDP可以通过remap_schema和remap_tablespace直接实现不同用户和表空间之间的数据迁移;而EXP/IMP则稍微麻烦一点，需要ALTER TABLE xxx MOVE TABLESPACE xxx/ALTER INDEX xxx REBUILD TABLESPACE xxx方式来实现。move相当于把表再重建一次，可以修改storage参数(如initial、pctfree、pctincrease等)，还可以move到另外的表空间，如果不指定表空间，则在原来的表空间中move，因此做move操作时，目标表空间需要保证有这个表同样大小的剩余空间，整个操作相当于exp/imp(从表空间A导出到表空间B)，move完表后要重建索引。
基于以上几种原因，现在做数据逻辑迁移都是用EXPDP/IMPDP而很少再使用EXP/IMP了。
这个百度上一搜一大把,都说的很详细,楼主没搜过,
数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
exp system/manager@TEST file=d:daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1) query=" where filed1 like '00%'"
上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。
数据的导入
1 将D:daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:daochu.dmp
imp aichannel/aichannel@HUST full=y file=d:datanewsmgnt.dmp ignore=y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:daochu.dmp tables=(table1)
基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。
注意：
操作者要有足够的权限，权限不够它会提示。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。
当然，上面的方法在导出数据时可能会导出很大的包，那是因为你的用户可能在授权的时候授予了DBA的权限，所以可以采用下面方法来进行数据库备份：
数据库备份
建议系统过渡后，每周进行一次备份。或者在数据表发生重大改变前，对要改变的数据表进行备份。
执行以下步骤，进行备份。
在命令行里，敲入“cmd”，回车，进入命令行窗口。
在窗口中，输入：
exp mas/123456@mas
系统提示：输入数组提取缓冲区大小: 4096 >
可以直接回车；
系统提示：导出文件: EXPDAT.DMP>
此处输入导出文件的位置，其路径必须存在，Oracle在这里不会自动建立路径，但可以建立文件名。备份文件以dmp作为后缀。
输入内容如：e:\work\mas_db_090925v1.dmp 回车
系统提示： (1)E(完整的数据库)，(2)U(用户) 或 (3)T(表): (2)U > u
此处可以输入u，也可以直接回车，因为系统此时默认的是U
系统提示：导出权限(......余下全文>>
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。
执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行，
DOS中可以执行时由于 在oracle 8i 中 安装目录ora81BIN被设置为全局路径，
该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写，SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包装后的类文件。
SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类，完成导入导出功能。
当使用EXPDP工具时，其转储文件只能被存放在DIRECTORY对象所对应的OS目录中，面不能直接指定转储文件所在的OS目录。因此，当使用EXPDP工具时，必须首先建立DIRECTORY对象，并且需要为数据库用户授予使用DIRECTORY对象的权限。
例：SQL>conn sys/manager
SQL>CREATE DIRECTORY dump_dir AS '/u01/app/dump';
SQL>GRANT READ,WRITE ON DIRECTORY dump_dir to scott;
确认备份泵出文件的位置
========================
SQL> set linesize 180
SQL> col directory_path format a60
SQL> select * from dba_directories
下面介绍的是exp/imp导入导出的实例。
数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\datanewsmgnt.dmp tables=(inner_notify,notify_staff_relat)
4 将数据库中的表table1中的字段filed1以”00″打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=” where filed1 like ‘00%’”
上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。
数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
imp aichannel......余下全文>>