Impdp imports data, connects to the database, but waits

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 Friday 14: 44: 282012

Copyright (c) 1982,200 9, Oracle and/or itsaffiliates. All Rights Reserved

Connect to: Oracle Database 11g Enterprise Edition release11.2.0.1.0-Production

 

At this point, and then keep waiting ....

Query wait events

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
Discover a special wait event

 

Query 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
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.

 

The usage of the tablespace to be imported is % 80-% 90, because the user tablespace is not large.

View log files:

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 suincluded due
ORA-01691: Lob segment boss. sys_lob0000121815c00045 $ unable to scale through 128 (in tablespace Users)
Thu Apr 26 10:50:06 2012

After adding space to the tablespace, impdp starts to report an error: the job waits for recoverability..., and then continues to import data. The imported data is normal;

 

 

Select * From dba_datapump_jobs t where T. owner_name like 'bos' 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

 

 

The normal import status is executing, while the pending status is defining.