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

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle