用数据泵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
转载于:https://blog.51cto.com/hbxztc/1959357