数据泵导入导出案例

connectedtoyou

数据泵导入导出案例(expdp & impdp)

案例环境：
rhel-server-6.5-x86_64
oracle 11.2.0.4.0
源数据库：
1、首先查看数据泵目录
<pre name="code" class="sql"><pre name="code" class="sql"><pre name="code" class="sql">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，命令如下：
<pre name="code" class="sql">SQL> create directory dump_test as'/dump/test';

Directory created.
2、赋权限（数据泵导入导出时的执行用户 对上面选用的数据泵目录要有读写权限）
<pre name="code" class="sql">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" <strong>successfully completed</strong> at Sun Jul 3 21:39:19 2016 elapsed 0 00:01:09
      3.2>按表导出
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.3>按查询条件导
<pre name="code" class="sql">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" <strong>successfully completed</strong> 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" <strong>successfully completed </strong>at Sun Jul 3 22:15:04 2016 elapsed 0 00:00:36

      3.5>全库导出
按全库导出时，若执行用户仍然使用hr，那么需要给hr赋予另外两个权限
第一个是EXP_FULL_DATABASE权限，否则，在执行全库导出命令时会报如下错误：
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges
所以先给hr赋EXP_FULL_DATABASE权限
SQL> grant EXP_FULL_DATABASE to hr;

Grant succeeded.
第二个是EXEMPT ACCESS POLICY权限，否则在执行全库导出命令时会报如下错误，此错误可通过查看导出日志得到：
ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHAS
进一步根据报错代码得到如下信心：
39181, 0000, "Only partial table data may be exported due to fine grain access control on %s"
// *Cause:  An unprivileged user has tried to export a table that has fine
//          grain access control. The table owner is subjected to access
//          control and may not be able to export all rows in the table.
//          Only the rows that can be seen by that user will be exported. In
//          order to preserve integrity of the table, the user importing the
//          table should have enough privilege to recreate the table with the
//          security policies at import time.
// *Action: It is strongly recommended that the database administrator handle
//          exporting of this table.

大概意思就是：
由于细粒度访问控制存在，所以只有部分表数据可能被导出
原因:非特权用户试图导出一个具有细粒度的访问控制的表。表的所有者受到访问控制和可能无法导出表中的所有行。
用户只能导出能访问到的行。为了保持表的完整性,用户导入（此处应该写为：导入或者导出）表应该有足够的特权并通过安全策略重新创建表。

哈哈哈，最后，oracle官方强烈建议通过拥有dba权限的用户导出表。

给hr赋EXEMPT ACCESS POLICY权限
SQL> grant EXEMPT ACCESS POLICY to hr;

Grant succeeded.

当然了，直接可以直接通过as sysdba导出：
expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=full.dmp logfile=full.log parallel=2 full=y
此处，为了顺便更深入地理解权限问题，所以下面仍以hr用户为例：
expdp hr/hr directory=DATA_PUMP_DIR dumpfile=full.dmp logfile=full.log parallel=2 full=y

结果如下（太长，刷屏了，仅贴最后信息）：
Master table "HR"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_FULL_01 is:
  /u01/app/oracle/admin/orcl/dpdump/full.dmp
Job "HR"."SYS_EXPORT_FULL_01" <strong>successfully completed </strong>at Sun Jul 3 23:15:50 2016 elapsed 0 00:06:01