AIX环境下EXPDP卡住问题处理

问题现象：

Export: Release 11.2.0.3.0 - Production on Fri Dec 5 13:06:21 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

查看等待事件

根据等待事件，查看该会话履行的SQL语句以下：

这里可以看到有个表统计信息的信息，但是履行该SQL语句，发现没法查询出结果，上metalink上，找到1篇以下文档：

EXPDP HANGS ON AIX WHEN EXCLUDE IS USED WITH QUERY CLAUSE (文档 ID 1513238.1)

YMPTOMS

This article is specifically written for EXPDP/HANG on AIX platforms.  If the HANG is seen on WIN/Linux then this article is not likely to assist

and if seen on any other platform it is worth looking at the symptoms to see if they match, if they do then there is no adverse impact

in applying the advised solution to see if it resolves.

 

The following symptoms are relevant :-

a) EXPDP at FULL or SCHEMA level gets beyond the 'Total estimation using BLOCKS method' phase and then seems to hang

b) The INCLUDE/EXCLUDE option is being used with a QUERY clause e.g.

 

INCLUDE=TABLE:"not in (select do.object_name from dba_objects do where do.object_name = 'DUAL')"

EXCLUDE=TABLE:"IN (select table_name from  all_tables where  ((table_name like 'XN_%' )   or (table_name like 'TR_%' ))) "

 

c) If in addition to the EXLUDE in (B) we also use EXCLUDE=STATISTICS the EXPDP no longer hangs and runs to completion.

SOLUTION

To implement a solution for unpublished Bug:14095143, please execute any of the below alternative solutions:

 

• Upgrade to 12.1 when it will become available
  
  OR

• Apply patchset release 11.2.0.4 when it becomes available (not available as of time of publishing this article: DEC⑵012) in which Bug:14095143 is fixed.
  
  OR

• Download and apply interim Patch:14095143, if available for your platform and RDBMS release. To check for conflicting patches, please use the MOS Patch Planner Tool. If no patch is available, file a Service Request through My Oracle Support for your specific Oracle version and platform.  An AIX oneoff fix is made for 11203
  
  OR
• Use the workaround of specifying the EXCLUDE=STATISTICS command line option for EXPDP.

可以看到，我们确切EXPDP中使用了EXCLUDE=TABLE:"IN (select * )  这类类似的排除语句，文档给出的解决方法是排除统计信息，尝试修改导出语句，排除统计信息，EXPDP顺利完成。