Monitor Oracle Data Pump Resumable Waits with DBA_RESUMABLE

calendar Mar 4, 2026 / · 5 min read · oracle database data-pump impdp dba-resumable oracle-admin import-export space-management dba_resumable  ·
Share on: linkedin copy

Monitor Oracle Data Pump Resumable Waits with DBA_RESUMABLE

When you run a large Oracle Data Pump import (impdp), the job can fail mid-way if a tablespace runs out of space. Oracle's resumable import feature prevents this by suspending the job instead of failing it, giving you time to fix the problem and then letting the job continue automatically.

What Is a Resumable Import?

A resumable operation is a database operation that can be paused when it hits a correctable error — such as a full tablespace — and then automatically resumed once the problem is fixed. Instead of losing all progress and restarting from zero, you simply add space and the import picks up where it left off.

Resumable Parameters and Data Pump Import

Data Pump impdp has no RESUMABLE, RESUMABLE_NAME or RESUMABLE_TIMEOUT parameters. Those three belong to the original imp utility. If you pass them to impdp, it switches to legacy mode and ignores them, because Data Pump provides resumable waits automatically for users granted the IMP_FULL_DATABASE role. To give the job a name you can find later, use JOB_NAME instead.

Original imp parameterWhat impdp does with it
resumable=yIgnored — resumable waits are automatic for IMP_FULL_DATABASE users
resumable_nameIgnored — name the job with JOB_NAME
resumable_timeoutIgnored

In the original imp utility, RESUMABLE_TIMEOUT set how long a suspended statement waited for the error to be fixed before it was terminated, with a documented default of 7200 seconds (2 hours). The Data Pump documentation does not list an equivalent impdp parameter.

The RESUMABLE Privilege

For impdp, resumable waits are provided automatically to users with the IMP_FULL_DATABASE role. The RESUMABLE system privilege is what a user needs to enable resumable space allocation for ordinary SQL statements with ALTER SESSION ENABLE RESUMABLE.

1grant resumable to <user>;

Oracle requires this grant because suspended statements can hold system resources while they wait.

Monitoring with DBA_RESUMABLE

A suspended import may not be obvious from the impdp session itself. Oracle reports the error in the alert log, and the statement appears in the DBA_RESUMABLE view, so check either one to know if the job is waiting.

Query 1 — Check Job Status

This query shows all active resumable statements and their current state:

1set lines 100 pages 999
2col name format a20
3
4select name
5      ,start_time
6      ,suspend_time
7      ,status
8from   dba_resumable
9/

Column breakdown:

ColumnDescription
NAMEFor a Data Pump job, the job name (JOB_NAME, or a default such as SYS_IMPORT_FULL_01)
START_TIMEWhen the resumable statement started
SUSPEND_TIMEWhen the statement was last suspended (null if it has never been suspended)
STATUSOne of RUNNING, SUSPENDED, TIMEOUT, ERROR or ABORTED

The DBA_RESUMABLE view displays all resumable statements currently executing or suspended in the system. When STATUS is SUSPENDED, the job is waiting for you to fix a resource problem before it can continue.

Query 2 — Find the Reason for Suspension

If a job is suspended, this query tells you exactly what went wrong:

1set lines 100 pages 999
2
3select error_msg
4from   dba_resumable
5where  name like '%&job_name%'
6/

The ERROR_MSG column holds the message for ERROR_NUMBER, the last correctable error — typically a space error on a tablespace that cannot extend. In the impdp log, Data Pump reports the wait as ORA-39171: Job is experiencing a resumable wait, and the follow-on message describes the cause.

Key Points to Remember

  • Always monitor actively. A suspension may not be obvious from the impdp session — check DBA_RESUMABLE or the alert log regularly.
  • IMP_FULL_DATABASE users get resumable waits automatically. The RESUMABLE privilege is for ALTER SESSION ENABLE RESUMABLE in ordinary SQL sessions.
  • JOB_NAME is your tracking label. Choose a clear, unique job name so you can find your job easily in DBA_RESUMABLE.
  • The 7200-second (2 hour) RESUMABLE_TIMEOUT default belongs to the original imp utility. impdp ignores that parameter, and the Data Pump documentation does not state a timeout for its resumable waits.
  • After fixing the problem (for example, adding space to a tablespace), the suspended job resumes automatically — no manual restart needed.
  • USER_RESUMABLE is the same view scoped to the current user — useful for non-DBA users checking their own jobs.

Practical Example

Suppose your import is named PROD_IMPORT_2026. You would start the import like this:

1impdp system/password \
2  directory=DATA_PUMP_DIR \
3  dumpfile=export.dmp \
4  logfile=import.log \
5  job_name=PROD_IMPORT_2026

For a Data Pump job, the NAME shown in DBA_RESUMABLE comes from the job name (JOB_NAME, or the default SYS_IMPORT_FULL_01 style name). Then you watch the status with:

1select name, status, suspend_time
2from   dba_resumable
3where  name like '%PROD_IMPORT_2026%';

If you see STATUS = SUSPENDED, run the error query:

1select error_msg
2from   dba_resumable
3where  name like '%PROD_IMPORT_2026%';

Fix the reported issue (such as extending a tablespace), and the import continues on its own.

References

Posts in this series