Check RESUMABLE_TIMEOUT and RESUMABLE Status in V$SESSION

calendar Jun 19, 2026 / · 6 min read · Oracle DBA V$SESSION Resumable Space Allocation Oracle Administration Session Management Space Management Oracle Performance Dynamic Performance Views  ·
Share on: linkedin copy

Check RESUMABLE_TIMEOUT and RESUMABLE Status in V$SESSION

Purpose

V$SESSION carries two columns that tell you whether a session can survive running out of space: RESUMABLE and the resumable timeout that governs it. When resumable space allocation is enabled, a statement that hits a space error — a full tablespace, a maxed-out datafile, an exceeded user quota — does not fail immediately. Oracle suspends it, raises a RESUMABLE alert, and waits. If a DBA frees the space inside the timeout window, the statement wakes up and continues from where it stopped. If the window expires first, the statement fails with the original space error.

The columns in V$SESSION let you see this state in real time. RESUMABLE shows whether the session is operating in resumable mode at all. The associated timeout shows how many seconds a suspended statement will wait before giving up. Reading them is the first diagnostic step when a long-running load or index build appears to hang instead of failing — it may not be hung, it may be suspended and waiting for you to act.

This post shows how to query those columns, how to join V$SESSION to the dedicated DBA_RESUMABLE view for the full picture, and how the RESUMABLE_TIMEOUT initialization parameter and ALTER SESSION settings interact to set the window.

Code

 1-- Sessions and their resumable mode + timeout window
 2SELECT sid,
 3       serial#,
 4       username,
 5       resumable,
 6       resumable_timeout
 7FROM   v$session
 8WHERE  username IS NOT NULL
 9ORDER  BY username, sid;
10
11-- Statements suspended RIGHT NOW, with the reason and how long
12SELECT r.session_id,
13       r.username,
14       r.status,
15       r.timeout,
16       r.suspend_time,
17       r.error_msg
18FROM   dba_resumable r
19WHERE  r.status = 'SUSPENDED';
20
21-- The instance-wide default timeout (0 = resumable off by default)
22SHOW PARAMETER resumable_timeout

Code Breakdown

The V$SESSION query

The first query reads the resumable state straight from the session list.

  • resumable — a flag showing whether the session is in resumable mode. A session enters resumable mode through ALTER SESSION ENABLE RESUMABLE, through a logon trigger, or through a non-zero RESUMABLE_TIMEOUT parameter at instance level.
  • resumable_timeout — the number of seconds a suspended statement in that session will wait for space before it fails. A value of 0 means the session is not resumable.
  • WHERE username IS NOT NULL filters out the background processes (SMON, PMON, DBWn, and the rest), which carry no real user and are not interesting here.

The DBA_RESUMABLE query

V$SESSION tells you the capacity to suspend; DBA_RESUMABLE tells you what is actually suspended. The second query lists every session currently in the resumable system, with its STATUS (SUSPENDED, NORMAL, or TIMEOUT), the remaining TIMEOUT in seconds, the SUSPEND_TIME when it stopped, and ERROR_MSG — the exact space error that triggered the suspension, such as ORA-01653: unable to extend table. Filtering on status = 'SUSPENDED' gives you the live work queue: the statements waiting for you to add space.

The parameter check

SHOW PARAMETER resumable_timeout reports the instance default. When this parameter is 0 (the shipped default), sessions are not resumable unless they opt in explicitly. Set it to a positive number of seconds and every session inherits resumable behaviour with that timeout. The session-level ALTER SESSION SET RESUMABLE_TIMEOUT or ENABLE RESUMABLE TIMEOUT n overrides the instance value for that session.

Key Points

  • RESUMABLE = 0 means not resumable. A session with a zero timeout fails on the first space error instead of suspending.
  • Two layers set the window. The RESUMABLE_TIMEOUT instance parameter sets the default; ALTER SESSION ENABLE RESUMABLE TIMEOUT n overrides it per session.
  • V_$SESSION is the same view. Scripts and older references sometimes query the V_$SESSION synonym instead of V$SESSION; both resolve to the same fixed table, so the columns are identical.
  • Suspension is silent to the application. A suspended statement does not return an error to the client during the wait — it simply blocks, which is why it can look like a hang.
  • DBA_RESUMABLE carries the reason. Only that view shows ERROR_MSG, the precise space error that needs fixing; V$SESSION shows the mode, not the cause.

Insights and Best Practices

Enable resumable mode for long batch loads

The classic use case is an overnight data load or a large index build that must not fail at 90% complete because a tablespace ran 100 MB short. Enabling resumable mode on that session turns a hard failure into a pause: the load suspends, an alert fires, the on-call DBA adds a datafile, and the load resumes without re-running from the start. Set the timeout long enough to give a human time to react — a few hours is common for unattended overnight work.

Wire an AFTER SUSPEND trigger to alert automatically

Oracle fires a database event when a statement suspends. An AFTER SUSPEND ON DATABASE trigger can capture that event and send an email or page, so the team learns about the space shortage the moment it happens rather than when the timeout expires. Pair the trigger with the DBA_RESUMABLE query above to log which statement stopped and why.

Watch the timeout, not just the flag

A session can be resumable yet still fail if the timeout is too short for anyone to act. When you see suspended statements timing out in DBA_RESUMABLE with status = 'TIMEOUT', the fix is usually a longer window, not more space — the space was added, just not in time. Review the TIMEOUT value against your real alerting and response latency.

Keep RESUMABLE_TIMEOUT off by default at instance level

Making every session resumable instance-wide can mask space problems: statements quietly suspend and resume, and the underlying tablespace shortage never gets attention. Many shops leave the instance parameter at 0 and enable resumable mode only for the specific batch jobs that benefit, so routine space errors still surface loudly.

When to Use This

  • Diagnosing a long-running load or index build that appears hung but may be suspended.
  • Confirming whether a critical batch session is actually running in resumable mode before it starts.
  • Auditing which sessions are resumable and what timeout windows are in force across the instance.
  • Building a monitoring query that flags suspended statements waiting on space.
  • Verifying that an ALTER SESSION ENABLE RESUMABLE or logon trigger took effect.

Troubleshooting Common Issues

If RESUMABLE_TIMEOUT shows 0 for a session you expected to be resumable, the ENABLE RESUMABLE step did not run — check the logon trigger or add ALTER SESSION ENABLE RESUMABLE TIMEOUT 7200 at the start of the job. If a statement is suspended but no one was alerted, there is no AFTER SUSPEND trigger in place; add one so future suspensions page the team. If DBA_RESUMABLE shows a status of TIMEOUT, the statement already failed — the window expired before space was freed, so increase the timeout or speed up the space-allocation response. If you query V$SESSION and the resumable columns are missing, you are likely on a very old release or querying a restricted view; confirm you have SELECT on the V_$SESSION fixed table through a role such as SELECT_CATALOG_ROLE.

References

Posts in this series