分享程序员开发的那些事...
更新时间：2022-10-04 22:22:51
用数据泵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> statusJob: 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 4SQL> 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> statusJob: 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
|