expdp进程HANG住了

故障现象如下：

$ more zsdump.ora
dumpfile=testpara_%U.dmp
PARALLEL=3
DIRECTORY=szdump
TABLES=(
BULK.TW_USRCALL_MO_200703,
BULK.TW_USR_BUSIOFFICE_DAY_200704,
BULK.TW_USR_CELLUSE_DAY_200705,
BULK.TW_USR_CELLUSE_DAY_200704,
BULK.TW_USR_BUSIUSE_DAY_200705
)
EXCLUDE=REF_CONSTRAINT,GRANT,INDEX,STATISTICS
$ expdp BULK/BULK PARFILE=zsdump.ora logfile=testpara3.log

[@more@]

Export: Release 10.2.0.2.0 - 64bit Production on Thursday, 31 May, 2007 14:34:34

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "BULK"."SYS_EXPORT_TABLE_02": BULK/******** PARFILE=zsdump.ora logfile=testpara.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47.16 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/COMMENT
. . exported "BULK"."TW_USR_BUSIUSE_DAY_200705" 9.265 GB 78101543 rows
. . exported "BULK"."TW_USR_CELLUSE_DAY_200704" 9.071 GB 54889856 rows
.....................
....................

至此，EXPDP进程就一直挂住不动，查看等待事件发现

SQL> select sid, serial#, sofar, totalwork
2 from v$session_longops
3 where opname like 'SYS_EXPORT_TABLE%'
4 and sofar != totalwork;

SID SERIAL# SOFAR TOTALWORK
---------- ---------- ---------- ----------
841 12691 18778 48300

SQL> select event from v$session_wait where sid=841;

EVENT
----------------------------------------------------------------
wait for unread message on broadcast channel

ORACLE解释这是个空闲事件，不必理会，在METALINK上Note:170464.1也做了解释
半个小时过去，还是

SID SERIAL# SOFAR TOTALWORK
---------- ---------- ---------- ----------
841 12691 18778 48300

强行断掉这个进程，不指定PARALLEL，导出成功。然后修改PARALLEL参数分别为4，5，6，分别测试，

还是和前面情况一样，最后修改PARALLEL参数为7，导出才成功，以下为导出的日志

Export: Release 10.2.0.2.0 - 64bit Production on Thursday, 31 May, 2007 17:09:51

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "BULK"."SYS_EXPORT_TABLE_03": BULK/******** PARFILE=zsdump.ora logfile=testpara3.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47.16 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/COMMENT
. . exported "BULK"."TW_USR_BUSIUSE_DAY_200705" 9.265 GB 78101543 rows
. . exported "BULK"."TW_USR_CELLUSE_DAY_200704" 9.071 GB 54889856 rows
. . exported "BULK"."TW_USR_CELLUSE_DAY_200705" 9.223 GB 54311228 rows
;;; Export> status
. . exported "BULK"."TW_USR_BUSIOFFICE_DAY_200704" 7.948 GB 109663502 rows
;;; Export> status
;;; Export> status
;;; Export> status
;;; Export> status
. . exported "BULK"."TW_USRCALL_MO_200703" 13.45 GB 108700293 rows
Master table "BULK"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
******************************************************************************
Dump file set for BULK.SYS_EXPORT_TABLE_03 is:
/zs_arch01/testpara_01.dmp
/zs_arch01/testpara_02.dmp
/zs_arch01/testpara_03.dmp
/zs_arch01/testpara_04.dmp
/zs_arch01/testpara_05.dmp
/zs_arch01/testpara_06.dmp
/zs_arch01/testpara_07.dmp
/zs_arch01/testpara_08.dmp
/zs_arch01/testpara_09.dmp
Job "BULK"."SYS_EXPORT_TABLE_03" successfully completed at 17:14:58

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/58242/viewspace-916993/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/58242/viewspace-916993/