Question:
I am getting this ORA-39171 error during an import:
statement in resumable session 'SYSTEM.SYS_IMPORT_FULL_02.1'
was suspended due to
ORA-01659: unable to allocate
MINEXTENTS beyond 1 in tablespace MY_DATA
How do I fix the ORA-39171 error and
resume my import (impdp) job?
Answer:
The oerr utility shows this for the ORA-39171 error
message:
ORA- 39171: Job is
experiencing a resumable wait.\n%s"
Cause:
The Data Pump job is stalled with one or more of
its sessions having a resumable wait. Resumable waits are
typically caused by a non-expandable tablespace running out
of space. The follow-on message describes the nature of the
wait.
Action: Correct the condition
causing the wait. This will typically involve adding
datafiles to the tablespace that is full.
The resumable_timeout parameter setting determines the wait
time before the job aborts:
ORA-30032: the
suspended (resumable) statement has timed out
When you have a resumable wait, you can use the following
steps to re-start the import utility:
1: From the impdp windows, use <ctrl> c to stop the
import job. This will result in a prompt like this
Import>
2: Next, stop the job without aborting it:
Import> stop_job=immediate
2a. At this point, you can kill the
job if you wish to start over:
Import> kill_job
3: Next, fix the root cause of the error.
This could be adding a datafile to a tablespace or resizing
a datafile or enabling the "autoextend" option for a
tablespace.
alter datafile 'C:\myfiles\mydata.dbf' AUTOEXTEND ON MAXSIZE
UNLIMITED;
4. Now we re-attach the import job to its session
in the database:
C:\import> impdp scott/tiger parfile
my_imp.par attach=job_name
If you omit the attach parameter, Oracle will
assign a default name for the job.
5: Now, we can re-start the import (impdp) job:
Import> start_job
Note that at any time during the execution of the import
job you can use <cntl> c and use this command to see the
status of your import job:
Import> status
