Oracle Automation

Capture instance status and role information as a repeatable database check.

SELECT
    instance_name,
    status,
    database_status
FROM v$instance;

Capture database open mode, role and logging configuration for operational validation.

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

Identify tablespaces approaching a high utilization 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 invalid objects that should be investigated after changes or deployments.

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

Review scheduled jobs that are disabled or currently broken.

SELECT
    owner,
    job_name,
    enabled,
    state,
    failure_count
FROM dba_scheduler_jobs
WHERE enabled = 'FALSE'
   OR state = 'BROKEN'
ORDER BY owner, job_name;

Create a compact result set that can be written to an automation log.

SELECT
    SYSTIMESTAMP AS check_time,
    SYS_CONTEXT('USERENV', 'DB_NAME') AS database_name,
    SYS_CONTEXT('USERENV', 'INSTANCE_NAME') AS instance_name
FROM dual;