💡 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