oracle impdp导入时卡住,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_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的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。