Oracle database 14章 使用数据泵导出和导入 导出 理论试验
来源：互联网 发布：java 根据域名获取ip 编辑：程序博客网 时间：2024/06/06 18:22
1、先要指明目录对象，并赋予读写的权限
~> expdp hr/hrExport: Release 11.2.0.1.0 - Production on Thu Apr 4 14:07:22 2013Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-39002: invalid operationORA-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.dmpExport: Release 11.2.0.1.0 - Production on Thu Apr 4 14:16:41 2013Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 1.25 MBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing 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 rowsMaster 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.dmpJob "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:17:02
~/app/oracle/admin/test/dpdump> ll -htotal 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=HRDIRECTORY=data_pump_dirDUMPFILE=SPECIAL_SERVICE_DATA.dmpSCHEMAS=HR
expdp PARFILE=service_data.txt
Export: Release 11.2.0.1.0 - Production on Thu Apr 4 14:25:26 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: hrPassword: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** PARFILE=service_data.txt Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.25 MBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing 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 rowsMaster 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.dmpJob "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:25:52oracle@linux-tpch:~/app/oracle/admin/test/dpdump> lsdp.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=yExport: Release 11.2.0.1.0 - Production on Thu Apr 4 14:56:32 2013Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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 KBTotal estimation using BLOCKS method: 1.25 MBJob "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=YExport: Release 11.2.0.1.0 - Production on Thu Apr 4 15:09:28 2013Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal 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 rowsProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSMaster 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.dmpJob "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:09:36
~/app/oracle/admin/test/dpdump> ll -htotal 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
- Oracle database 14章 使用数据泵导出和导入 导出 理论试验
- Oracle database 14章 使用数据泵导出和导入 导入 理论试验
- Oracle database 14章 使用数据泵导出和导入 监控数据泵作业 理论试验
- oracle 数据导入导出 和数据泵导入导出
- [database] oracle 导入/导出
- Oracle Database 导入导出
- oracle 传统导出导入和数据泵导出导入
- oracle使用数据泵导入导出数据
- Oracle database 使用外部表装载数据 理论试验
- 使用数据泵导入导出oracle数据库
- Oracle使用数据泵导出导入表
- oracle数据泵的导入和导出
- oracle 数据导入和导出
- oracle 数据导入和导出
- oracle 数据导出和导入
- oracle 数据泵导入导出
- oracle数据泵导入导出
- 数据泵导入导出--oracle
- Python 中 异常UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3:处理
- IBUS Install
- linux-2.6.32在mini2440开发板上移植（19）之yaffs2文件系统移植
- 设计模式之行为型模式
- IsapiModule或CgiModule必须在模块列表中
- Oracle database 14章 使用数据泵导出和导入 导出 理论试验
- iis上配置运行python(win7-iis7.5)
- VC6.0编写的MFC项目移植到VS2010中编译遇到的几个问题
- XNA4.0学习笔记2：控制精灵和碰撞检测
- PHP：PHPUnit安装
- jUnit 单元测试
- 检测毒药
- 类似约瑟夫环
- 第五次作业
