Problem phenomenon:recently a user is ready to build a test environment, because the user's official library is the Aix small machine, the test environment is X86 platform, so only through the EXPDP for data export, but in the export, found that EXPDP has been stuck in the following location, a few hours are not moved:
Export:release 11.2.0.3.0-production on Fri Dec 5 13:06:21 2014
Copyright (c) 1982, 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 fromDba_tables where table_name in (' EMR graph ', ' Test image results ', ' test report image ') and owner= ' Zlhis ') "Cluster=n trace=480300Estimate 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
Viewing wait Events
Depending on the wait event, view the SQL statement that the session executes as follows:
Here you can see a table statistics information, but the execution of the SQL statement, found unable to query the results, on Metalink, found a document such as:
EXPDP hangs on AIX when EXCLUDE was used with QUERY CLAUSE (document ID 1513238.1)
Ymptoms
This article are specifically written for Expdp/hang on AIX platforms. If the hang are seen on win/linux then this article are not likely to assist
And if seen on any other platform it's worth looking at the symptoms-see if they match, if they does then there is no ad Verse impact
In applying the advised solution to see if it resolves.
The following symptoms is 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 (the Select table_name from All_tables where ((table_name as ' 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'll become available
OR
- Apply Patchset Release 11.2.0.4 when it becomes available (not available as of the 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, use the MOS Patch Planner Tool. If no patch is available, the file a Service Request through My Oracle support for your specific Oracle version and platform. An AIX OneOff fix are made for 11203
OR
- Use the workaround of specifying, the exclude=statistics command line option for EXPDP.
As you can see, we did use the EXPDP in theexclude=table: "In (SELECT *) such a similar exclusion statement, the document gives the solution is to exclude statistics, try to modify the export statement, exclude statistics, EXPDP successfully completed.
EXPDP stuck problem processing under AIX environment
