OS環境：windows2008

資料庫版本：oracle 11.2.0

使用工具：EXPDP

今天幫同事在測試庫上匯出兩張表，第一張表大小為30M，第二章表大小為7G，以下是expdp語法

expdp tranuser/******** parallel=4 content=data_only directory=my_job dumpfile=wuqi02.dmp job_name=wuqi02 tables=(table_name)

第一張表很簡單的匯出完畢了，但是匯出第二張表的時候則報錯，報錯內容如下

;;;

Export: Release 11.2.0.1.0 - Production on Fri May 13 13:40:01 2011

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "TRANUSER"."WUQI02": tranuser/******** parallel=4 content=data_only directory=my_job dumpfile=wuqi02.dmp job_name=wuqi02 tables=(BOND_CB_YIELD_CUR_F)

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 7.062 GB

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Job "TRANUSER"."WUQI02" stopped due to fatal error at 13:40:20

Job WUQI02 has been reopened at Friday, 13 May, 2011 13:44

Restarting "TRANUSER"."WUQI02": tranuser/******** parallel=4 content=data_only directory=my_job dumpfile=wuqi02.dmp job_name=wuqi02 tables=(BOND_CB_YIELD_CUR_F)

ORA-39097: Data Pump job encountered unexpected error -39079

ORA-39065: unexpected master process exception in DISPATCH

ORA-39079: unable to enqueue message RP,KUPC$C_1_20110513134453,MCP,KUPC$A_1_20110513134602,1,Y

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.KUPC$QUE_INT", line 965

ORA-24033: no recipients for message

ORA-39065: unexpected master process exception in SEND

ORA-39079: unable to enqueue message RP,KUPC$C_1_20110513134453,MCP,KUPC$A_1_20110513134602,1,Y

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.KUPC$QUE_INT", line 965

ORA-24033: no recipients for message

Job "TRANUSER"."WUQI02" stopped due to fatal error at 13:46:03

Job WUQI02 has been reopened at Friday, 13 May, 2011 13:53

ORA-39012: Client detached before the job started.

Job "TRANUSER"."WUQI02" stopped due to fatal error at 14:13:31

透過在網上查詢相關的解決文件，我發現我這次的報錯分為兩個，一個是ORA-39095，另一個是ORA-39097，下面讓我們來解決一下這個兩個報錯。

1：解決ORA-39095報錯，出現該報錯的原因一般分為兩種情況。

第一種情況是因為在expdp的時候給才轉儲空間太小，資料無法寫入轉儲檔案內，所以報錯，這種報錯很好解決，只需要進入互動模式下，可以用add_file來增加轉儲檔案，然後restart該job即可。

第二種情況則是因為設定引數parallel大於轉儲檔案數目引起的，官方文件的解釋是parallel io server processes寫檔案不能同時寫一個，如果只有一個dumpfile(或少於parallel)就會影響效能。不但如此，當一個io server process在等待從而不能寫dumpfile的時候就會報ORA-39095

解決該問題一共兩種辦法

1：進入到互動模式，減少parallel的數量，使其等於dmp檔案的數量或增加dmp檔案，使其與parallel數量相等。

2：在寫匯出語句的時候指定dumpfile中使用變數 %u(大小寫均可)，讓其自由分配轉儲檔案即可。

2：解決ORA-39097報錯

該報錯我並沒有查詢到根本的原因，去metalink上看到有解決辦法，則是加大streams_pool_size即可，匯出恢復正常 alter system set streams_pool_size = 48M;

------------------------&gt>另外一貼
EXPDP/IMPDP進行Oracle資料遷移從高版本（11g）到底版本（10g）步驟 Oralce資料匯入

可能產生的錯誤

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at 13:34:40

expdp時parallel不當也會引起ORA-39095
2008-09-24 15:01

在expdp做匯出的時候會有碰到ora-39095的錯誤，引起這個錯誤的原因有兩種。一一說來
先看官方的解釋：
ORA-39095: Dump file space has been exhausted: Unable to allocate string bytes
Cause: The Export job ran out of dump file space before the job was completed.
Action: Reattach to the job and add additional dump files to the job restarting the job.
從字面意思就解釋了第一種原因，那就是：空間不夠了。解決方法也簡單，多來點空間。
還有第二中原因：當使用了PARALLEL但是dumpfile卻只有一個或小於parallel數，下面是官方的說明：

Oracle? Database Utilities
10g Release 2 (10.2)
2 Data Pump Export
PARALLEL=integer
The value you specify for integer should be less than, or equal to, the number of files in the dump file set (or you should specify substitution variables in the dump file specifications).
Because each active worker process or I/O server process writes exclusively to one file at a time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job.(第一個原因是影響效能)
More importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error. (ora-39095的成因)Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job.
說白話點就是：parallel io server processes寫檔案不能同時寫一個，如果只有一個dumpfile(或少於parallel)就會影響效能。不但如此，當一個io server process在等待從而不能寫dumpfile的時候就會報ora-39095
要解決：expdp ATTACH 連上作業然後減少parallel或者增加dumpfile
從起源解決的話就是：指定parallel的時候就要指定至少同樣多的dumpfile或者使用類似下面的命令(注意紅字)：
expdp hr/hr DIRECTORY=dpump_dir1 LOGFILE=parallel_export.log JOB_NAME=par4_job DUMPFILE=par_exp%u.dmp PARALLEL=4

解決 ORA-39095 ORA-3909 EXPDP

相關文章