1、先要指明目录对象,并赋予读写的权限
~> expdp hr/hr
Export: Release 11.2.0.1.0 - Production on Thu Apr 4 14:07:22 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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-39145: directory object parameter must be specified and non-null
指明目录对象,赋予权限:
SQL> grant read,write on DIRECTORY data_pump_dir to hr;
Grant succeeded.
再次导出:
~/app/oracle/admin/test/dpdump> expdp hr/hr directory=data_pump_dir dumpfile=hr.dmp
Export: Release 11.2.0.1.0 - Production on Thu Apr 4 14:16:41 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.25 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
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/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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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.375 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.81 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"."NEW_COUNTRIES" 6.289 KB 25 rows
. . exported "HR"."NEW_COUNTRIES1" 5.976 KB 8 rows
. . exported "HR"."NEW_COUNTRIES2" 5.187 KB 25 rows
. . exported "HR"."NEW_COUNTRIES3" 5.976 KB 8 rows
. . exported "HR"."NEW_EMPLOYEES" 16.82 KB 107 rows
. . exported "HR"."NEW_EMPLOYEES1" 6.039 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES2" 6.625 KB 51 rows
. . exported "HR"."NEW_EMPLOYEES3" 6.968 KB 9 rows
. . exported "HR"."NEW_EMPLOYEES4" 6.882 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES4_YEARLY_PAYMENT" 6.812 KB 3 rows
. . exported "HR"."NEW_EMPLOYEES5" 6.890 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES6" 6.796 KB 3 rows
. . exported "HR"."REGIONS" 5.484 KB 4 rows
. . exported "HR"."SERVICE_DATA" 6.625 KB 14 rows
. . exported "HR"."YEARLY_SPECIAL_SERVICE_COST" 0 KB 0 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/oracle/admin/test/dpdump/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:17:02
~/app/oracle/admin/test/dpdump> ll -h
total 2.2M
-rw-r----- 1 oracle oinstall 116 Mar 31 14:58 dp.log
-rw-r----- 1 oracle oinstall 1.6M Apr 4 14:07 expdat.dmp
-rw-r--r-- 1 oracle oinstall 3.4K Apr 4 14:17 export.log
-rw-r----- 1 oracle oinstall 600K Apr 4 14:17 hr.dmp
2、使用参数文件进行数据泵导出:
~/app/oracle/admin/test/dpdump> vi service_data.txt
SCHEMAS=HR
DIRECTORY=data_pump_dir
DUMPFILE=SPECIAL_SERVICE_DATA.dmp
SCHEMAS=HR
expdp PARFILE=service_data.txt
Export: Release 11.2.0.1.0 - Production on Thu Apr 4 14:25:26 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: hr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** PARFILE=service_data.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.25 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
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/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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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.375 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.81 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"."NEW_COUNTRIES" 6.289 KB 25 rows
. . exported "HR"."NEW_COUNTRIES1" 5.976 KB 8 rows
. . exported "HR"."NEW_COUNTRIES2" 5.187 KB 25 rows
. . exported "HR"."NEW_COUNTRIES3" 5.976 KB 8 rows
. . exported "HR"."NEW_EMPLOYEES" 16.82 KB 107 rows
. . exported "HR"."NEW_EMPLOYEES1" 6.039 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES2" 6.625 KB 51 rows
. . exported "HR"."NEW_EMPLOYEES3" 6.968 KB 9 rows
. . exported "HR"."NEW_EMPLOYEES4" 6.882 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES4_YEARLY_PAYMENT" 6.812 KB 3 rows
. . exported "HR"."NEW_EMPLOYEES5" 6.890 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES6" 6.796 KB 3 rows
. . exported "HR"."REGIONS" 5.484 KB 4 rows
. . exported "HR"."SERVICE_DATA" 6.625 KB 14 rows
. . exported "HR"."YEARLY_SPECIAL_SERVICE_COST" 0 KB 0 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/oracle/admin/test/dpdump/SPECIAL_SERVICE_DATA.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:25:52
oracle@linux-tpch:~/app/oracle/admin/test/dpdump> ls
dp.log expdat.dmp export.log hr.dmp service_data.txt SPECIAL_SERVICE_DATA.dmp
3、数据泵导出参数:
a、与文件和目录有关的参数
reuse_dumpfiles: 覆盖已经存在的dumpfile
expdp hr/hr PARFILE=service_data.txt reuse_dumpfiles=y
dumpfile exp%U.dmp 创建多个转储文件
expdp hr/hr directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA%U.dmp
此外还有parfile 、filesize、compression
b、与导出方式相关的参数:
full、schemas、tables、tablespace、transport_tablespaces、transport_full_check
c、估算参数:
estimate、estimate_only : 估算导出作业中将消耗多少物理空间,estimate有两个选项 blocks和statistics两个选项
expdp hr/hr directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA.dmp REUSE_DUMPFILES=y ESTIMATE=BLOCKS
在output中可以看到:
Total estimation using BLOCKS method: 1.25 MB
expdp hr/hr directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA.dmp REUSE_DUMPFILES=y ESTIMATE=
STATISTICS
在output中可以看到:
Total estimation using STATISTICS method: 147.4 KB
~/app/oracle/admin/test/dpdump> expdp hr/hr directory=data_pump_dir ESTIMATE_ONLY=y
Export: Release 11.2.0.1.0 - Production on Thu Apr 4 14:56:32 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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 ESTIMATE_ONLY=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "HR"."COUNTRIES" 64 KB
. estimated "HR"."DEPARTMENTS" 64 KB
. estimated "HR"."EMPLOYEES" 64 KB
. estimated "HR"."JOBS" 64 KB
. estimated "HR"."JOB_HISTORY" 64 KB
. estimated "HR"."LOCATIONS" 64 KB
. estimated "HR"."NEW_COUNTRIES" 64 KB
. estimated "HR"."NEW_COUNTRIES1" 64 KB
. estimated "HR"."NEW_COUNTRIES2" 64 KB
. estimated "HR"."NEW_COUNTRIES3" 64 KB
. estimated "HR"."NEW_EMPLOYEES" 64 KB
. estimated "HR"."NEW_EMPLOYEES1" 64 KB
. estimated "HR"."NEW_EMPLOYEES2" 64 KB
. estimated "HR"."NEW_EMPLOYEES3" 64 KB
. estimated "HR"."NEW_EMPLOYEES4" 64 KB
. estimated "HR"."NEW_EMPLOYEES4_YEARLY_PAYMENT" 64 KB
. estimated "HR"."NEW_EMPLOYEES5" 64 KB
. estimated "HR"."NEW_EMPLOYEES6" 64 KB
. estimated "HR"."REGIONS" 64 KB
. estimated "HR"."SERVICE_DATA" 64 KB
. estimated "HR"."YEARLY_SPECIAL_SERVICE_COST" 0 KB
Total estimation using BLOCKS method: 1.25 MB
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:56:34
d、与作业有关的参数:
PARALLEL:几个线程导出操作写相同数量的转储文件 与%U同时用
~/app/oracle/admin/test/dpdump> expdp hr/hr directory=data_pump_dir dumpfile=SPECIAL_SERVICE_DATA%U.dmp PARALLEL=2 REUSE_DUMPFILES=Y
Export: Release 11.2.0.1.0 - Production on Thu Apr 4 15:09:28 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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=SPECIAL_SERVICE_DATA%U.dmp PARALLEL=2 REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.25 MB
. . exported "HR"."COUNTRIES" 6.375 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.81 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"."NEW_COUNTRIES" 6.289 KB 25 rows
. . exported "HR"."NEW_COUNTRIES1" 5.976 KB 8 rows
. . exported "HR"."NEW_COUNTRIES2" 5.187 KB 25 rows
. . exported "HR"."NEW_COUNTRIES3" 5.976 KB 8 rows
. . exported "HR"."NEW_EMPLOYEES" 16.82 KB 107 rows
. . exported "HR"."NEW_EMPLOYEES1" 6.039 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES2" 6.625 KB 51 rows
. . exported "HR"."NEW_EMPLOYEES3" 6.968 KB 9 rows
. . exported "HR"."NEW_EMPLOYEES4" 6.882 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES4_YEARLY_PAYMENT" 6.812 KB 3 rows
. . exported "HR"."NEW_EMPLOYEES5" 6.890 KB 6 rows
. . exported "HR"."NEW_EMPLOYEES6" 6.796 KB 3 rows
. . exported "HR"."REGIONS" 5.484 KB 4 rows
. . exported "HR"."SERVICE_DATA" 6.625 KB 14 rows
. . exported "HR"."YEARLY_SPECIAL_SERVICE_COST" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
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/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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/oracle/admin/test/dpdump/SPECIAL_SERVICE_DATA01.dmp
/home/oracle/app/oracle/admin/test/dpdump/SPECIAL_SERVICE_DATA02.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:09:36
~/app/oracle/admin/test/dpdump> ll -h
total 3.3M
-rw-r----- 1 oracle oinstall 116 Mar 31 14:58 dp.log
-rw-r----- 1 oracle oinstall 1.6M Apr 4 14:07 expdat.dmp
-rw-r--r-- 1 oracle oinstall 3.5K Apr 4 15:09 export.log
-rw-r----- 1 oracle oinstall 600K Apr 4 14:17 hr.dmp
-rw-r--r-- 1 oracle oinstall 80 Apr 4 14:29 service_data.txt
-rw-r----- 1 oracle oinstall 488K Apr 4 15:09 SPECIAL_SERVICE_DATA01.dmp
-rw-r----- 1 oracle oinstall 116K Apr 4 15:09 SPECIAL_SERVICE_DATA02.dmp
-rw-r----- 1 oracle oinstall 600K Apr 4 14:53 SPECIAL_SERVICE_DATA.dmp