Automation Diagnostics

Find scheduler jobs whose recent executions did not succeed.

SELECT
    owner,
    job_name,
    status,
    error#,
    actual_start_date
FROM dba_scheduler_job_run_details
WHERE status <> 'SUCCEEDED'
ORDER BY actual_start_date DESC
FETCH FIRST 30 ROWS ONLY;

Inspect scheduler job error details recorded for failed executions.

SELECT
    owner,
    job_name,
    status,
    error#,
    additional_info
FROM dba_scheduler_job_run_details
WHERE status <> 'SUCCEEDED'
ORDER BY actual_start_date DESC
FETCH FIRST 20 ROWS ONLY;

Review currently running scheduler jobs and their elapsed execution time.

SELECT
    owner,
    job_name,
    session_id,
    running_instance,
    elapsed_time
FROM dba_scheduler_running_jobs
ORDER BY elapsed_time DESC;

Inspect database sessions associated with currently running automated work.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.event,
    s.sql_id
FROM v$session s
WHERE s.program LIKE '%(J%'
ORDER BY s.sid;

Look for invalid objects that may indicate an unsuccessful deployment or maintenance task.

SELECT
    owner,
    object_type,
    object_name,
    status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

Confirm the database state while investigating an automation failure.

SELECT
    name,
    open_mode,
    database_role,
    log_mode
FROM v$database;