A curated set of read-only SQL*Plus scripts for the daily operational health checking of Oracle databases — availability, recoverability, space, and performance — built and used by a Senior Oracle DBA supporting production enterprise systems.
The scripts are deliberately dependency-free: plain SQL*Plus (or SQLcl), no installed objects, no third-party tooling. Drop them on any host with an Oracle client and a read-only monitoring account and you have a consistent morning health-check routine in minutes.
Most production incidents announce themselves hours in advance — a tablespace nearing its ceiling, a Fast Recovery Area filling up, a batch job quietly failing, a session holding a lock. This repository turns those early signals into a repeatable 10–15 minute daily pass so problems are caught and fixed before users or applications feel them.
Each script answers one operational question, prints clean fixed-width output suitable for spooling, and (where an action might be needed) includes the remediation as a commented template rather than executing anything. The companion docs turn the scripts into a documented procedure with troubleshooting playbooks.
| Oracle version | Status |
|---|---|
| Oracle Database 19c Enterprise Edition | ✅ Supported |
| Oracle Database 21c Enterprise Edition | ✅ Supported |
The scripts use V$/GV$ and DBA data-dictionary views that are stable across 19c and 21c,
and are RAC-aware (GV$ views return one row per instance) and multitenant-aware
(run inside a PDB for that PDB, or in CDB$ROOT for the root container). They will
generally run on 12c/18c as well, but 19c and 21c are the tested targets.
All scripts, sample outputs, and documentation in this repository are fully sanitized
and generic. They contain no real hostnames, IP addresses, SIDs, service names,
credentials, schema contents, or employer/company data of any kind. Every value shown
in the sample_outputs/ folder is fictional, generated purely to illustrate what a
report looks like and how to interpret it.
These are reference scripts for portfolio and educational use. Review and test any script in a non-production environment, and apply any commented action step under your own change-control process, before using it against a production database.
| Script | What it answers | Key views |
|---|---|---|
db_status.sql |
Is the instance OPEN, in the right role/mode, in ARCHIVELOG, and how long has it been up? | V$INSTANCE, V$DATABASE, GV$INSTANCE |
archive_log_status.sql |
Is archiving healthy and is the FRA at risk of filling (ORA-00257)? | V$DATABASE, V$ARCHIVE_DEST_STATUS, V$RECOVERY_FILE_DEST, V$ARCHIVED_LOG |
tablespace_usage.sql |
Which permanent tablespaces are nearing their autoextend ceiling? | DBA_DATA_FILES, DBA_FREE_SPACE |
temp_usage.sql |
Is TEMP under pressure, and which session/SQL is consuming it? | V$TEMP_SPACE_HEADER, GV$SORT_USAGE, GV$SESSION |
database_size.sql |
What is the total footprint and where is the growth? | DBA_DATA_FILES, DBA_SEGMENTS, V$LOG |
invalid_objects.sql |
Are there invalid objects (e.g., after a deploy/patch) to recompile? | DBA_OBJECTS |
failed_jobs.sql |
Did any scheduled jobs fail or break? | DBA_SCHEDULER_JOB_RUN_DETAILS, DBA_SCHEDULER_JOBS, DBA_JOBS |
session_count.sql |
How close are we to session/process limits; any pool leak? | GV$RESOURCE_LIMIT, GV$SESSION |
blocking_sessions.sql |
Is anything blocked, by whom, on which object? | GV$SESSION, GV$LOCKED_OBJECT, DBA_OBJECTS |
top_sessions.sql |
Which active sessions are heaviest right now and on what wait? | GV$SESSION, GV$SESS_IO, GV$SQL |
A sanitized, annotated sample run of every script lives in
sample_outputs/.
Prerequisites: an Oracle client (SQL*Plus or SQLcl) and a database account with
SELECT_CATALOG_ROLE (or explicit SELECT on the views above). A dedicated read-only
monitoring account is recommended; do not run routine checks as SYS.
Connect and run a single script:
sqlplus monitor_user@//your_host:1521/your_service
SQL> @scripts/db_status.sql
Run the whole set and spool a log (create a small driver that calls each script in the order of the daily procedure):
sqlplus -s monitor_user@DEMO_TNS @run_all.sql
Tunable thresholds are exposed as DEFINEs at the top of the relevant scripts:
| Script | Parameter | Default |
|---|---|---|
tablespace_usage.sql |
warn_threshold |
85 (% of max) |
failed_jobs.sql |
lookback_days |
7 |
Full instructions — including RAC/multitenant notes and a ready-to-adapt driver script —
are in docs/How to Use Scripts.md.
Example from tablespace_usage.sql (fictional data):
===================== PERMANENT TABLESPACE USAGE ========================
TABLESPACE_NAME ALLOC_GB USED_GB FREE_GB MAX_GB PCT_USED_ALLOC PCT_USED_OF_MAX STATUS_F
---------------------- ----------- ------------ ------------ ------------ -------------- --------------- --------
APP_DATA 120.00 111.40 8.60 128.00 92.8 87.0 ALERT
APP_INDEX 64.00 49.20 14.80 96.00 76.9 51.3 OK
USERS 10.00 3.10 6.90 32.00 31.0 9.7 OK
Example from blocking_sessions.sql (fictional data):
===================== BLOCKER -> WAITER CHAINS ==========================
WAITER WAITER_USER BLOCKER BLOCKER_USER WAIT_EVENT WAIT_SECS
------------------ ---------------- ------------------ ---------------- ---------------------------- ---------
1:418,2207 APP_USER 1:330,1145 BATCH_USER enq: TX - row lock contention 742
Each file in sample_outputs/ ends with a short "Read:" note
explaining what a DBA should conclude and do next.
The scripts in this repository describe what to check. This section shows them in use — real terminal output from a sanitized demo database (ORADEMO), captured the way a DBA actually reads it on a morning pass. Every value is fictional; there are no real hostnames, SIDs, schemas, or company data. What matters here isn't the SQL — it's the interpretation: how an experienced operator turns a screen of output into a decision, and catches the one early signal that prevents tomorrow's incident.
The five checks below are sequenced the way a daily health pass runs: confirm the database is available and recoverable first, then look for space pressure, contention, recoverability risk, and finally what's working the database hardest right now.
Problem demonstrated. The first question of every morning: is the database actually up, in the right role and mode, and is it protected? On a cluster, that question has to be answered for every node, not just the one you happened to connect to.
What an experienced DBA concludes. This is a clean, recoverable, highly available primary. The instance is OPEN, in ARCHIVELOG with FORCE_LOGGING on — which together mean online backups and Data Guard are viable, not just hoped for. The 15-day uptime confirms there was no unplanned restart overnight, and both RAC instances (ORADEMO1, ORADEMO2) report OPEN, so the cluster is whole. Nothing to action — but every one of those facts was verified, not assumed.
Troubleshooting takeaway. Check availability and recoverability before anything else; a fast database that can't be recovered is a liability. On RAC, always read the cluster-wide instance list — a single-instance query can show "all good" while a second node sits down.
Problem demonstrated. Tablespaces growing toward their autoextend ceiling. Left unseen, this ends as ORA-01653 ("unable to extend") — an application outage that always seems to land mid-business-day.
What an experienced DBA concludes. The script's threshold logic has already done the triage: APP_DATA is the single ALERT at 87% of its maximum size — not its current allocation, which is the distinction that catches people out. The other tablespaces are comfortable. The action is clear and unhurried: add a datafile or raise MAXSIZE now, on your own schedule, and separately ask why APP_DATA is growing.
Troubleshooting takeaway. Measure against the autoextend ceiling, not current fill — a tablespace at 50% allocated can still be one big insert from the wall. Acting at ~85% on a routine pass turns a potential outage into a two-minute maintenance task.
Problem demonstrated. "The application is hung." Two APP_USER sessions have been waiting 12+ minutes on enq: TX - row lock contention, and the locked-object detail ties them to a single table, MAIL_EVENT_FACT.
What an experienced DBA concludes. Both waiters point at the same blocker — session 330,1145 (BATCH_USER) — which holds an Exclusive lock and is not itself waiting. That makes it the root blocker: a batch job that updated rows and never committed. The script deliberately prints the kill command as a commented template, not an action — because the right next step is to confirm with the batch owner first, then commit or kill 330,1145 to release the chain.
Troubleshooting takeaway. Always resolve to the root blocker before touching anything; killing the visible waiters just moves the pain. And a read-only health check should surface the remediation, never auto-execute it — judgment and change control belong between detection and action.
Problem demonstrated. The slow-motion outage that catches teams off guard: the Fast Recovery Area fills, the archiver stalls, and the database hangs with ORA-00257. This check exists to see it coming days early.
What an experienced DBA concludes. Recoverability is intact: ARCHIVELOG is on, and both archive destinations are VALID — including the standby, which confirms redo is shipping for DR. The FRA is at 59% with 22% reclaimable, so there's real headroom. The 7-day generation trend (~25 GB weekdays, ~10 GB weekends) tracks business load, which is exactly what healthy looks like. The note says the quiet part out loud: if FRA PCT_USED ever approaches 100, the archiver stops and the database hangs.
Troubleshooting takeaway. Watch FRA percent-used and reclaimable together — high usage with high reclaimable is fine; high usage with nothing reclaimable means your archive backups have quietly stopped. Trend archive generation too: a sudden jump is often the first sign of an unexpected workload change.
Problem demonstrated. "The database feels slow." Without data that's a guess. This check turns it into a ranked, evidence-based answer: which active sessions are consuming the most I/O, on what wait, running which SQL.
What an experienced DBA concludes. The top consumer is BATCH_USER doing a full table scan of mail_event_fact — 48 million logical reads on db file scattered read, the classic signature of a missing index or a bad FULL hint. The wait-event rollup shows most sessions are on CPU (a busy but healthy system), and the two enq: TX waits are the same row-lock chain seen in check 3 — connecting the dots between the contention and the workload behind it. The output hands you the exact SQL_ID (8gk2yq3m1xr4a) to tune first.
Troubleshooting takeaway. Attribute load to a specific session and SQL_ID before tuning — "the database is slow" should always become "this statement is doing 48M reads." Capturing the SQL_ID live is what lets you pull its plan and fix the one thing that matters instead of guessing at ten.
All screenshots are fully sanitized and fictional. Names like
ORADEMO,APP_DATA,BATCH_USER, andMAIL_EVENT_FACT, along with every path, SQL_ID, and value, are illustrative demo data created for this portfolio — no production, employer, or confidential information is shown. Each capture mirrors the annotated output insample_outputs/, where every example ends with a "Read:" note explaining what to conclude and do next.
| Doc | Contents |
|---|---|
| Daily Health Check Procedure | The ordered 10-step morning routine, with "good vs red flag" for each check |
| How to Use Scripts | Prerequisites, connecting, running individually or as a set, RAC/PDB notes |
| Troubleshooting Guide | Symptom → cause → fix for the eight most common issues these checks surface |
oracle-health-checks/
├── README.md
├── LICENSE
├── .gitignore
├── scripts/ # the 10 read-only SQL health-check scripts
├── sample_outputs/ # fictional, sanitized example output for each script
├── docs/ # procedure, usage, and troubleshooting guides
└── screenshots/ # sanitized terminal captures — see "Operational Screenshots"
run_all.sqldriver + shell/PowerShell wrapper that spools a timestamped report and emails it, with cron / Task Scheduler examples.- AWR/ASH performance pack — top SQL by elapsed time, wait-event trend, and a segment-growth trend report for capacity planning.
- HTML report output — render the daily run as a single styled HTML page for sharing with application teams and management.
- Data Guard health check — apply lag, transport lag, and gap detection for standby environments.
- Threshold config file — externalize all warning thresholds into one file instead
of per-script
DEFINEs. - CI lint — a lightweight GitHub Action that syntax-checks the SQL on each commit.
Released under the MIT License — free to use, adapt, and share with attribution.