expdp中PARALLEL和DUMPFILE关系

发表于 2012 年 3 月 7 日由惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：expdp中PARALLEL和DUMPFILE关系

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天有朋友在说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)如果导出文件数量少于并发数时，多于并发将不会工作。

此条目发表在逻辑备份/恢复分类目录。将固定链接加入收藏夹。

评论功能已关闭。

17813235971