使用impdp工具導入，發現dw00程序在編譯包時候在等待library cache pin 事件：
SQL>selectsid,program,event,BLOCKING_SESSION,state,sql_idfromv$sessionwhereusernameis
notnullandstatus='ACTIVE'orderbyevent,sql_id;
SID PROGRAM EVENT BLOCKING_SESSION STATE
SQL_ID
--- ------------------------------------------------ ----------------------------------- ---------------- -------------------
-------------
38 [email protected] (TNS V1-V3) SQL*Net message toclient WAITED SHORT
TIME6jg0jp7ggufy6
5 [email protected] (TNS V1-V3) Streams AQ: waiting formessagesinWAITING
the queue
134 [email protected] (DW00) library cache pin 134 WAITING
5bdu5n68x313q
189 OMS wait forunread messageonbroadcas WAITING
2b064ybzkwf1y
t channel
68 [email protected] (TNS V1-V3) wait forunread messageonbroadcas WAITING
SQL>select*fromtable(dbms_xplan.display_cursor('5bdu5n68x313q'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
SQL_ID 5bdu5n68x313q, child number 0
ALTERPACKAGE"HFMIS"."PKG_CPT_MSG"COMPILE BODY
PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= TRUEPLSCOPE_SETTINGS='IDENTIFIERS:NONE'REUSE
SETTINGS TIMESTAMP'2012-12-17 19:07:36'
導入時沒有使用并行，且沒有任何其他程序連上資料庫，出現這種異常情況，而且阻塞程序是自己本身，估計很有可能是oracle bug造成的
搜尋metlink，發現與文檔《Bug 13624984 - IMPDP or IMP hangs on compilation of PLSQL containinga pipelined function [ID 13624984.8]》所描述相吻合；
Impdp在編譯包含有管道函數的pl/sql包時遇到了此bug
處理方式：
1) Do not use datapump import nor import to load and compile the PLSQL
or
2) Manually grant EXECUTE WITH GRANT OPTION privilege on the shadow
type to the user doing the compilation before attempting
the compilation.
or
3) Wait for the compilation to finish - it will complete - it just
blocks for 15-30 minutes
4)patch to 11.2.0.3.4 version
這裡打更新檔至11.2.0.3.4版本後再次執行impdp故障解除