impdp导入数据，连接上数据库，但是一直等待

C:\Users\Administrator>impdp  boss/boss directory=dir   dumpfile=out-%u.dmp   remap_schema=boss:boss  remap_tablespace=NNC_DATA01:USERS remap_tablespace=NNC_DATA02:USERS   remap_tablespace=NNC_DATA0

3:USERSremap_tablespace=NNC_DATA04:USERS remap_tablespace=NNC_DATA05:USERS  remap_tablespace=NNC_I

NDEX01:USERS remap_tablespace=NNC_INDEX02:USERS   remap_tablespace=NNC_INDEX03:USERSremap_tablespace

=NNC_INDEX4:USERS  remap_tablespace=NNC_INDEX05:USERS

 Import: Release 11.2.0.1.0 - Production on星期五 4月 20 14:44:282012

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved

连接到: Oracle Database 11g Enterprise Edition Release11.2.0.1.0 - Production

 

到此，然后一直等待。。。。

通过查询等待事件

SQL> select sid,serial#,username,program,sql_id,event,p1,p2,p3
  2  from v$session s, dba_datapump_sessions d
  3  where s.saddr = d.saddr;
 
       SID    SERIAL# USERNAME                       PROGRAM                             SQL_ID        EVENT                                                                    P1         P2         P3
---------- ---------- ------------------------------ ---------------------------------------------  ------------- ---------------------------------------------------------------- ---------- ---------- ----------
       405       7030 BOSS                           ORACLE.EXE (DW00)                                       statement suspended, wait error to be cleared                            0          0          0
      1008        299 BOSS                           ORACLE.EXE (DM00)                  bjf05cwcj5s6p wait for unread message on broadcast channel      1660579987 1660536484          0
      1021      54558 BOSS                           impdp.exe                                afcz9s4uazbpk wait for unread message on broadcast channel       1660579987 1660536602          0
  发现一个特殊的等待事件

 

查询support

Cause

Oracle Database provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables you to take corrective action instead of the Oracle Database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The statements that are affected are called resumable statements. The time between suspending the execution till correction of the error is reported as "statement suspended, wait error to be cleared" wait event.

Solution

This wait event is expected in case of enabling Resumable Space Allocation and if you want to avoid it you have to avoid the original space related errors or to disable Resumable Space Allocation.

Resumable space allocation can be disabled by one of the following actions:

- The RESUMABLE_TIMEOUT initialization parameter is set to zero.
- The ALTER SESSION DISABLE RESUMABLE statement is issued.

 

查看表空间发现所要导入表空间使用率在%80-%90，因为user表空间不大

查看日志文件：

Thu Apr 26 10:45:26 2012
DW00 started with pid=82, OS id=1852, wid=1, job BOSS.SYS_IMPORT_SCHEMA_01
statement in resumable session 'BOSS.SYS_IMPORT_SCHEMA_01.1' was suspended due to
    ORA-01691: Lob 段 BOSS.SYS_LOB0000121815C00045$$ 无法通过 128 (在表空间 USERS 中) 扩展
Thu Apr 26 10:50:06 2012

通过给表空间增加空间后，impdp开始报错：作业出现可恢复性等待。。。。，然后继续导入数据，导入数据正常；

 

 

select * from dba_datapump_jobs t where t.owner_name like 'BOSS' and t.state='EXECUTING'；

OWNER_NAME  JOB_NAME                       OPERATION  JOB_MODE   STATE          DEGREE      ATTACHED_SESSIONS           DATAPUMP_SESSIONS
----------            ------------------------------                ----------          ----------     ----------                ---------- -----------------                            -----------------
BOSS       SYS_IMPORT_SCHEMA_01           IMPORT     SCHEMA     EXECUTING           1                 1                                      3

 

 

正常导入的状态是executing，而挂起是的状态是DEFINING