共计 3188 个字符,预计需要花费 8 分钟才能阅读完成。
用数据泵 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
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-08/146519.htm
正文完
星哥玩云-微信公众号