用数据泵impdp往开发数据库导数据,但导入到INDEX时感觉卡住不动了
ProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/INDEX ----查看状态,CompletedObjects:33一直没有变化。 Import>status Job:SYS_IMPORT_FULL_01 Operation:IMPORT Mode:FULL State:EXECUTING BytesProcessed:843,222,272 PercentDone:99 CurrentParallelism:1 JobErrorCount:0 DumpFile:/home/oracle/dump/wj_dev%u.dmp DumpFile:/home/oracle/dump/wj_dev01.dmp DumpFile:/home/oracle/dump/wj_dev02.dmp DumpFile:/home/oracle/dump/wj_dev03.dmp DumpFile:/home/oracle/dump/wj_dev04.dmp DumpFile:/home/oracle/dump/wj_dev05.dmp DumpFile:/home/oracle/dump/wj_dev06.dmp DumpFile:/home/oracle/dump/wj_dev07.dmp DumpFile:/home/oracle/dump/wj_dev08.dmp DumpFile:/home/oracle/dump/wj_dev09.dmp DumpFile:/home/oracle/dump/wj_dev10.dmp Worker1Status: ProcessName:DW00 State:EXECUTING ObjectSchema:ESOP2TEST ObjectName:SYS_MSISDNNUMID ObjectType:SCHEMA_EXPORT/TABLE/INDEX/INDEX CompletedObjects:33 WorkerParallelism:1
查看导入任务对应的会话做在等待什么
sql>select*fromDBA_DATAPUMP_JOBS; OWNER_NAMEJOB_NAME OPERATION JOB_MODE STATE DEGREEATTACHED_SESSIONSDATAPUMP_SESSIONS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SYS SYS_IMPORT_FULL_01 IMPORT FULL EXECUTING 1 2 4 sql>selectsid,sql_id,eventfromv$sessionwhereaction='SYS_IMPORT_FULL_01'; SIDsql_ID EVENT --------------------------------------------------------------------------------------- 146bjf05cwcj5s6pwaitforunreadmessageonbroadcastchannel 29558rzgvcv6gnjsstatementsuspended,waiterrortobecleared
从上面的查询中可以看到“statement suspended,wait error to be cleared”等待事件,这个是一个不常见的等待事件。
继续查看alert日志,发现如下报错:
statementinresumablesession'SYS.SYS_IMPORT_FULL_01.1'wassuspendeddueto ORA-01652:unabletoextendtempsegmentby128intablespaceTEMP
看到上面的报错就能明白为什么导入会卡住不动了,正在导入INDEX,创建索引会使用临时表空间,但临时文件太小又没有设置自动扩展导致创建索引语句HANG住。启用临时文件的自动扩展问题解决:
alterdatabasetempfile'/oradata/dbs/temp01.dbf'autoextendonnext100m;
导入正常:
Import>status Job:SYS_IMPORT_FULL_01 Operation:IMPORT Mode:FULL State:EXECUTING BytesProcessed:843,272 PercentDone:99 CurrentParallelism:1 JobErrorCount:0 DumpFile:/home/oracle/dump/wj_dev%u.dmp DumpFile:/home/oracle/dump/wj_dev01.dmp DumpFile:/home/oracle/dump/wj_dev02.dmp DumpFile:/home/oracle/dump/wj_dev03.dmp DumpFile:/home/oracle/dump/wj_dev04.dmp DumpFile:/home/oracle/dump/wj_dev05.dmp DumpFile:/home/oracle/dump/wj_dev06.dmp DumpFile:/home/oracle/dump/wj_dev07.dmp DumpFile:/home/oracle/dump/wj_dev08.dmp DumpFile:/home/oracle/dump/wj_dev09.dmp DumpFile:/home/oracle/dump/wj_dev10.dmp Worker1Status: ProcessName:DW00 State:EXECUTING ObjectSchema:SUF3TEST ObjectName:IDX_ORDER_MEMBER_CHARACTER_ID ObjectType:SCHEMA_EXPORT/TABLE/INDEX/INDEX CompletedObjects:407 WorkerParallelism:1