用数据泵impdp往开发数据库导数据，但导入到INDEX时感觉卡住不动了
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
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
|
查看导入任务对应的会话做在等待什么
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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日志，发现如下报错：
|
1
2
|
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住。启用临时文件的自动扩展问题解决：
|
1
|
alter
database
tempfile
'/oradata/dbs/temp01.dbf'
autoextend
on
next
100m;
|
导入正常：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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
|
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1959357
，如需转载请自行联系原作者
