【EXPDP/IMPDP】ORACLE数据泵导入导出案例(expdp & impdp)

时间:2021-01-06 14:46:32

概要:

因项目需要,通常需要将生产库下的部分数据抽取并恢复到测试库上

本文主要介绍数据泵导入导出的几种情况以及错误处理

案例环境:

rhel-server-6.5-x86_64

oracle 11.2.0.4.0

一、数据泵导出

a、按用户导出

b、按表导出

b1、全表导出

b2、按查询条件导出表

c、按表空间导出

二、数据泵导入

a、导入到具体用户

a1、按用户导入,不改变schema

a2、按用户导入,改变schema

b、导入表

b1、导入表,不改变schema

b2、导入表,改变schema

c、导入表空间

源数据库:

1、首先查看数据泵目录

SQL> set lines 200
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            SUBDIR                         /u01/app/oracle/product/11204/db/demo/schema/order_entry//2002/Sep
SYS                            SS_OE_XMLDIR                   /u01/app/oracle/product/11204/db/demo/schema/order_entry/
SYS                            LOG_FILE_DIR                   /u01/app/oracle/product/11204/db/demo/schema/log/
SYS                            MEDIA_DIR                      /u01/app/oracle/product/11204/db/demo/schema/product_media/
SYS                            DATA_FILE_DIR                  /u01/app/oracle/product/11204/db/demo/schema/sales_history/
SYS                            XMLDIR                         /u01/app/oracle/product/11204/db/rdbms/xml
SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11204/db/ccr/hosts/localhost.localdomain/state
SYS                            DATA_PUMP_DIR                  /u01/app/oracle/admin/orcl/dpdump/
SYS                            ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/11204/db/ccr/state
9 rows selected.
SQL>
 
 
 
 
 

此处,我们数据泵目录选用DATA_PUMP_DIR ,其对应的目录路径为  /u01/app/oracle/admin/orcl/dpdump/  当然此处也可以新建数据泵的directory,命令如下:

SQL> create directory dump_test as'/dump/test';

Directory created.
 

2、赋权限(数据泵导入导出时的执行用户 对上面选用的数据泵目录要有读写权限)

SQL> grant read,write on directory DATA_PUMP_DIR to hr;

Grant succeeded.
 

3、数据泵导出

      3.1>按用户导出

a>首先查看需要导出的用户所在的表空间,以及该用户下的表在初始化时占用表空间的大小(换算为MB)此处以HR用户为例:

SQL> SELECT ds.tablespace_name,
  2         SUM(ds.initial_extent) / 1024 / 1024 as initial_extent
  3    FROM DBA_SEGMENTS ds
  4   WHERE ds.owner IN ( --查看账户状态为open的用户HR
  5                      SELECT du.username
  6                        FROM DBA_USERS du
  7                       WHERE du.account_status = 'OPEN'
  8                         and du.username = 'HR')
  9   GROUP BY ds.tablespace_name
 10   ORDER BY initial_extent desc;

TABLESPACE_NAME                INITIAL_EXTENT
------------------------------ --------------
EXAMPLE                                1.5625
USERS                                   .0625

SQL> 

b>在表空间数量较多的情况下,可以用以下语句查看表在初始化时占用表空间的总大小:

SQL> SELECT SUM(ds.initial_extent) / 1024 / 1024 as initial_extent
  2    FROM DBA_SEGMENTS ds
  3   WHERE ds.owner IN ( --查看账户状态为open的用户HR
  4                      SELECT du.username
  5                        FROM DBA_USERS du
  6                       WHERE du.account_status = 'OPEN'
  7                         and du.username = 'HR');


INITIAL_EXTENT
--------------
         1.625

SQL>

c>查看表空间对应的数据文件大小

SQL> set lines 200
SQL> col FILE_NAME for a50
SQL> col TOTAL_SPACE for a20
SQL> select tablespace_name, file_id, file_name,
  2  round(bytes/(1024*1024),0)||' MB' total_space,AUTOEXTENSIBLE
  3  from dba_data_files
  4  order by tablespace_name;

TABLESPACE_NAME                   FILE_ID FILE_NAME                                          TOTAL_SPACE          AUT
------------------------------ ---------- -------------------------------------------------- -------------------- ---
EXAMPLE                                 5 /oradata/orcl/example01.dbf                        313 MB               YES
SYSAUX                                  2 /oradata/orcl/sysaux01.dbf                         520 MB               YES
SYSTEM                                  1 /oradata/orcl/system01.dbf                         750 MB               YES
TEST_TBS                                6 /oradata/orcl/test_tbs.dbf                         30 MB                YES
UNDOTBS1                                3 /oradata/orcl/undotbs01.dbf                        90 MB                YES
USERS                                   4 /oradata/orcl/users01.dbf                          5 MB                 YES

6 rows selected.

SQL>
上面a、b、c三个步骤查到的结果,在目标库规划存储空间大小和建表空间初始大小时作为参考条件。

d> expdp按用户导出(schemas=hr

$ expdp hr/hr directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=hr.log schemas=hr
结果如下:

Export: Release 11.2.0.4.0 - Production on Sun Jul 3 21:38:06 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/******** directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=hr.log schemas=hr 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."JOBS"                                 6.992 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.476 KB       4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/orcl/dpdump/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jul 3 21:39:19 2016 elapsed 0 00:01:09

      3.2>按表导出

3.2.1>全表导出

expdp hr/hr directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=tab.log tables=hr.employees,hr.departments
结果如下:

Export: Release 11.2.0.4.0 - Production on Sun Jul 3 21:51:45 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=tab.log tables=hr.employees,hr.departments 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl/dpdump/tab.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jul 3 21:52:05 2016 elapsed 0 00:00:17

             3.2.2>按查询条件导出表

$ expdp hr/hr directory=DATA_PUMP_DIR dumpfile=condition.dmp logfile=condition.log tables=hr.employees query=\' WHERE DEPARTMENT_ID\=\10\'

 结果如下: 
  

Export: Release 11.2.0.4.0 - Production on Sun Jul 3 22:06:09 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** directory=DATA_PUMP_DIR dumpfile=condition.dmp logfile=condition.log tables=hr.employees query=' WHERE DEPARTMENT_ID=10' 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES"                            9.320 KB       1 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/orcl/dpdump/condition.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jul 3 22:06:25 2016 elapsed 0 00:00:14

      3.4>按表空间导出

             以example表空间为例

expdp hr/hr directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tablespace.log  TABLESPACES=example;
结果如下:

Export: Release 11.2.0.4.0 - Production on Sun Jul 3 22:14:26 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_TABLESPACE_01":  hr/******** directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tablespace.log TABLESPACES=example 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."JOBS"                                 6.992 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.476 KB       4 rows
Master table "HR"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLESPACE_01 is:
  /u01/app/oracle/admin/orcl/dpdump/tablespace.dmp
Job "HR"."SYS_EXPORT_TABLESPACE_01" successfully completed at Sun Jul 3 22:15:04 2016 elapsed 0 00:00:36

4、将源数据库出的文件传到目标数据库

      4.1>首先查看目标端据库的数据泵目录

SQL> set lines 200
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            XMLDIR                         /u01/app/oracle/product/11204/db/rdbms/xml
SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11204/db/ccr/hosts/ggs2/state
SYS                            DATA_PUMP_DIR                  /u01/app/oracle/admin/hankey/dpdump/
SYS                            ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/11204/db/ccr/state

SQL> 
 
 

       4.2>在源端,通过scp命令或者其他工具传送文件到目标端,此处使用scp命令

$ pwd
/u01/app/oracle/admin/orcl/dpdump
$ ls
condition.dmp  condition.log  export.log  full.dmp  full.log  hr.dmp  hr.log  tab.dmp  tablespace.dmp  tablespace.log  tab.log
$ scp * 10.10.10.10:/u01/app/oracle/admin/hankey/dpdump/
The authenticity of host '10.10.10.10 (10.10.10.10)' can't be established.
RSA key fingerprint is 42:41:f4:75:62:6d:17:2c:e8:79:c1:05:97:82:41:7b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.10.10' (RSA) to the list of known hosts.
oracle@10.10.10.10's password: 
condition.dmp                                                                                                      100%  176KB 176.0KB/s   00:00    
condition.log                                                                                                      100% 1613     1.6KB/s   00:00    
export.log                                                                                                         100% 2056     2.0KB/s   00:00    
full.dmp                                                                                                           100%  151MB  21.6MB/s   00:07    
full.log                                                                                                           100%  105KB 104.5KB/s   00:00    
hr.dmp                                                                                                             100%  488KB 488.0KB/s   00:00    
hr.log                                                                                                             100% 2325     2.3KB/s   00:00    
tab.dmp                                                                                                            100%  224KB 224.0KB/s   00:00    
tablespace.dmp                                                                                                     100%  404KB 404.0KB/s   00:00    
tablespace.log                                                                                                     100% 2079     2.0KB/s   00:00    
tab.log                                                                                                            100% 1658     1.6KB/s   00:00    
$ 

目标端(实例名为hankey)

5、进入目标端的数据泵目录,切换用户到root,查看文件并修改权限和目标端数据库一致

$ su - root
Password: 
# cd /u01/app/oracle/admin/hankey/dpdump
# chown oracle:oinstall *
# ls -la
total 156580
drwxr-x---. 2 oracle oinstall      4096 Jul  4 01:00 .
drwxr-x---. 5 oracle oinstall      4096 Jun 19 20:26 ..
-rw-r-----. 1 oracle oinstall    180224 Jul  4 01:00 condition.dmp
-rw-r--r--. 1 oracle oinstall      1613 Jul  4 01:00 condition.log
-rw-r-----. 1 oracle oinstall       116 Jun 19 20:39 dp.log
-rw-r--r--. 1 oracle oinstall      2056 Jul  4 01:00 export.log
-rw-r-----. 1 oracle oinstall 158691328 Jul  4 01:00 full.dmp
-rw-r--r--. 1 oracle oinstall    107013 Jul  4 01:00 full.log
-rw-r-----. 1 oracle oinstall    499712 Jul  4 01:00 hr.dmp
-rw-r--r--. 1 oracle oinstall      2325 Jul  4 01:00 hr.log
-rw-r-----. 1 oracle oinstall    229376 Jul  4 01:00 tab.dmp
-rw-r-----. 1 oracle oinstall    413696 Jul  4 01:00 tablespace.dmp
-rw-r--r--. 1 oracle oinstall      2079 Jul  4 01:00 tablespace.log
-rw-r--r--. 1 oracle oinstall      1658 Jul  4 01:00 tab.log
# 
 
  

6、查看需要目标数据库存在的表空间

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
 
  

参考步骤3.1对比源数据库和目标数据库表空间差异,发现,目标端无EXAMPLE,所以在目标端创建EXAMPL 表空间 ,

参考源数据库估算,这里初始大小设置为300M,允许扩展数据文件,在需要扩展时,则分配给数据文件的下一个磁盘空间量的大小为50M

SQL> create tablespace EXAMPLE 
  2  datafile '/oradata/hankey/example01.dbf' size 300M
  3  autoextend on next 50M;

Tablespace created.

SQL>
 
 

附:若创建不合适,需要删除表空间的话,执行以下语句,然后重新创建即可。

SQL> drop tablespace example including contents and datafiles cascade constraints; 

7、数据泵导入

       7.1>导入到具体用户

7.1.1>按用户导入,不改变schema

             对应步骤3.1按用户导出的文件 

首先创建需要导入的用户hr

SQL> create user hr identified by hr;

User created.

SQL> grant connect,resource to hr;

Grant succeeded.
 执行导入命令 
  

$ impdp  \'/ as sysdba\'  directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr;

结果如下:

Import: Release 11.2.0.4.0 - Production on Mon Jul 4 21:00:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . imported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . imported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . imported "HR"."JOBS"                                 6.992 KB      19 rows
. . imported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . imported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . imported "HR"."REGIONS"                              5.476 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT REFERENCES ON "HR"."COUNTRIES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."COUNTRIES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."JOB_HISTORY" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT REFERENCES ON "HR"."EMPLOYEES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."EMPLOYEES" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."JOBS" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."LOCATIONS" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT REFERENCES ON "HR"."LOCATIONS" TO "OE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'OE' does not exist
Failing sql is:
GRANT SELECT ON "HR"."DEPARTMENTS" TO "OE"
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
ORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 15 error(s) at Mon Jul 4 21:00:55 2016 elapsed 0 00:00:07

发现有报错,从报错信息来看,是因为赋权限时找不到名为OE的用户,实际此时hr用户的数据已经完全导入,若就希望在执行时候不报错误,那么继续往下走

通过语句查看目标库确实无OE用户

select username from dba_users;

所以,创建oe用户:

SQL> create user oe identified by oe;

User created.

SQL> grant connect,resource to oe;


Grant succeeded.

SQL>
因上述导入报错,但实际hr用户数据已经导入,若要重新完全导入,首先删除hr用户。

SQL> drop user hr cascade;
重新创建用户

SQL> create user hr identified by hr;

User created.

SQL> grant connect,resource to hr;

Grant succeeded.

SQL> 
重新执行导入语句:

$ impdp  \'/ as sysdba\'  directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr;
结果如下:

Import: Release 11.2.0.4.0 - Production on Mon Jul 4 21:43:25 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imhr.log SCHEMAS=hr 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . imported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . imported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . imported "HR"."JOBS"                                 6.992 KB      19 rows
. . imported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . imported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . imported "HR"."REGIONS"                              5.476 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Jul 4 21:43:33 2016 elapsed 0 00:00:06
导入成功后,可自行连接hr用户验证核对源库和目标库的数据。

7.1.2>按用户导入,改变schema

首先,目标端新建用户,如下,创建名为buzhengjing的用户

SQL> create user buzhengjing identified by buzhengjing;

User created.
SQL> grant connect,resource to buzhengjing;

Grant succeeded.

SQL> 
执行导入语句,注意REMAP_SCHEMA=hr:buzhengjing,参数中冒号的前半部分为源数据库的SCHEMA,后半部分为目标数据库要导入的SCHEMA,即新建的用户

$ impdp  \'/ as sysdba\'  directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imbzj.log REMAP_SCHEMA=hr:buzhengjing
执行结果如下

Import: Release 11.2.0.4.0 - Production on Tue Jul 5 22:00:02 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=hr.dmp logfile=imbzj.log REMAP_SCHEMA=hr:buzhengjing 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "BUZHENGJING"."COUNTRIES"                   6.367 KB      25 rows
. . imported "BUZHENGJING"."DEPARTMENTS"                 7.007 KB      27 rows
. . imported "BUZHENGJING"."EMPLOYEES"                   16.80 KB     107 rows
. . imported "BUZHENGJING"."JOBS"                        6.992 KB      19 rows
. . imported "BUZHENGJING"."JOB_HISTORY"                 7.054 KB      10 rows
. . imported "BUZHENGJING"."LOCATIONS"                   8.273 KB      23 rows
. . imported "BUZHENGJING"."REGIONS"                     5.476 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 5 22:02:08 2016 elapsed 0 00:00:06

        7.2>导入表

              用到步骤3.2按表导出的数据文件

              7.2.1>导入表,不改变schema

导入表到同名用户下,由于上一步骤已导入数据,所以此处删除hr用户并重新创建。

SQL> drop user hr cascade;
重新创建用户

SQL> create user hr identified by hr;

User created.

SQL> grant connect,resource to hr;

Grant succeeded.

SQL> 

执行导入命令

$ impdp  \'/ as sysdba\'  directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log
执行结果:
Import: Release 11.2.0.4.0 - Production on Mon Jul 4 22:16:03 2016


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . imported "HR"."EMPLOYEES"                            16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 6 error(s) at Mon Jul 4 22:16:07 2016 elapsed 0 00:00:03
从报错信息看,是导入完数据后,创建外键约束和触发器失败。当然,数据已经完全导入。

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPARTMENTS
EMPLOYEES

SQL> select count(*) from DEPARTMENTS;

  COUNT(*)
----------
        27

SQL> select count(*) from EMPLOYEES;

  COUNT(*)
----------
       107

SQL> 

此错误可以忽略,但对于强迫症来说,哦,不,对于一个处事风格严谨的人来说,不容许有一个错误出现,才叫完美,那么,接着走。

毫无疑问,上一步骤已导入数据,所以继续删除hr用户并重新创建。

SQL> drop user hr cascade;

重新创建用户

SQL> create user hr identified by hr;

User created.

SQL> grant connect,resource to hr;

Grant succeeded.

SQL> 

若要不报错,可以在导出命令中加一个参数exclude,过滤掉constraint(约束)、TRIGGER(触发器)、grant(赋权限)

注意,因为导出的单个表,难免会存在和其他未导出的表存在关联的情况,而这种做法并不是将错误无视并跳过,因为这些对数据导入完整性没有影响,都是在导入完成后进行的。我们可以避免在导出过程中报错,然后根据需要手动建立constraint(约束)、TRIGGER(触发器)和grant(赋权限)等操作,而多数情况下,对于单个表,后续并不需要操作这些的。

执行导入命令

$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log tables=hr.employees,hr.departments exclude=constraint,trigger,grant
Import: Release 11.2.0.4.0 - Production on Tue Jul 5 00:36:29 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tab.dmp tables=hr.employees,hr.departments exclude=constraint,trigger,grant 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . imported "HR"."EMPLOYEES"                            16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jul 5 00:36:33 2016 elapsed 0 00:00:03
7.2.2>导入表,改变schema

创建一个新的正经的用户zhengjing

SQL> create user zhengjing identified by zhengjing;


User created.

SQL> grant connect,resource to zhengjing;


Grant succeeded.

SQL> 
执行导入命令,注意用参数REMAP_SCHEMA改变schema,参数exclude的使用同上一步骤

$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tab.dmp logfile=imtab.log REMAP_SCHEMA=hr:zhengjing exclude=constraint,trigger,grant
Import: Release 11.2.0.4.0 - Production on Tue Jul 5 01:00:13 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tab.dmp REMAP_SCHEMA=hr:zhengjing exclude=constraint,trigger,grant 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZHENGJING"."DEPARTMENTS"                   7.007 KB      27 rows
. . imported "ZHENGJING"."EMPLOYEES"                     16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 5 01:00:17 2016 elapsed 0 00:00:03

7.3>导入表空间

对应步骤3.4的按表空间导出文件

首先在目标端查看有哪些表在使用example表空间以及其所属用户

SQL> set lines 200
SQL> select dt.owner, dt.table_name, dt.tablespace_name, dt.status
  2    from dba_tables dt
  3   where lower(dt.tablespace_name) = 'example';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
BUZHENGJING                    DEPARTMENTS                    EXAMPLE                        VALID
BUZHENGJING                    REGIONS                        EXAMPLE                        VALID
BUZHENGJING                    LOCATIONS                      EXAMPLE                        VALID
BUZHENGJING                    JOBS                           EXAMPLE                        VALID
BUZHENGJING                    EMPLOYEES                      EXAMPLE                        VALID
BUZHENGJING                    JOB_HISTORY                    EXAMPLE                        VALID
HR                             EMPLOYEES                      EXAMPLE                        VALID
HR                             DEPARTMENTS                    EXAMPLE                        VALID
HR                             JOB_HISTORY                    EXAMPLE                        VALID
HR                             JOBS                           EXAMPLE                        VALID
HR                             LOCATIONS                      EXAMPLE                        VALID

OWNER                          TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
HR                             REGIONS                        EXAMPLE                        VALID
ZHENGJING                      EMPLOYEES                      EXAMPLE                        VALID
ZHENGJING                      DEPARTMENTS                    EXAMPLE                        VALID

14 rows selected.

SQL> 
因之前演示中创建了许多用户,为避免上述导入的数据干扰产生嫌疑,此处删除这几个用户,当然用户下对应的表也将跟着一起被干掉。

SQL> drop user BUZHENGJING cascade;
drop user HR cascade;
drop user ZHENGJING cascade;

User dropped.

SQL> 
User dropped.

SQL> 
User dropped.
源库的example下存在hr用户,因而,目标库对应的hr用户也要存在。

SQL> create user hr identified by hr;

User created.

SQL> grant connect,resource to hr;

Grant succeeded.
执行导入命令

$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example
执行结果如下

Import: Release 11.2.0.4.0 - Production on Tue Jul 5 01:36:23 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . imported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . imported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . imported "HR"."JOBS"                                 6.992 KB      19 rows
. . imported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . imported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . imported "HR"."REGIONS"                              5.476 KB       4 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_TABLESPACE_01" completed with 4 error(s) at Tue Jul 5 01:36:28 2016 elapsed 0 00:00:04
查看报错信息,得知是由触发器的引起的,需要加上参数exclude=TRIGGER

在执行导入语句之前,清理并重建用户

SQL> drop user hr cascade;

User dropped.

SQL> create user hr identified by hr;

User created.

SQL> grant connect,resource to hr;
Grant succeeded.
SQL>

 执行导入语句 
  
$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example exclude=TRIGGER
执行结果如下

Import: Release 11.2.0.4.0 - Production on Tue Jul 5 01:44:30 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLESPACE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tablespace.dmp logfile=tsb.log TABLESPACES=example exclude=TRIGGER 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . imported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . imported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . imported "HR"."JOBS"                                 6.992 KB      19 rows
. . imported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . imported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . imported "HR"."REGIONS"                              5.476 KB       4 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
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 "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at Tue Jul 5 01:44:34 2016 elapsed 0 00:00:04