Oracle：impdp導入等待statement suspended, wait error to be cleared

用數據泵impdp往開發數據庫導數據，但導入到INDEX時感覺卡住不動了

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
----查看狀態，Completed Objects: 33一直沒有變化。
Import> status
Job: SYS_IMPORT_FULL_01
  Operation: IMPORT                         
  Mode: FULL                           
  State: EXECUTING                      
  Bytes Processed: 843,222,272
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/dump/wj_dev%u.dmp
  Dump File: /home/oracle/dump/wj_dev01.dmp
  Dump File: /home/oracle/dump/wj_dev02.dmp
  Dump File: /home/oracle/dump/wj_dev03.dmp
  Dump File: /home/oracle/dump/wj_dev04.dmp
  Dump File: /home/oracle/dump/wj_dev05.dmp
  Dump File: /home/oracle/dump/wj_dev06.dmp
  Dump File: /home/oracle/dump/wj_dev07.dmp
  Dump File: /home/oracle/dump/wj_dev08.dmp
  Dump File: /home/oracle/dump/wj_dev09.dmp
  Dump File: /home/oracle/dump/wj_dev10.dmp
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: ESOP2TEST
  Object Name: SYS_MSISDNNUMID
  Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
  Completed Objects: 33
  Worker Parallelism: 1

查看導入任務對應的會話做在等待什么

SQL> select * from DBA_DATAPUMP_JOBS;

OWNER_NAME JOB_NAME			  OPERATION			 JOB_MODE			STATE				   DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SYS	   SYS_IMPORT_FULL_01		  IMPORT			 FULL				EXECUTING				1		  2		    4

SQL> select sid,sql_id,event from v$session where action='SYS_IMPORT_FULL_01';

       SID SQL_ID	 EVENT
---------- ------------- ----------------------------------------------------------------
       146 bjf05cwcj5s6p wait for unread message on broadcast channel
       295 58rzgvcv6gnjs statement suspended, wait error to be cleared

從上面的查詢中可以看到“statement suspended, wait error to be cleared”等待事件，這個是一個不常見的等待事件。

繼續查看alert日志，發現如下報錯：

statement in resumable session 'SYS.SYS_IMPORT_FULL_01.1' was suspended due to
    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

看到上面的報錯就能明白為什么導入會卡住不動了，正在導入INDEX，創建索引會使用臨時表空間，但臨時文件太小又沒有設置自動擴展導致創建索引語句HANG住。啟用臨時文件的自動擴展問題解決：

alter database tempfile '/oradata/dbs/temp01.dbf' autoextend on next 100m;

導入正常：

Import> status

Job: SYS_IMPORT_FULL_01
  Operation: IMPORT                         
  Mode: FULL                           
  State: EXECUTING                      
  Bytes Processed: 843,222,272
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/dump/wj_dev%u.dmp
  Dump File: /home/oracle/dump/wj_dev01.dmp
  Dump File: /home/oracle/dump/wj_dev02.dmp
  Dump File: /home/oracle/dump/wj_dev03.dmp
  Dump File: /home/oracle/dump/wj_dev04.dmp
  Dump File: /home/oracle/dump/wj_dev05.dmp
  Dump File: /home/oracle/dump/wj_dev06.dmp
  Dump File: /home/oracle/dump/wj_dev07.dmp
  Dump File: /home/oracle/dump/wj_dev08.dmp
  Dump File: /home/oracle/dump/wj_dev09.dmp
  Dump File: /home/oracle/dump/wj_dev10.dmp
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: SUF3TEST
  Object Name: IDX_ORDER_MEMBER_CHARACTER_ID
  Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
  Completed Objects: 407
  Worker Parallelism: 1