用數據泵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_MODESTATE???DEGREE?ATTACHED_SESSIONS?DATAPUMP_SESSIONS

----------?------------------------------?------------------------------?------------------------------?------------------------------?----------?-----------------?-----------------

SYS???SYS_IMPORT_FULL_01??IMPORT?FULLEXECUTING1??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

總結

以上是生活随笔為你收集整理的oracle impdp导入时卡住,Oracle：impdp导入等待statement suspended, wait error to be cleared的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。