impdp导入卡住，等待事件wait for unread message on broadcast channel

现象

Oracle 11.2.0.4 impdp导入数据，卡了很久不动，也不报错。

先说明我的问题吧：

我的环境是因为导入中包含了一个大的表，且该表包含CLOB字段，而CLOB占用了40G的空间，又因为是11.2.0.4的环境对CLOB导出导入有bug，所以特别慢。具体可以参考：https://www.xmmup.com/oracle-11-2-0-4-expdpdaochuhanclobziduanbasicfiledebiaochaojimandewenti.html

原因

一般造成该问题的原因有两种：出现可能是假死或者导入对象为大对象CLOB，造成表象为卡顿的现象。

判断是否彻底卡住了，可以通过如下几种办法：

1、查询表空间大小是否有变化，参考：https://www.xmmup.com/oracle-11gchaxunbiaokongjiandaxiao.html

2、通过ATTACH，进去后输入status命令进行查看：

impdp \'/ AS SYSDBA\'  ATTACH=SYS_IMPORT_FULL_01


-- 例如
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: ABCLOGY
  Object Name: WORKFLOW_ABCLOG
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Completed Rows: 22,368,372
  Completed Bytes: 12,104,771,848
  Percent Done: 69
  Worker Parallelism: 1

3、查询v$session_longops视图获取进度：

SET LINE 9999  PAGESIZE 9999
col username format a10
col OSUSER format a10
col target format a20
col opname format a30
col WAIT_CLASS format a20
col wait_event format a30
col message format a80
col SQL_TEXT format a100
col sofar_TOTALWORK format a20
col session_info format a15
col progress format a8

SELECT a.USERNAME,
       (SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER, 
       (SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID
          FROM v$process pr, v$session nb
         WHERE nb.PADDR = pr.ADDR
           and nb.sid = a.SID
           and nb.SERIAL# = a.SERIAL#) session_info,
       a.opname,
       to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time,
       round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress,
       a.TIME_REMAINING  TIME_REMAINING,
       a.elapsed_seconds elapsed_seconds,
       message message,
       (SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event,
       (SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS
  FROM v$session_longops a
 WHERE a.time_remaining <> 0
 ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid;

解决

WAITEVENT: "wait for unread message on broadcast channel" Reference Note (文档 ID 170464.1)

The Oracle process is waiting for a message on a broadcast channel. This is normally an idle wait - the process is waiting for an AQ message on a subscribed queue.

This event is classed as an "idle" wait so should be ignored when looking at systemwide timings. See <<61998.1>> for more information about "IDLE" waits.

该等待事件是由于oracle进程在请求message时出现等待，该等待是空闲(idle)等待，并且可以忽略

总结

1、首先需要弄懂是不是真的卡住了，还是导入非常慢。若是11.2.0.4的版本，表含有大的CLOB字段，则做导出操作会非常慢，这是一个bug，参考：https://www.xmmup.com/ruhetigaoshujubengdecaozuoxingneng.html#11204han_you_da_declob_zi_duan

2、别忘记检查告警日志，查看是否某个表空间满了或是闪回恢复区满了等问题。