使用数据泵工具expdp工具导出数据

本文中讲述使用数据泵expdp工具导出一个表中部分的数据，导出一个表，导出一个模式，
导出一个表空间，还有导出整个数据库的操作过程。

---创建导出导入数据的目录对象:
--创建目录对象：
SQL> create or replace directory dump_home as '/home/oracle/dirhome';
Directory created.

--授权：
SQL> grant read,write on directory dump_home to scott;
Grant succeeded.

--创建测试表空间：
SQL> create tablespace myspace datafile
  2  '/u01/app/oracle/oradata/PROD/myspace01.dbf' size 10M
  3  autoextend on next 2M maxsize 30M;
Tablespace created.

SQL> !ls /u01/app/oracle/oradata/PROD/myspace01.dbf
/u01/app/oracle/oradata/PROD/myspace01.dbf
#已创建成功。

---利用数据泵导出数据：
--用expdp导出某个emp表中deptno=30的数据：
--导出策略：expdp  scott/tiger  directory=dump_home dumpfile=emp30.dmp tables=emp query="'where deptno=30'";

[oracle@enmo dirhome]$ expdp  scott/tiger  directory=dump_home dumpfile=emp30.dmp tables=emp query="'where deptno=30'";
Export: Release 11.2.0.4.0 - Production on Tue Nov 1 23:52:32 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 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_home dumpfile=emp30.dmp tables=emp query='where deptno=30' 
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/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
. . exported "SCOTT"."EMP"                                8.25 KB       6 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dirhome/emp30.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 1 23:52:55 2016 elapsed 0 00:00:18

[oracle@enmo dirhome]$ 
#导出成功。
--查看导出文件：
[oracle@enmo dirhome]$ ll
total 176
-rw-r--r-- 1 oracle oinstall     80 Nov  1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall     95 Nov  1 22:39 dt2.csv
-rw-r--r-- 1 oracle oinstall     64 Nov  1 23:18 dt3.csv
-rw-r----- 1 oracle oinstall 139264 Nov  1 23:52 emp30.dmp
... ...

--导出suxing用户下的表mytest:
--导出策略：expdp suxing/oracle  directory=dump_home dumpfile=mytest.dmp tables=mytest

[oracle@enmo dirhome]$ expdp suxing/oracle  directory=dump_home dumpfile=mytest.dmp tables=mytest
Export: Release 11.2.0.4.0 - Production on Tue Nov 1 23:59:34 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
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_HOME is invalid
#遇到这种状况说suxing用户没有使用目录对象的权限，则需要授权。

--授权给suxing用户使用目录对象：
SQL> grant read,write on directory dump_home to suxing;
Grant succeeded.
#继续导出表操作。

[oracle@enmo dirhome]$ expdp suxing/oracle  directory=dump_home dumpfile=mytest.dmp tables=mytest
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:02: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
Starting "SUXING"."SYS_EXPORT_TABLE_01":  suxing/******** directory=dump_home dumpfile=mytest.dmp tables=mytest 
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
. . exported "SUXING"."MYTEST"                           5.859 KB       1 rows
Master table "SUXING"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SUXING.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dirhome/mytest.dmp
Job "SUXING"."SYS_EXPORT_TABLE_01" successfully completed at Wed Nov 2 00:02:35 2016 elapsed 0 00:00:05

[oracle@enmo dirhome]$ 
#导出成功。
--查看导出文件：
[oracle@enmo dirhome]$ ll
total 276
... ...
-rw-r--r-- 1 oracle oinstall   1852 Nov  1 22:54 load.log
-rw-r----- 1 oracle oinstall  98304 Nov  2 00:02 mytest.dmp
[oracle@enmo dirhome]$ 

--导出Scott schema所有的对象：
--导出策略：expdp  scott/tiger  directory=dump_home dumpfile=scott.dmp schemas=scott
--或者策略：expdp  scott/tiger  directory=dump_home dumpfile=scott.dmp 与expdp  scott/tiger  directory=dump_home

[oracle@enmo dirhome]$ expdp  scott/tiger  directory=dump_home dumpfile=scott.dmp schemas=scott
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:10:20 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 "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dump_home dumpfile=scott.dmp schemas=scott 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
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/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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."HISLOADER"                         5.921 KB       3 rows
. . exported "SCOTT"."MYLOADER"                          6.023 KB       7 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dirhome/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 2 00:10:56 2016 elapsed 0 00:00:35

[oracle@enmo dirhome]$ 
#导出完成。
--查看导出文件：
[oracle@enmo dirhome]$ ll
total 560
... ...
-rw-r----- 1 oracle oinstall  98304 Nov  2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 286720 Nov  2 00:10 scott.dmp
[oracle@enmo dirhome]$ 

--导出MySpace表空间： （注意：导出表空间，则要在系统管理员角色的账户操作）
--导出策略：expdp system/oracle directory=dump_home  dumpfile=myspace.dmp logfile=myspace.log  tablespaces=myspace

[oracle@enmo dirhome]$ expdp system/oracle directory=dump_home  dumpfile=myspace.dmp logfile=myspace.log  tablespaces=myspace
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 00:21:20 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 "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** directory=dump_home dumpfile=myspace.dmp logfile=myspace.log tablespaces=myspace 
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
. . exported "SUXING"."MYTEST"                           5.859 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
  /home/oracle/dirhome/myspace.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Nov 2 00:21:26 2016 elapsed 0 00:00:05

[oracle@enmo dirhome]$ 
#导出成功。
--查看导出文件：
[oracle@enmo dirhome]$ ll
total 664
-rw-r--r-- 1 oracle oinstall     80 Nov  1 22:38 dt1.csv
... ...
-rw-r----- 1 oracle oinstall  98304 Nov  2 00:21 myspace.dmp
-rw-r--r-- 1 oracle oinstall   1093 Nov  2 00:21 myspace.log
-rw-r----- 1 oracle oinstall  98304 Nov  2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 286720 Nov  2 00:10 scott.dmp
[oracle@enmo dirhome]$ 

--导出PROD数据库全库：
--导出策略：expdp system/oracle directory=dump_home  dumpfile=PROD.dmp logfile=PROD.log  full=y

[oracle@enmo dirhome]$expdp system/oracle directory=dump_home  dumpfile=PROD.dmp logfile=PROD.log  full=y
... ...
... ...
. . exported "SYSTEM"."REPCAT$_SITES_NEW"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /home/oracle/dirhome/PROD.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Wed Nov 2 00:31:28 2016 elapsed 0 00:04:59

[oracle@enmo dirhome]$ 
#全库导出成功。
--查看导出文件：
[oracle@enmo dirhome]$ ll
total 163028
-rw-r--r-- 1 oracle oinstall        80 Nov  1 22:38 dt1.csv
-rw-r--r-- 1 oracle oinstall        95 Nov  1 22:39 dt2.csv
... ...
-rw-r----- 1 oracle oinstall     98304 Nov  2 00:02 mytest.dmp
-rw-r----- 1 oracle oinstall 165978112 Nov  2 00:31 PROD.dmp
-rw-r--r-- 1 oracle oinstall    107455 Nov  2 00:31 PROD.log
-rw-r----- 1 oracle oinstall    286720 Nov  2 00:10 scott.dmp
[oracle@enmo dirhome]$ 

从上面可以发现，当导出表空间与全库时候，只有具有DBA角色权限的账户才能做导出操作，
同时要导出一个log日志文件，当在导入的时候使用。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2127481/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2127481/