Tag Archives: ORA-39171

Restart Data Pump after import errors

When using Data Pump, you may encounter the following error:
ORA-39171: Job is experiencing a resumable wait.
Together with

ORA-01653: Unable to extend table %s.%s by %s in tablespace %s
ORA-01691  unable to extend lob segment string.string by string in tablespace string
Both errors basically say that the table space you are using can not allocate more space. This could be because the datafile is full, the disk is full or autoextend is not set. This is simple to check by running the following query on DBA_DATA_FILE:
select *
from dba_data_files
where tablespace_name=’TABLESPACE_NAME’

The bytes column will tell you how many bytes are used by Oracle in the tablespace. If AUTOEXTENSIBLE is set to NO, then Oracle will not extend the size of the tablespace.

In many cases, the Data Pump import may seem to hang. Data Pump will try to continue after some time but unless something is changed, you will keep getting the above errors.

The solution, however, is simple enough. All you need to do is use the Data Pump Restart Capability:
1)    In the IMPDP window, click CTRL-C to stop the job.
2)    In the command line type:
Import> stop_job=immediate
3)    Use SQLPlus to make the required changes to the table space. You can:

    a)    Add DataFile ALTER TABLESPACE <tablespace name> ADD DATAFILE ‘<path to file name and  file name>’ SIZE <integer>M;

    b)    Resize the Datafile:
    ALTER DATABASE DATAFILE ‘< path to file name and  file name >’ RESIZE <integer>M;
    c)     Enable autoextend:
    ALTER DATABASE DATAFILE ‘< path to file name and  file name >’ AUTOEXTEND ON MAXSIZE UNLIMITED;

4)    Attach the Job.
>impdp system/manager attach=Job_Name
If you did not provide a specific job name when you first ran the IMPDP, Oracle will assign a default name for it. In order to find the System assigned name for the IMPDP job you can run the following query:
The result will probably be something like SYS_IMPORT_FULL_number.
5)     Restart the job
Import> start_job
6)    You can check the status of the job by simply typing STATUS in the utility command prompt.
Import> status