Oracle Data Pump 导出和导入数据(2)

有些情况下,为了满足测试要求,我们可能需要整库导出或整个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

有些情况下,导出的数据需要通过网络或通过媒介传输到其它目的地,考虑媒介的大小和网络情况,每次只能限定传输,为了达到这个目的可以将数据导出到多个文件,然后再分批或独立发送;比如评估的数据大小是80M,我想把文件导出为4个文件,每个文件的大小为20M,so 我像这样导出数据:

expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:03:03 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"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log 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" 74.64 MB 844416 rows . . exported "OPS$SYWU"."TB02" 452.3 KB 13448 rows . . exported "OPS$SYWU"."T_EXCE" 5.820 KB 2 rows Master table "OPS$SYWU"."EXPORTTB_BYSIZE" successfully loaded/unloaded ****************************************************************************** Dump file set for OPS$SYWU.EXPORTTB_BYSIZE is: /u01/backup/size_each_sywu.dump /u01/backup/file02.dmp /u01/backup/file03.dmp /u01/backup/file04.dmp Job "OPS$SYWU"."EXPORTTB_BYSIZE" successfully completed at 18:03:24

filesize 表示每个文件的大小,单位为:bytes
dumpfile 表示导出的4个dump文件名,如果实际导出数据大小大于指定的文件数据(dumpfile)乘以每个文件大小(filesize)之合,则导出停止并报错
ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes Job "OPS$SYWU"."SYS_EXPORT_SCHEMA_07" stopped due to fatal error at 17:57:31
有个地方要注意:在导出数据时,数据库会在导数用户下根据job_name名称创建一张表,比如job_name=exporttb_bysize,在导数过程中数据库���建一张名EXPORTTB_BYSIZE的表,该表记录了导数的信息,导数成功完成后自动删除,如果导数未成功,比如空间不足或实际大小大于指定大小,则job停止,该表不会自动删除。
为了说明问题,我将filesize改为每个文件10m模拟错误发生,最后观察状态,然后解决错误;

expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:21:07 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"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log 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 ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes Job "OPS$SYWU"."EXPORTTB_BYSIZE" stopped due to fatal error at 18:21:26

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/43bf65e73c0c33098e3a92024cfde3a3.html