http://blog.itpub.net/28602568/viewspace-2133375/ 转帖 EXPDP 里面 parallel 与 dumpfile 里面的文件数的关系. 但是我这里有一个疑惑 我impdp的时候 导出的parallel 用的是 8 产生了 8个文件 我导入的时候 选择是 4 就报错. 可能导入与导出需要的文件数目必须相同才可以.
同事晚上值班使用expdp备份1TB量的DB，“开并行”备份了5个小时。
命令：
expdp "'/ as sysdba'" dumpfile=all.dmp directory=expdp schemas=用户 parallel= compression=all
原因：
导出文件数量少于并发数时，多于并发将不会工作，也就是说导出文件dumpfile的个数就是有效的parallel并行个数。
dumpfile=file_name.%U.dmp 文件将按需要创建n+1(通配符 %U )
【如果dumpfile 指定一个文件，并发设置过大，在导出过程中可能直接报错（ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes）
导入是paralle要小于dump文件数。 如果paralle 大于dump文件的个数，就会因为超过的那个进程获取不到文件，就不能对性能提高。】
演示：
命令：
expdp "'/ as sysdba'" dumpfile=al1.dmp,al2.dmp directory=expdp schemas=USR_WMS_CITY parallel= compression=all
attach观察expdp导出情况：
$ expdp "'/ as sysdba'" attach=SYS_EXPORT_SCHEMA_05
Job: SYS_EXPORT_SCHEMA_05
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 44C8EA6252512B44E0530701F00AF814
Start Time: Thursday, December, ::
Mode: SCHEMA
Instance: test
Max Parallelism:
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "/******** AS SYSDBA" dumpfile=al1.dmp,al2.dmp directory=expdp schemas=USR_WMS_CITY parallel= compression=all
COMPRESSION ALL
State: EXECUTING
Bytes Processed:
Current Parallelism:
Job Error Count:
Dump File: /data/auto/al1.dmp
bytes written: ,
Dump File: /data/auto/al2.dmp
bytes written: , Worker Status:
Process Name: DW00
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: ITEM
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Worker Parallelism: Worker Status:
Process Name: DW01
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_MOVEHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Completed Rows: ,,
Worker Parallelism: Worker Status:
Process Name: DW02
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORYHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Completed Rows: ,,
Worker Parallelism: Worker Status:
Process Name: DW03
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORY
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Worker Parallelism: Export>
Export> status Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed:
Current Parallelism:
Job Error Count:
Dump File: /data/auto/al1.dmp
bytes written: ,
Dump File: /data/auto/al2.dmp
bytes written: , Worker Status:
Process Name: DW00
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: ITEM
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Worker Parallelism: Worker Status:
Process Name: DW01
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_MOVEHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Completed Rows: 26,723,846
Worker Parallelism: Worker Status:
Process Name: DW02
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORYHIS
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Completed Rows: 34,458,978
Worker Parallelism: Worker Status:
Process Name: DW03
State: EXECUTING
Object Schema: USR_WMS_CITY
Object Name: CON_CONTENT_HISTORY
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects:
Total Objects:
Worker Parallelism: Export>
****** 只有2个进程(2个dumpfile)在工作(有completed rows:XXX)
后台数据库层查看正在执行的sql如下...event = direct path read 是2个工作进程，其他2个transceive_int 对应等待是wait for unread message on broadcast channel 空闲等待；
使用dumpfile=$bak_time.%U.dmp后台情况..
expdp "'/ as sysdba'" dumpfile=$bak_time.%U.dmp directory=expdp schemas=USR_WMS_CITY parallel=4 compression=all logfile=$bak_time.log
【源于本人笔记】 若有书写错误，表达错误，请指正...