联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天有朋友在说PARALLEL设置为n(大于1),DUMPFILE为一个文件(不包含%U),会出现什么样的情况。下面通过实验来说明这个问题
1.当并发数比较大时(这里实验使用4),expdp会报ORA-39095错误
[oracle@node1 tmp]$ expdp chf/xifenfei DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=4 Export: Release 11.2.0.3.0 - Production on Wed Mar 7 20:48:52 2012 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, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "CHF"."SYS_EXPORT_TABLE_01": chf/******** DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=4 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 5.362 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
2.并发数较少时(实验为2),通过attach观察
2.1)执行expdp导出
[oracle@node1 tmp]$ expdp chf/xifenfei DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=2 Export: Release 11.2.0.3.0 - Production on Wed Mar 7 20:49:15 2012 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, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Starting "CHF"."SYS_EXPORT_TABLE_02": chf/******** DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=2 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 5.362 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX . . exported "CHF"."T1" 4.647 GB 51080958 rows Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "CHF"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded ****************************************************************************** Dump file set for CHF.SYS_EXPORT_TABLE_02 is: /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp Job "CHF"."SYS_EXPORT_TABLE_02" successfully completed at 20:52:23
2.2)在导出过程中,使用attach观察expdp工作情况
[oracle@node1 trace]$ expdp chf/xifenfei attach=SYS_EXPORT_TABLE_02 Export: Release 11.2.0.3.0 - Production on Wed Mar 7 20:49:38 2012 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, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Job: SYS_EXPORT_TABLE_02 Owner: CHF Operation: EXPORT Creator Privs: TRUE GUID: BAA7964815021C96E0438C09A8C04319 Start Time: Wednesday, 07 March, 2012 20:49:16 Mode: TABLE Instance: chf Max Parallelism: 2 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND chf/******** DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=2 State: EXECUTING Bytes Processed: 0 Current Parallelism: 2 Job Error Count: 0 Dump File: /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp bytes written: 4,096 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: CHF Object Name: T1 Object Type: TABLE_EXPORT/TABLE/TABLE Completed Objects: 1 Total Objects: 1 Worker Parallelism: 1 Worker 2 Status: Process Name: DW02 State: EXECUTING Object Schema: CHF Object Name: T1 Object Type: TABLE_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Total Objects: 1 Completed Rows: 16,104,279 Worker Parallelism: 1 Export> status Job: SYS_EXPORT_TABLE_02 Operation: EXPORT Mode: TABLE State: EXECUTING Bytes Processed: 0 Current Parallelism: 2 Job Error Count: 0 Dump File: /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp bytes written: 4,096 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: CHF Object Name: T1 Object Type: TABLE_EXPORT/TABLE/TABLE Completed Objects: 1 Total Objects: 1 Worker Parallelism: 1 Worker 2 Status: Process Name: DW02 State: EXECUTING Object Schema: CHF Object Name: T1 Object Type: TABLE_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Total Objects: 1 Completed Rows: 35,425,534 Worker Parallelism: 1 Export> status Job: SYS_EXPORT_TABLE_02 Operation: EXPORT Mode: TABLE State: COMPLETING Bytes Processed: 4,989,989,105 Percent Done: 100 Current Parallelism: 2 Job Error Count: 0 Dump File: /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp bytes written: 4,990,009,344 Worker 1 Status: Process Name: DW00 State: WORK WAITING Worker 2 Status: Process Name: DW02 State: WORK WAITING
3.最终结论
3.1)如果并发设置过大,在导出过程中直接报错
3.2)如果导出文件数量少于并发数时,多于并发将不会工作。