Monitoring Automation

Identify sessions currently active or waiting so workload changes can be surfaced.

SELECT
    sid,
    serial#,
    username,
    status,
    event,
    wait_class,
    seconds_in_wait
FROM v$session
WHERE type = 'USER'
ORDER BY seconds_in_wait DESC;

Identify tablespaces whose used percentage has reached a monitoring threshold.

SELECT
    tablespace_name,
    ROUND(used_percent, 2) AS used_percent
FROM dba_tablespace_usage_metrics
WHERE used_percent >= 80
ORDER BY used_percent DESC;

Find sessions currently blocking other sessions.

SELECT
    sid,
    serial#,
    username,
    blocking_session,
    event
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session, sid;

Detect invalid objects that may indicate deployment or maintenance issues.

SELECT
    owner,
    object_type,
    COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;

Surface recent scheduler failures for operational follow-up.

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 20 ROWS ONLY;

Return a simple status value that can be consumed by a script or monitoring workflow.

SELECT
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM dba_tablespace_usage_metrics
            WHERE used_percent >= 90
        )
        THEN 'ALERT'
        ELSE 'OK'
    END AS status
FROM dual;