Oracle Resumable Import Parameters: Monitor and Manage DBA_RESUMABLE

Oracle Resumable Import Parameters: Monitor and Manage 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 Import Parameters

To enable this feature on your impdp command, you use three parameters together:

1resumable=y
2resumable_name=<name to appear in dba_resumable>
3resumable_timeout=<timeout in seconds>

The resumable_timeout value controls how long Oracle holds the job in a suspended state before it finally times out and aborts. If you set this to a high value (for example, 7200 for two hours), you have more time to fix a full tablespace before the job fails.

Grant the RESUMABLE Privilege

If you are running the import as a user other than SYS or SYSTEM, that user must be granted the RESUMABLE system privilege.

1grant resumable to <user>;

Without this privilege, the user's session cannot control its own resumable state. The RESUMABLE system privilege specifically enables resumable space allocation for the grantee.

Monitoring with DBA_RESUMABLE

Oracle does not always print a visible message on your screen when an import job becomes suspended. You must actively monitor either the alert log or the DBA_RESUMABLE view 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:

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 '&resumable_name'
6/

The ERROR_MSG column in DBA_RESUMABLE holds the full error message that caused the suspension. A common cause is ORA-39171: Job is experiencing a resumable wait, which typically means a tablespace is full.

Key Points to Remember

• Always monitor actively. Oracle may not display a suspension message on screen — check DBA_RESUMABLE or the alert log regularly.
• Non-SYS/SYSTEM users need the privilege. Run grant resumable to <user> before starting the import.
• resumable_name is your tracking label. Choose a clear, unique name so you can find your job easily in DBA_RESUMABLE.
• resumable_timeout has a default of 7200 seconds (2 hours) in Data Pump, even if you do not set it explicitly — impdp enables resumable mode by default.
• 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  resumable=y \
6  resumable_name=PROD_IMPORT_2026 \
7  resumable_timeout=14400

Then you watch the status with:

1select name, status, suspend_time
2from   dba_resumable
3where  name = '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

• Oracle Data Pump Import Utility — Official Documentation (Oracle 19c) — Full reference for all impdp parameters and usage.
• Parameters Available in Oracle Data Pump Import Command-Line Mode — Detailed list of every Data Pump Import command-line parameter including RESUMABLE, RESUMABLE_NAME, and RESUMABLE_TIMEOUT.
• DBA_RESUMABLE — Oracle 19c Reference — Column definitions and description of the DBA_RESUMABLE data dictionary view.
• DBA_RESUMABLE — Oracle 10g Reference (Classic) — Original detailed column reference for DBA_RESUMABLE, including ERROR_MSG and ERROR_NUMBER.
• Managing Resumable Space Allocation — Oracle DBA Guide 11g — Administrator's guide explaining resumable space allocation, how to enable/disable it, and how to use DBA_RESUMABLE for monitoring.
• GRANT Statement — Oracle SQL Reference — Official reference for the GRANT statement, including granting the RESUMABLE system privilege.
• Impdp Resumable Mystery — GotoDBA — Practical deep-dive into how impdp handles the RESUMABLE_TIMEOUT parameter and unexpected behavior when the value is set to 0.
• Resumable — Oracle Scratchpad (Jonathan Lewis) — Expert-level explanation of the resumable privilege, session control, and how resumable_timeout interacts at the system and session level.
• About RESUMABLE, RESUMABLE_NAME Parameter — Oracle FAQ Forums — Community discussion clarifying the purpose of RESUMABLE, RESUMABLE_NAME, and RESUMABLE_TIMEOUT parameters in Data Pump.
• USER_RESUMABLE — Oracle 10g Reference — Description of the USER_RESUMABLE view, the user-scoped counterpart to DBA_RESUMABLE.