OracleDataPump导出和导入数据
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了OracleDataPump导出和导入数据，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5621字，纯文字阅读大概需要9分钟。
内容图文
Data pump export/import(hereinafter referred to as Export/Import for ease of reading)是一种将元数据和数据导出到系统文件集
阅读导航
Data pump export/import(hereinafter referred to as Export/Import for ease of reading)是一种将元数据和数据导出到系统文件集/从系统文件集导入数据的服务端工具;导出的文件可以移动到其它服务器上,但只能使用impdp导入;使用前要先建立目录(directory)和授权:
conn / as sysdba CREATE DIRECTORY PUMP_DIR AS '/u01/backup';创建后即可查询出目录信息,包括目录名称、所有者、目录位置:
SQL> column directory_path format a50 SQL>select * from dba_directories where directory_name='PUMP_DIR'; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------- SYS PUMP_DIR /u01/backup删除已有的目录
drop directory pump_dir;向某些用户授权使用目录,可以授权read或write权限,或者两个权限同时授予:
directory pump_dir to frdc;撤销权限
revoke read,write on directory pump_dir from frdc;回到顶部
1导出数据 1.1基本说明 EXPDP USERID='sywu/sywu' job_name=export_tb parallel=3 tables=(tb01,tb02,tb03) dumpfile=pump_dir:dw_20150602.dmp logfile=pump_dir:exptb.log version='11.2.0.1.0' exclude=''userid 表示数据库连接信息,可以是as sysdba权限,非sysdba权限用户可以省略;
job_name 表示导出任务名,未指定默认格式为: SYS_EXPORT_TABLE _*;
parallel 表示并行数,默认为1;
tables 表示导出的表名,可以指定所有者owner.tab,导出分区表的某个分区owner.tab:part01;
dumpfile 表示导出的dump文件名,格式目录名:dump文件名,注:某些版本可能不兼容报错(ORA-39145),可以把目录独立出来用directory参数指定(directory=pump_dir);
version 表示导入目的地数据库版本,在非同一版本数据库之间导出导入数据这个比较重要;
exclude 表示导入排除的对象,DATABASE_EXPORT_OBJECTS表中记录所有的(数据库级别)排除模式,SCHEMA_EXPORT_OBJECTS表中记录schema级别排除对象模式,TABLE_EXPORT_OBJECTS表中记录table级别排除对象模式;
有时候只想导出特定的数据,比如id=10或id in(10,20,30),这种情况下可以用条件限定导出数据(注意字符转义)
expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:\"where object_id in\(10,20,30\)\" dumpfile=query_tab.dump directory=pump_dir Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:09:24 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."SYS_EXPORT_TABLE_01": userid=ops$sywu/******** tables=tb01 query=tb01:"where object_id in(10,20,30)" dumpfile=query_tab.dump directory=pump_dir Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 88 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$SYWU"."TB01" 25.70 KB 192 rows Master table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is: /u01/backup/query_tab.dump Job "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:09:32或者通过rownum限定行的方式导出数据(注意字符转义)
expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:\"where rownum\<10\" dumpfile=query_tab.dump directory=pump_dir Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:14:28 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."SYS_EXPORT_TABLE_01": userid=ops$sywu/******** tables=tb01 query=tb01:"where rownum<10" dumpfile=query_tab.dump directory=pump_dir Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 88 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$SYWU"."TB01" 11.41 KB 9 rows Master table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is: /u01/backup/query_tab.dump Job "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:14:36有些情况下,为了满足测试要求,我们可能需要整库导出或整个schema下的数据导出,但又不需要所有数据,so 同样可以使用限定行数的方式限定所有表数据行导出数据
expdp userid='ops$sywu/sywu' SCHEMAS='ops$sywu' query=\"where rownum\<10\" dumpfile=schema_sywu.dump directory=pump_dir Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:18:34 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "OPS$SYWU"."SYS_EXPORT_SCHEMA_01": userid=ops$sywu/******** SCHEMAS=ops$sywu query="where rownum<10" dumpfile=schema_sywu.dump directory=pump_dir Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 104 MB 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/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC 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/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$SYWU"."TB01" 11.41 KB 9 rows . . exported "OPS$SYWU"."TB02" 6.015 KB 9 rows . . exported "OPS$SYWU"."T_EXCE" 5.820 KB 2 rows Master table "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.SYS_EXPORT_SCHEMA_01 is: /u01/backup/schema_sywu.dump Job "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:18:55内容总结
以上是互联网集市为您收集整理的OracleDataPump导出和导入数据全部内容，希望文章能够帮你解决OracleDataPump导出和导入数据所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。