Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)
来源:互联网 发布:华为ar1200s mac绑定 编辑:程序博客网 时间:2024/05/17 04:01
Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)
Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)
目的:指导项目侧自行进行简单的数据泵迁移工作。
本文实验环境:Oracle 11.2.0.4,利用数据库自带的scott示例用户进行试验测试。
1.首先需要创建Directory
2.使用expdp导出用户数据
- 2.1 只导出scott用户的元数据,且不包含统计信息;
- 2.2 只导出scott用户的数据;
- 2.3 只导出scott用户下的emp,dept表及数据;
- 2.4 只导出scott用户下的emp,dept表结构;
- 2.5 导出scott用户下所有的内容;
- 2.6 并行导出scott用户下所有的内容;
3.查询当前用户用到的表空间
1. 首先需要创建Directory
这里目录名字定义为"jy",
若是windows平台,对应系统目录为"E:\jingyu";
create or replace directory jy as 'E:\jingyu';
若是Unix/Linux平台,对应系统目录为"/tmp/jingyu".
create or replace directory jy as '/tmp/jingyu';
注意:目录在系统上需要真实存在(mkdir -p /tmp/jingyu),且有访问的权限。
drwxr-xr-x. 2 oracle oinstall 4.0K May 22 16:48 jingyu
2. 使用expdp导出用户数据
2.1 只导出scott用户的元数据,且不包含统计信息;
expdp system directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log
$ expdp system directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.logExport: Release 11.2.0.4.0 - Production on Fri May 22 16:57:59 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing 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/CONSTRAINTMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_meta.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:13 2015 elapsed 0 00:00:11
2.2 只导出scott用户的数据;
expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log
$ expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.logExport: Release 11.2.0.4.0 - Production on Fri May 22 16:58:47 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KB. . exported "SCOTT"."DEPT" 5.929 KB 4 rows. . exported "SCOTT"."EMP" 8.484 KB 12 rows. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_data.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:57 2015 elapsed 0 00:00:07
2.3 只导出scott用户下的emp,dept表及数据;
这里如果用scott用户导出,需要注意scott用户对于directory的权限问题:需要dba用户赋予scott用户read,write目录的权限。
即:grant read, write on directory jy to scott;
SQL> grant read, write on directory jy to scott;Grant succeeded.
expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log
$ expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.logExport: Release 11.2.0.4.0 - Production on Fri May 22 17:13:55 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 128 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."DEPT" 5.929 KB 4 rows. . exported "SCOTT"."EMP" 8.484 KB 12 rowsMaster table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/jingyu/scott_emp_dept.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 22 17:14:04 2015 elapsed 0 00:00:06
2.4 只导出scott用户下的emp,dept表结构;
expdp scott directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log
$ expdp scott directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.logExport: Release 11.2.0.4.0 - Production on Fri May 22 17:14:51 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log Processing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSMaster table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/jingyu/scott_emp_dept_meta.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 22 17:15:01 2015 elapsed 0 00:00:07
2.5 导出scott用户下所有的内容;
expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log
$ expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.logExport: Release 11.2.0.4.0 - Production on Fri May 22 17:15:52 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing 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/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."DEPT" 5.929 KB 4 rows. . exported "SCOTT"."EMP" 8.484 KB 12 rows. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_all.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 17:16:06 2015 elapsed 0 00:00:11
2.6 并行导出scott用户下所有的内容;
expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2
$ expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2Export: Release 11.2.0.4.0 - Production on Fri May 22 16:55:13 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLE. . exported "SCOTT"."DEPT" 5.929 KB 4 rows. . exported "SCOTT"."EMP" 8.484 KB 12 rows. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing 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/STATISTICS/TABLE_STATISTICSMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_all01.dmp /tmp/jingyu/scott_all02.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:56:12 2015 elapsed 0 00:00:54
3. 查询当前用户用到的表空间
select tablespace_name from user_tables unionselect tablespace_name from user_tab_partitions unionselect tablespace_name from user_indexes unionselect tablespace_name from user_ind_partitions;
- Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)
- Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)
- Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(下)
- 【EXPDP/IMPDP】ORACLE数据泵导入导出案例(expdp & impdp)
- oracle expdp/impdp 数据泵导入导出命令
- oracle 数据泵导入导出(impdp/expdp)
- Oracle expdp/impdp导出导入命令
- Oracle数据导入导出imp/exp命令 10g以上expdp/impdp命令 详细的
- Oracle数据泵导入导出 Oracle impdp/expdp
- oracle 导入导出数据 expdp、impdp
- oracle 数据导入导出 之expdp impdp
- ORACLE impdp / expdp 导入导出数据
- Oracle 数据导入/导出 IMPDP/EXPDP
- ORACLE数据泵导入导出案例(expdp & impdp)
- 使用Oracle数据泵EXPDP/IMPDP导入导出
- Oracle 数据泵(IMPDP/EXPDP)导入导出总结
- ORACLE--导入导出/数据泵(imp/exp impdp/expdp)
- 使用数据泵expdp命令和impdp命令对数据库的数据进行导入导出
- 第十二周项目三 日期时间类
- 5-6
- iOS浅谈如何进行网络判断
- 装饰者模式
- Python模块常用的几种安装方式
- Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)
- 【浅入浅出】后台代码完结篇
- 希尔排序
- c#连接Oracle库整理
- [MultiMedia][实验5(前景去除)教程]
- iOS 正则表达式判断邮箱格式,手机号码
- 文件与内存的桥梁:页缓存
- 欢迎使用CSDN-markdown编辑器
- Web分页工具类