Check suspended session with dba_resumable view

Check stuck job with resume time or error detail with dba_resumable view

DBA_RESUMABLE view will help to see the all suspended session and cause of error. If we will fixed it within timeout period then suspended session will resume the work automatically.

Example: During any job, tablespace space issue occurred, you can see that session waiting for tablespace issue resolved in DBA_RESUMABLE view.

Check the view for suspended session

col sql_text for a30
col error_msg for a30
select user_id, session_id, status,
 start_time, timeout, 
suspend_time, sql_text, error_number,
 error_msg
from dba_resumable;

Note: Column Details:
Timeout: Timeout of the resumable statement after specific time.
Start_time: of the resumable statement.
Suspended_time: Last time the resumable statement was suspended.
RESUME_TIME: Last time the suspended resumable statement was resumed
ERROR_NUMBER: Error code of the last correctable error. When STATUS is set to RUNNING, its value will be 0.
ERROR_MSG: Error message corresponding to ERROR_NUMBER. It will be NULL when ERROR_NUMBER is 0.
STATUS: it will tell about current status of resumable job: RUNNING, SUSPENDED, TIMEOUT, ERROR, ABORTED

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.