expdp/ impdp – When impdp is hung..

We use expdp/impdp often as part of backup strategy, data movement, refresh requests, etc. Here is one situation I encountered when trying to import data into a database using impdp.

 

Environment: Oracle 11.2.0.2 on Suse Linux 10 SP2

The import was hung and it was WAITING !!


Identifiy the impdp process at the session level and see what it is waiting on..

 

 

SQL> col username for a15

SQL> col program for a30

SQL> set lines 200

SQL> col machine for a25

SQL> select inst_id,username,machine,program,sid,serial#,status,sql_id,SQL_HASH_VALUE,last_call_et from gv$session where username is not null and username <>'SYS' order by 10;

   INST_ID USERNAME        MACHINE                   PROGRAM                               SID    SERIAL# STATUS   SQL_ID        SQL_HASH_VALUE LAST_CALL_ET

---------- --------------- ------------------------- ------------------------------ ---------- ---------- -------- ------------- -------------- ------------

         1 DBSNMP          nelsdb20                emagent@nelsdb20 (TNS V1-V3)             99         17 INACTIVE                            0           56

         2 DBSNMP          nelsdb21                emagent@nelsdb21 (TNS V1-V3)            226      10053 INACTIVE                            0           75

         1 SSCADM          nelsdb20                oracle@nelsdb20 (DW00)                   40        841 ACTIVE   9035v672ffr0x     3303496733         1085

         2 DBSNMP          nelsdb21                emagent@nelsdb21 (TNS V1-V3)            163         13 INACTIVE                            0       322465

 

 

 

SQL> select event from v$session_wait where sid=40;

 

EVENT

----------------------------------------------------------------

statement suspended, wait error to be cleared

 

SQL> SELECT NAME,STATUS, TIMEOUT, ERROR_NUMBER, ERROR_MSG FROM DBA_RESUMABLE;

 

NAME                       STATUS       TIMEOUT       ERROR_NUMBER  ERROR_MSG

--------------------------- ---------   --------      ------------  -----------------------------------------------------------------

SYS.SYS_IMPORT_SCHEMA_01.1 SUSPENDED       7200              1652   ORA-01652: unable to extend temp segment by 128 in tablespace SSC

SYS.SYS_IMPORT_SCHEMA_01   NORMAL          7200

 

 

The error points to unavailability of free space in the tablespace.  The impdp goes on a resumable state in this case and simply waits.

 

Once space is added to the tablespace, the process automatically picks up and the impdp completes.