Oracle Data Pump 导出和导入数据

Data pump export/import(hereinafter referred to as Export/Import for ease of reading)是一种将元数据和数据导出到系统文件集/从系统文件集导入数据的服务端工具;导出的文件可以移动到其它服务器上,但只能使用impdp导入;使用前要先建立目录(directory)和授权:

conn / as sysdba CREATE DIRECTORY PUMP_DIR AS '/u01/backup'; 

创建后即可查询出目录信息,包括目录名称、所有者、目录位置:

SQL> column directory_path format a50 SQL>select * from dba_directories where directory_name='PUMP_DIR';  OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------- SYS                            PUMP_DIR                       /u01/backup 

删除已有的目录

drop directory pump_dir; 

向某些用户授权使用目录,可以授权read或write权限,或者两个权限同时授予:

grant read,write on directory pump_dir to frdc; 

撤销权限

revoke read,write on directory pump_dir from frdc; 

1导出数据

1.1基本说明

EXPDP USERID='sywu/sywu' job_name=export_tb parallel=3 tables=(tb01,tb02,tb03) dumpfile=pump_dir:dw_20150602.dmp logfile=pump_dir:exptb.log version='11.2.0.1.0' exclude='' 

1.2带条件导出数据

有时候只想导出特定的数据,比如id=10或id in(10,20,30),这种情况下可以用条件限定导出数据(注意字符转义)

expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:/"where object_id in/(10,20,30/)/" dumpfile=query_tab.dump directory=pump_dir  Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:09:24 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."SYS_EXPORT_TABLE_01":  userid=ops$sywu/******** tables=tb01 query=tb01:"where object_id in(10,20,30)" dumpfile=query_tab.dump directory=pump_dir  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 88 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$SYWU"."TB01"                           25.70 KB     192 rows Master table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is:   /u01/backup/query_tab.dump Job "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:09:32 

或者通过rownum限定行的方式导出数据(注意字符转义)

 expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:/"where rownum/<10/" dumpfile=query_tab.dump directory=pump_dir   Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:14:28 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."SYS_EXPORT_TABLE_01":  userid=ops$sywu/******** tables=tb01 query=tb01:"where rownum<10" dumpfile=query_tab.dump directory=pump_dir  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 88 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$SYWU"."TB01"                           11.41 KB       9 rows Master table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is:   /u01/backup/query_tab.dump Job "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:14:36 

有些情况下,为了满足测试要求,我们可能需要整库导出或整个schema下的数据导出,但又不需要所有数据,so 同样可以使用限定行数的方式限定所有表数据行导出数据

expdp userid='ops$sywu/sywu' SCHEMAS='ops$sywu' query=/"where rownum/<10/" dumpfile=schema_sywu.dump directory=pump_dir  Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:18:34 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."SYS_EXPORT_SCHEMA_01":  userid=ops$sywu/******** SCHEMAS=ops$sywu query="where rownum<10" dumpfile=schema_sywu.dump directory=pump_dir  Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 104 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$SYWU"."TB01"                           11.41 KB       9 rows . . exported "OPS$SYWU"."TB02"                           6.015 KB       9 rows . . exported "OPS$SYWU"."T_EXCE"                         5.820 KB       2 rows Master table "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.SYS_EXPORT_SCHEMA_01 is:   /u01/backup/schema_sywu.dump Job "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:18:55 

有些情况下,导出的数据需要通过网络或通过媒介传输到其它目的地,考虑媒介的大小和网络情况,每次只能限定传输,为了达到这个目的可以将数据导出到多个文件,然后再分批或独立发送;比如评估的数据大小是80M,我想把文件导出为4个文件,每个文件的大小为20M,so 我像这样导出数据:

expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log  Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:03:03 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."EXPORTTB_BYSIZE":  userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log  Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 104 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$SYWU"."TB01"                           74.64 MB  844416 rows . . exported "OPS$SYWU"."TB02"                           452.3 KB   13448 rows . . exported "OPS$SYWU"."T_EXCE"                         5.820 KB       2 rows Master table "OPS$SYWU"."EXPORTTB_BYSIZE" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.EXPORTTB_BYSIZE is:   /u01/backup/size_each_sywu.dump   /u01/backup/file02.dmp   /u01/backup/file03.dmp   /u01/backup/file04.dmp Job "OPS$SYWU"."EXPORTTB_BYSIZE" successfully completed at 18:03:24 

 expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log  Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:21:07 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."EXPORTTB_BYSIZE":  userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log  Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 104 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes Job "OPS$SYWU"."EXPORTTB_BYSIZE" stopped due to fatal error at 18:21:26 

指定文件大小乘以文件个数小于实际数据量大小,导出报错停止,仅仅是停止;

ls -lh /u01/backup  -rw-r--r--. 1 oracle asmadmin 1.8K Jun  2 17:18 export.log -rw-r--r--. 1 oracle asmadmin 1.5K Jun  2 18:21 exportt01.log -rw-r-----. 1 oracle asmadmin  10M Jun  2 18:21 file02.dmp -rw-r-----. 1 oracle asmadmin  10M Jun  2 18:21 file03.dmp -rw-r-----. 1 oracle asmadmin  10M Jun  2 18:21 file04.dmp -rw-r-----. 1 oracle asmadmin  10M Jun  2 18:21 size_each_sywu.dump 

此时查看数据库,发现一张于job_name名称相同的表EXPORTTB_BYSIZE,表中记录了导数时的信息;查询job的状态为:NOT RUNING;

select * from tab;  TNAME                          TABTYPE  CLUSTERID ------------------------------ ------- ---------- EXPORTTB_BYSIZE                TABLE TB01                           TABLE TB02                           TABLE T_EXCE                         TABLE  select job_name,state from dba_datapump_jobs;  JOB_NAME                       STATE ------------------------------ ------------------------------ EXPORTTB_BYSIZE                NOT RUNNING 

继续完成导数可以通过添加dump文件或使用系统默认的文件

expdp userid='ops$sywu/sywu' attach=EXPORTTB_BYSIZE  Export: Release 11.2.0.3.0 - Production on Tue Jun 2 22:28:37 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options  Job: EXPORTTB_BYSIZE   Owner: OPS$SYWU                          Operation: EXPORT                            Creator Privs: FALSE                             GUID: 178798A215814641E053FE1813ACD41C   Start Time: Tuesday, 02 June, 2015 22:28:38   Mode: SCHEMA                            Instance: sydb   Max Parallelism: 1   EXPORT Job Parameters:   Parameter Name      Parameter Value:      CLIENT_COMMAND        userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log    State: IDLING                            Bytes Processed: 0   Current Parallelism: 1   Job Error Count: 0   Dump File: /u01/backup/size_each_sywu.dump     size: 10,485,760     bytes written: 10,485,760   Dump File: /u01/backup/file02.dmp     size: 10,485,760     bytes written: 10,485,760   Dump File: /u01/backup/file03.dmp     size: 10,485,760     bytes written: 10,485,760   Dump File: /u01/backup/file04.dmp     size: 10,485,760     bytes written: 10,485,760  Worker 1 Status:   Process Name: DW00   State: UNDEFINED                         Object Schema: OPS$SYWU   Object Name: TB01   Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA   Completed Objects: 1   Total Objects: 3   Completed Bytes: 41,844,736   Percent Done: 45   Worker Parallelism: 1  Export> add_file=file05.dmp  Export> start_job 

开始任务后,导数继续执行

select job_name,state from dba_datapump_jobs;  JOB_NAME                       STATE ------------------------------ ------------------------------ EXPORTTB_BYSIZE                EXECUTING  select job_name,state from dba_datapump_jobs;  JOB_NAME                       STATE ------------------------------ ------------------------------ EXPORTTB_BYSIZE                COMPLETING 

在添加dump file(add_file)时,添加的文件最终大小取决于添加了多少dump file,在上例中仅添加了一个file05.dmp,so 剩余的数据量将全部归纳到该文件中;如果未添加文件oracle会自己分配和创建一个dump文件;

[oracle@sywu backup]$ ls -ltrh -rw-r-----. 1 oracle asmadmin  10M Jun  2 22:30 file02.dmp -rw-r-----. 1 oracle asmadmin  10M Jun  2 22:30 file03.dmp -rw-r-----. 1 oracle asmadmin  10M Jun  2 22:30 file04.dmp -rw-r-----. 1 oracle asmadmin  10M Jun  2 22:30 size_each_sywu.dump -rw-r-----. 1 oracle asmadmin  36M Jun  2 22:30 file05.dmp -rw-r--r--. 1 oracle asmadmin 2.4K Jun  2 22:30 exportt01.log [oracle@sywu backup]$ cat exportt01.log   Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:42:17 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. ;;;  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."EXPORTTB_BYSIZE":  userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log  Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 104 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes Job "OPS$SYWU"."EXPORTTB_BYSIZE" stopped due to fatal error at 18:42:36 Job EXPORTTB_BYSIZE has been reopened at Tuesday, 02 June, 2015 22:28  Restarting "OPS$SYWU"."EXPORTTB_BYSIZE":  userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log  . . exported "OPS$SYWU"."TB01"                           74.64 MB  844416 rows . . exported "OPS$SYWU"."TB02"                           452.3 KB   13448 rows . . exported "OPS$SYWU"."T_EXCE"                         5.820 KB       2 rows Master table "OPS$SYWU"."EXPORTTB_BYSIZE" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.EXPORTTB_BYSIZE is:   /u01/backup/size_each_sywu.dump   /u01/backup/file02.dmp   /u01/backup/file03.dmp   /u01/backup/file04.dmp   /u01/backup/file05.dmp Job "OPS$SYWU"."EXPORTTB_BYSIZE" completed with 1 error(s) at 22:30:38 

1.3导出某个schema下的数据

对于导出schema下的数据,建议使用sysdba用户导出,因为随着系统的复杂和环境不一,某些时候依赖的包、对象、存储过程可能是属于另一个用户的,导出时被导出schema未必具有完全的权限,同时还要考虑导入的目的地环境;当然万事没有绝对,完全取决于应用;

expdp userid='sys/oracle as sysdba' job_name=export_schema SCHEMAS='ops$sywu'  directory=pump_dir dumpfile=schema_sywu.dmp logfile=schema_sywu.log 

1.4整库导出

整库导出必须使用sysdba权限;

expdp userid='sys/oracle as sysdba' job_name=export_schema FULL=Y  directory=pump_dir dumpfile=full_dbexport.dmp logfile=full_dbexport.log 

1.5查询导数job状态

column username format a10  column opname format a20  column units format a10  select     round(sofar/totalwork*100,2)  percent_completed,     t.sid,t.SERIAL#,t.USERNAME,t.OPNAME,t.TOTALWORK,t.UNITS,t.START_TIME,t.MESSAGE  from     v$session_longops  t  where   sofar <> totalwork  order by target,sid;  PERCENT_COMPLETED        SID    SERIAL# USERNAME   OPNAME                TOTALWORK UNITS      START_TIME     MESSAGE ----------------- ---------- ---------- ---------- -------------------- ---------- ---------- -------------- ---------------------------------------------------------------------------             70.88        128      17213 FRDC       Table Scan              2533381 Blocks     02-6月 -15     Table Scan:  FRDC.H_BASE_MAIN05_T: 1795601 out of 2533381 Blocks done             12.58        128      17213 FRDC       Table Scan              2533381 Blocks     02-6月 -15     Table Scan:  FRDC.H_BASE_MAIN05_T: 318705 out of 2533381 Blocks done             70.74        128      17213 FRDC       Table Scan              2533381 Blocks     02-6月 -15     Table Scan:  FRDC.H_BASE_MAIN05_T: 1792046 out of 2533381 Blocks done             29.06          4      57701 FRDC       Index Fast Full Scan       2127 Blocks     13-5月 -15     Index Fast Full Scan:  FRDC.O_BASE_DW: 618 out of 2127 Blocks done                 0          5        280 SYS        Rowid Range Scan     4294967296 Blocks     02-6月 -15     Rowid Range Scan:  FRDC.O_BASE_DW_T: 22055 out of 0 Blocks done                 0        427      54951 FRDC       EXPORT_TB                  7900 MB         02-6月 -15     EXPORT_TB: EXPORT : 0 out of 7900 MB done 

2数据导入

2.1基本语法

$ impdp userid='uname/pwd' directory=pump_dir dumpfile=file01.dmp job_name='' log_file=''  remap_schema='scott:sywu' remap_table='tb01:tb02' remap_tablespace='tbs01:ts01' tables='' schemas='' table_exists_action='' tablespaces='' exclude='' sqlfile='' 

2.2导入数据

导入数据时要确认导入的用户是否存在,是否有权限访问表空间、是否具有读写directory的权限;

$ impdp userid='ops$sywu/sywu' schemas='ops$sywu' directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp  Import: Release 11.2.0.3.0 - Production on Sat Jun 6 13:39:43 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "OPS$SYWU"."IMPORT_OPSSYWU" successfully loaded/unloaded Starting "OPS$SYWU"."IMPORT_OPSSYWU":  userid=ops$sywu/******** schemas=ops$sywu directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp  Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "OPS$SYWU"."TB01"                           74.64 MB  844416 rows . . imported "OPS$SYWU"."TB02"                           452.3 KB   13448 rows . . imported "OPS$SYWU"."T_EXCE"                         5.820 KB       2 rows Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "OPS$SYWU"."IMPORT_OPSSYWU" successfully completed at 13:39:55 

导入数据时重定义schema和tablespaces,重定义schema建议使用sysdba权限导入数据:

create tablespace tbs02 datafile '/u01/oradata/sydb/tbs02.dbf' size 10m autoextend on uniform size 4m; create user sywu identified by sywu default tablespace tbs02 quota unlimited on tbs02; grant create session,resource to sywu; grant read,write on directory pump_dir to sywu; exit;  $ impdp userid="'sys/oracle as sysdba'" remap_schema='ops$sywu:sywu' remap_tablespace=tbs01:tbs02 directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp  Import: Release 11.2.0.3.0 - Production on Sat Jun 6 13:51:48 2015  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."IMPORT_OPSSYWU" successfully loaded/unloaded Starting "SYS"."IMPORT_OPSSYWU":  userid="sys/******** AS SYSDBA" remap_schema=ops$sywu:sywu remap_tablespace=tbs01:tbs02 directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp  Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SYWU"."TB01"                               74.64 MB  844416 rows . . imported "SYWU"."TB02"                               452.3 KB   13448 rows . . imported "SYWU"."T_EXCE"                             5.820 KB       2 rows Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."IMPORT_OPSSYWU" successfully completed at 13:52:08 

其它还有限定行数的操作,操作类似上面导出操作;The end(2015-06-06)