问题现象： 最近一用户准备搭建测试环境，由于该用户正式库是AIX小机，测试环境是X86平台，因此只能通过expdp进行数据导出，但是在导出的时候，发现EXPDP一直卡在如下位置,几个小时都不动弹： Export: Release 11.2.0.3.0 – Production on Fri Dec 5 13:06:2
问题现象：
最近一用户准备搭建测试环境，由于该用户正式库是AIX小机，测试环境是X86平台，因此只能通过expdp进行数据导出，但是在导出的时候，发现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.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsStarting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** dumpfile=dump:full20141204b_%U_db.dmp logfile=dump:full_expdp1204b_db.log full=y exclude=PACKAGE,FUNCTION,PROCEDURE,INDEX,TABLE:”IN (select table_name from dba_tables where table_name in (‘电子病历图形’,’检验图像结果’,’检验报告图像’) and owner=’ZLHIS’)” cluster=n TRACE=480300 Estimate in progress using BLOCKS method…Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATATotal estimation using BLOCKS method: 125.3 GBProcessing object type DATABASE_EXPORT/TABLESPACEProcessing object type DATABASE_EXPORT/PROFILEProcessing object type DATABASE_EXPORT/SYS_USER/USERProcessing object type DATABASE_EXPORT/SCHEMA/USERProcessing object type DATABASE_EXPORT/ROLEProcessing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLEProcessing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTAProcessing object type DATABASE_EXPORT/RESOURCE_COSTProcessing object type DATABASE_EXPORT/SCHEMA/DB_LINKProcessing object type DATABASE_EXPORT/TRUSTED_DB_LINKProcessing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCEProcessing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/CROSS_SCHEMA/OBJECT_GRANTProcessing object type DATABASE_EXPORT/DIRECTORY/DIRECTORYProcessing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/CONTEXTProcessing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYMProcessing object type DATABASE_EXPORT/SCHEMA/SYNONYMProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPECProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/CROSS_SCHEMA/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEMProcessing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJProcessing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEMProcessing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMAProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLEProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTIONProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENTProcessing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEWProcessing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENTProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
查看等待事件
根据等待事件，查看该会话执行的SQL语句如下：
这里可以看到有个表统计信息的信息，但是执行该SQL语句，发现无法查询出结果，上metalink上，找到一篇如下文档：vcD4KPGgyPjxzdHJvbmc+RVhQRFAgSEFOR1MgT04gQUlYIFdIRU4gRVhDTFVERSBJUyBVU0VEIFdJVEggUVVFUlkgQ0xBVVNFICjOxLW1IElEIDE1MTMyMzguMSk8L3N0cm9uZz48L2gyPgo8cD48L3A+CjxoMiBjbGFzcz0=”km”>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-2012) 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本文来源gao.dai.ma.com搞@代*码(网$ specific Oracle version and platform. An AIX oneoff fix is made for 11203
ORUse the workaround of specifying the EXCLUDE=STATISTICS command line option for EXPDP.可以看到，我们确实EXPDP中使用了EXCLUDE=TABLE:”IN (select * ) 这种类似的排除语句，文档给出的解决方法是排除统计信息，尝试修改导出语句，排除统计信息，EXPDP顺利完成。
转载请注明原文链接：AIX环境下EXPDP卡住问题处理
