Every DBA gets the same request sooner or later: "Can you quickly check the database and tell us if everything looks fine?" Clicking through OEM or running a dozen separate queries is slow, and the results are hard to share. In this post I share the SQL*Plus script I use to produce a single HTML health check report covering storage, memory, sessions, locks, top SQL, redo, RMAN backups and statistics, in one run.
Why use a health check script?
- Consistency: the same 25 checks every time, so nothing gets missed.
- Speed: a full snapshot in under a minute on most databases.
- Shareable: SQL*Plus SET MARKUP HTML turns the output into a clean HTML report that managers and app teams can read.
- Baseline: keep one report per week and you have an easy growth and trend history.
What the report covers
| Area | Checks | Why it matters |
|---|---|---|
| Overview | Report date, instance & database details, total DB size | Confirms which DB and nodes you are looking at (all RAC instances via GV$INSTANCE) |
| Storage | Tablespace usage, temp usage, schema sizes, tables > 10 GB, top 10 tables & indexes | Space trends and growth hotspots |
| Memory & CPU | CPU cores/sockets, physical RAM, SGA used/free, PGA target & limit | Right-sizing and memory pressure |
| Sessions | Active/inactive count, sessions & processes vs limit, session I/O, idle sessions | Connection leaks, approaching PROCESSES limit |
| Locks | Locked objects with owning session | Blocking issues during batch/COB |
| SQL performance | Top 5 SQL by buffer gets per execution, SQL Monitor history | Expensive SQL to tune first |
| Redo & archive | Redo groups, members, threads, archive status | Log switch and archiving health |
| Backup | RMAN job history for 180 days (full, incremental, archivelog, controlfile pieces) | Proof that backups are running and succeeding |
| Maintenance | Invalid objects, stale statistics, indexes analyzed in 5 days, expired/locked users | Housekeeping gaps |
Prerequisites
- Oracle Database 12.2 or later (uses ORACLE_MAINTAINED in DBA_USERS). Written and tested on 19c.
- A SYSDBA connection, or a user with SELECT ANY DICTIONARY.
- Licensing: section 17 reads V$SQL_MONITOR, which requires the Diagnostics and Tuning Pack. Remove that block if the pack is not licensed.
How to run it
- Save the script below as oracle_db_health_check.sql on the database server.
- Connect and run it:
$ sqlplus / as sysdba SQL> @oracle_db_health_check.sql
- Open ORACLE_DATABASE_INFO.html from the same directory in any browser.
The script
Each section is numbered and commented so you can remove the checks you do not need.
-- =============================================================================
-- Oracle Database Health Check Report (HTML)
-- Author : Tamilselvan Perumal | techstaradmin.blogspot.com
-- Tested : Oracle 19c (single instance and RAC)
-- Usage : sqlplus / as sysdba @oracle_db_health_check.sql
-- Output : ORACLE_DATABASE_INFO.html in the current directory
-- =============================================================================
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
SET FEEDBACK OFF
SET HEADING ON
SET PAGESIZE 1000
SET LINESIZE 1000
SPOOL ORACLE_DATABASE_INFO.html
PROMPT ORACLE DATABASE SERVER REPORT
PROMPT =============================
-- ---------------------------------------------------------------------------
-- 1. Report date
-- ---------------------------------------------------------------------------
PROMPT REPORT DATE
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') "REPORT RUN DATE" FROM dual;
-- ---------------------------------------------------------------------------
-- 2. Database and instance details (one row per RAC instance)
-- ---------------------------------------------------------------------------
PROMPT DATABASE DETAILS
SELECT gv.instance_name, gv.host_name, gv.version, gv.status,
v.name, v.created, v.log_mode, v.checkpoint_change#, v.archive_change#
FROM gv$instance gv
CROSS JOIN v$database v;
-- ---------------------------------------------------------------------------
-- 3. Total database size (data + temp + redo + controlfiles)
-- ---------------------------------------------------------------------------
PROMPT DATABASE SIZE
SELECT ROUND(
(SELECT SUM(bytes) FROM dba_data_files) +
(SELECT NVL(SUM(bytes),0) FROM dba_temp_files) +
(SELECT SUM(bytes * members) FROM v$log) +
(SELECT SUM(block_size * file_size_blks) FROM v$controlfile)
) / 1024/1024/1024, 2) "Size in GB"
FROM dual;
-- ---------------------------------------------------------------------------
-- 4. Tablespace usage (permanent + temporary)
-- ---------------------------------------------------------------------------
PROMPT TABLESPACE INFO
SELECT df.tablespace_name,
MAX(df.autoextensible) auto_ext,
ROUND(df.maxbytes / 1048576, 2) max_ts_size_mb,
ROUND(df.bytes / 1048576, 2) curr_ts_size_mb,
ROUND((df.bytes - NVL(SUM(fs.bytes),0)) / 1048576, 2) used_ts_size_mb,
ROUND(NVL(SUM(fs.bytes),0) / 1048576, 2) free_ts_size_mb
FROM dba_free_space fs,
(SELECT tablespace_name, SUM(bytes) bytes,
SUM(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes,
MAX(autoextensible) autoextensible
FROM dba_data_files GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name,
MAX(df.autoextensible),
ROUND(df.maxbytes / 1048576, 2),
ROUND(df.bytes / 1048576, 2),
ROUND(NVL(SUM(fs.bytes),0) / 1048576, 2),
ROUND((df.bytes - NVL(SUM(fs.bytes),0)) / 1048576, 2)
FROM (SELECT tablespace_name, bytes_used bytes FROM v$temp_space_header) fs,
(SELECT tablespace_name, SUM(bytes) bytes,
SUM(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes,
MAX(autoextensible) autoextensible
FROM dba_temp_files GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;
-- ---------------------------------------------------------------------------
-- 5. Temp tablespace usage (live sort segments)
-- ---------------------------------------------------------------------------
PROMPT TEMP TABLESPACE USAGE
SELECT a.tablespace_name tablespace, d.mb_total,
SUM(a.used_blocks * d.block_size)/1048576 mb_used,
d.mb_total - SUM(a.used_blocks * d.block_size)/1048576 mb_free
FROM v$sort_segment a,
(SELECT b.name, c.block_size, SUM(c.bytes)/1048576 mb_total
FROM v$tablespace b, v$tempfile c
WHERE b.ts# = c.ts#
GROUP BY b.name, c.block_size) d
WHERE a.tablespace_name = d.name
GROUP BY a.tablespace_name, d.mb_total;
-- ---------------------------------------------------------------------------
-- 6. Session summary (Active / Inactive / Total)
-- ---------------------------------------------------------------------------
PROMPT SESSION INFORMATION
SELECT NVL(INITCAP(status),'Total') status, COUNT(*) count
FROM v$session
GROUP BY ROLLUP(status)
ORDER BY GROUPING(status), status;
-- ---------------------------------------------------------------------------
-- 7. Server CPU and memory
-- ---------------------------------------------------------------------------
PROMPT CPU AND MEMORY OF DATABASE SERVER
SELECT stat_name, TO_CHAR(value) value
FROM v$osstat
WHERE stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
UNION ALL
SELECT stat_name, ROUND(value/1024/1024/1024,2) || ' GB'
FROM v$osstat
WHERE stat_name = 'PHYSICAL_MEMORY_BYTES';
-- ---------------------------------------------------------------------------
-- 8. SGA usage
-- ---------------------------------------------------------------------------
PROMPT CURRENT SGA USAGE
SELECT ROUND(SUM(CASE WHEN name <> 'free memory' THEN bytes END)/1048576,2) used_mb,
ROUND(SUM(CASE WHEN name = 'free memory' THEN bytes END)/1048576,2) free_mb,
ROUND(SUM(bytes)/1048576,2) total_mb
FROM v$sgastat;
-- ---------------------------------------------------------------------------
-- 9. PGA target and hard limit
-- ---------------------------------------------------------------------------
PROMPT PGA TARGET AND HARD LIMIT
SELECT name, TO_CHAR(value/1048576,'999,999,999.99') || ' MB' value_in_mb
FROM v$parameter
WHERE name IN ('pga_aggregate_target','pga_aggregate_limit');
-- ---------------------------------------------------------------------------
-- 10. Sessions / processes vs limits
-- ---------------------------------------------------------------------------
PROMPT RESOURCE UTILIZATION
SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit
WHERE resource_name IN ('sessions','processes');
-- ---------------------------------------------------------------------------
-- 11. Invalid objects
-- ---------------------------------------------------------------------------
PROMPT INVALID OBJECTS
SELECT owner, object_type, COUNT(*) "INVALID OBJECTS"
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY owner;
-- ---------------------------------------------------------------------------
-- 12. Redo log groups and archive status
-- ---------------------------------------------------------------------------
PROMPT REDO LOGS AND ARCHIVE STATUS
SELECT vlf.member "MEMBER_NAME", vl.group# "GROUP", vl.thread# "THREAD",
vl.status, vl.archived, vl.bytes/1048576 "SIZE (MB)", vl.sequence#
FROM v$logfile vlf, v$log vl
WHERE vlf.group# = vl.group#
ORDER BY vl.thread#, vl.group#, vlf.member;
-- ---------------------------------------------------------------------------
-- 13. Session I/O (top readers last)
-- ---------------------------------------------------------------------------
PROMPT SESSION I/O BY USERS
SELECT NVL(ses.username,'ORACLE PROC') username, ses.osuser, ses.process pid,
ses.sid, ses.serial#, sio.physical_reads, sio.block_gets,
sio.consistent_gets, sio.block_changes, sio.consistent_changes
FROM v$session ses, v$sess_io sio
WHERE ses.sid = sio.sid
ORDER BY sio.physical_reads DESC;
-- ---------------------------------------------------------------------------
-- 14. Inactive user sessions (generates KILL commands - review before use!)
-- ---------------------------------------------------------------------------
PROMPT INACTIVE USER SESSIONS - REVIEW BEFORE RUNNING ANY KILL COMMAND
SELECT username, osuser, machine, program,
ROUND(last_call_et/60) idle_minutes,
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' kill_cmd
FROM v$session
WHERE status = 'INACTIVE'
AND type = 'USER'
AND username IS NOT NULL
AND sid <> SYS_CONTEXT('USERENV','SID')
ORDER BY last_call_et DESC;
-- ---------------------------------------------------------------------------
-- 15. Locked objects
-- ---------------------------------------------------------------------------
PROMPT DATABASE LOCK INFORMATION
SELECT c.sid, c.serial#, c.osuser, c.username, a.owner, a.object_name,
a.object_id, b.locked_mode,
'ALTER SYSTEM KILL SESSION ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE;' kill_cmd
FROM dba_objects a, v$locked_object b, v$session c
WHERE a.object_id = b.object_id
AND c.sid = b.session_id;
-- ---------------------------------------------------------------------------
-- 16. Top 5 SQL by logical reads per execution
-- ---------------------------------------------------------------------------
PROMPT TOP 5 SQL BY BUFFER GETS PER EXECUTION
SELECT * FROM (
SELECT b.username, a.sql_id, a.buffer_gets, a.disk_reads, a.executions,
TRUNC(a.buffer_gets / a.executions) buffer_get_per_exec,
ROUND(a.cpu_time / 1e6, 2) cpu_sec,
ROUND(a.elapsed_time / 1e6, 2) elapsed_sec,
a.rows_processed,
100 - ROUND(100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
a.module,
SUBSTR(a.sql_text,1,200) sql_text
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND b.oracle_maintained = 'N'
AND a.buffer_gets > 10000
AND a.executions > 0
ORDER BY buffer_get_per_exec DESC)
WHERE ROWNUM <= 5;
-- ---------------------------------------------------------------------------
-- 17. SQL Monitor report (long-running / parallel SQL)
-- Note: V$SQL_MONITOR needs the Diagnostics + Tuning Pack licence
-- ---------------------------------------------------------------------------
PROMPT SQL PERFORMANCE MONITORING REPORT
SELECT status, sql_id, username,
TO_CHAR(sql_exec_start,'DD-MON-YYYY HH24:MI:SS') start_time,
ROUND(elapsed_time/1e6,2) "Elapsed (s)",
ROUND(cpu_time/1e6,2) "CPU (s)",
buffer_gets "Buffer Gets",
ROUND(physical_read_bytes/1048576,2) "Phys reads (MB)",
ROUND(physical_write_bytes/1048576,2) "Phys writes (MB)",
SUBSTR(sql_text,1,200) sql_text
FROM v$sql_monitor
WHERE username <> 'SYS'
ORDER BY sql_exec_start DESC;
-- ---------------------------------------------------------------------------
-- 18. RMAN backup history (last 180 days)
-- ---------------------------------------------------------------------------
PROMPT RMAN BACKUP DETAILS - LAST 180 DAYS
SELECT j.session_recid, j.session_stamp,
TO_CHAR(j.start_time,'YYYY-MM-DD HH24:MI:SS') start_time,
TO_CHAR(j.end_time, 'YYYY-MM-DD HH24:MI:SS') end_time,
ROUND(j.output_bytes/1048576) output_mbytes, j.status, j.input_type,
TO_CHAR(j.start_time,'Day') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
FROM v$rman_backup_job_details j
LEFT JOIN (SELECT d.session_recid, d.session_stamp,
SUM(CASE WHEN d.controlfile_included = 'YES' THEN d.pieces ELSE 0 END) cf,
SUM(CASE WHEN d.controlfile_included = 'NO'
AND d.backup_type||d.incremental_level = 'D' THEN d.pieces ELSE 0 END) df,
SUM(CASE WHEN d.backup_type||d.incremental_level = 'D0' THEN d.pieces ELSE 0 END) i0,
SUM(CASE WHEN d.backup_type||d.incremental_level = 'I1' THEN d.pieces ELSE 0 END) i1,
SUM(CASE WHEN d.backup_type = 'L' THEN d.pieces ELSE 0 END) l
FROM v$backup_set_details d
JOIN v$backup_set s ON s.set_stamp = d.set_stamp AND s.set_count = d.set_count
WHERE s.input_file_scan_only = 'NO'
GROUP BY d.session_recid, d.session_stamp) x
ON x.session_recid = j.session_recid AND x.session_stamp = j.session_stamp
LEFT JOIN (SELECT o.session_recid, o.session_stamp, MIN(inst_id) inst_id
FROM gv$rman_output o
GROUP BY o.session_recid, o.session_stamp) ro
ON ro.session_recid = j.session_recid AND ro.session_stamp = j.session_stamp
WHERE j.start_time > TRUNC(SYSDATE) - 180
ORDER BY j.start_time;
-- ---------------------------------------------------------------------------
-- 19. Index statistics gathered in last 5 days
-- ---------------------------------------------------------------------------
PROMPT INDEXES ANALYZED IN LAST 5 DAYS
SELECT owner, COUNT(*) indexes_analyzed,
MIN(last_analyzed) first_analyzed, MAX(last_analyzed) last_analyzed
FROM dba_indexes
WHERE last_analyzed > SYSDATE - 5
GROUP BY owner
ORDER BY owner;
-- ---------------------------------------------------------------------------
-- 20. Expired / locked users (last 7 days)
-- ---------------------------------------------------------------------------
PROMPT DB USERS EXPIRED OR LOCKED IN LAST 7 DAYS
SELECT username, account_status, created, last_login, expiry_date, default_tablespace
FROM dba_users
WHERE account_status <> 'OPEN'
AND (expiry_date > SYSDATE - 7 OR lock_date > SYSDATE - 7);
-- ---------------------------------------------------------------------------
-- 21. Schema sizes
-- ---------------------------------------------------------------------------
PROMPT SCHEMA SIZES
SELECT owner, ROUND(SUM(bytes)/1024/1024/1024,2) "Size in GB"
FROM dba_segments
GROUP BY owner
ORDER BY 2 DESC;
-- ---------------------------------------------------------------------------
-- 22. Tables larger than 10 GB
-- ---------------------------------------------------------------------------
PROMPT LARGE TABLES (> 10 GB)
SELECT owner, segment_name, segment_type,
ROUND(SUM(bytes)/1024/1024/1024,2) "SIZE (GB)"
FROM dba_segments
WHERE segment_type LIKE 'TABLE%'
GROUP BY owner, segment_name, segment_type
HAVING SUM(bytes) > 10*1024*1024*1024
ORDER BY 4 DESC;
-- ---------------------------------------------------------------------------
-- 23. Stale statistics by schema
-- ---------------------------------------------------------------------------
PROMPT STALE STATISTICS COUNT BY SCHEMA
SELECT owner, COUNT(*) stale_cnt
FROM dba_tab_statistics
WHERE stale_stats = 'YES'
GROUP BY owner
ORDER BY 2 DESC;
-- ---------------------------------------------------------------------------
-- 24. Top 10 tables (application schemas)
-- ---------------------------------------------------------------------------
PROMPT TOP 10 TABLES
SELECT * FROM (
SELECT s.owner, s.segment_name, s.segment_type, ROUND(SUM(s.bytes)/1048576) "SIZE MB"
FROM dba_segments s JOIN dba_users u ON u.username = s.owner
WHERE s.segment_type LIKE 'TABLE%'
AND s.segment_name NOT LIKE 'BIN$%'
AND u.oracle_maintained = 'N'
GROUP BY s.owner, s.segment_name, s.segment_type
ORDER BY 4 DESC)
WHERE ROWNUM <= 10;
-- ---------------------------------------------------------------------------
-- 25. Top 10 indexes (application schemas)
-- ---------------------------------------------------------------------------
PROMPT TOP 10 INDEXES
SELECT * FROM (
SELECT s.owner, s.segment_name, s.segment_type, ROUND(SUM(s.bytes)/1048576) "SIZE MB"
FROM dba_segments s JOIN dba_users u ON u.username = s.owner
WHERE s.segment_type LIKE 'INDEX%'
AND s.segment_name NOT LIKE 'BIN$%'
AND u.oracle_maintained = 'N'
GROUP BY s.owner, s.segment_name, s.segment_type
ORDER BY 4 DESC)
WHERE ROWNUM <= 10;
SPOOL OFF
SET MARKUP HTML OFF
PROMPT Report written to ORACLE_DATABASE_INFO.html
How to read the report
A few things I look at first:
- Tablespace: Used space above 85% of max size (not current size, since autoextend files can still grow).
- Processes / sessions:
MAX_UTILIZATIONclose toLIMIT_VALUEmeans you have already hit the ceiling once. - Redo logs: Several groups in
ACTIVEstatus orARCHIVED = NOon old groups points to slow archiving or checkpoints. - RMAN: Any
FAILEDorCOMPLETED WITH ERRORSjobs, and gaps in the daily pattern. - Stale stats: High counts on application schemas just before a batch window are a common cause of plan changes.
- Invalid objects: New invalid objects after a release or patch. Recompile with
utlrp.sql.
Important notes
- Most sections use V$ views, which show the current instance only. On RAC, run it on each node or switch the views to GV$.
- On very large databases, DBA_SEGMENTS queries can take a while. Run the report outside peak hours.
- Schedule it weekly with cron and keep the HTML files to build a simple trend history.
Conclusion
A single, repeatable health check script saves time, gives everyone the same view of the database, and makes it much easier to spot problems before users do. Feel free to take this script, adapt the thresholds to your environment, and extend it with your own checks.
If you have a check you always run that is missing here, share it in the comments and I will add it to the next version.
Tamilselvan Perumal · Oracle ACE Apprentice · Lead Product Engineer & Senior DBA
