Oracle Database Health Check Script: 25 Checks in One HTML Report



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.

In short: run one script as SYSDBA, get ORACLE_DATABASE_INFO.html, open it in a browser or attach it to an email. Tested on Oracle 19c single instance and RAC.

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

AreaChecksWhy it matters
OverviewReport date, instance & database details, total DB sizeConfirms which DB and nodes you are looking at (all RAC instances via GV$INSTANCE)
StorageTablespace usage, temp usage, schema sizes, tables > 10 GB, top 10 tables & indexesSpace trends and growth hotspots
Memory & CPUCPU cores/sockets, physical RAM, SGA used/free, PGA target & limitRight-sizing and memory pressure
SessionsActive/inactive count, sessions & processes vs limit, session I/O, idle sessionsConnection leaks, approaching PROCESSES limit
LocksLocked objects with owning sessionBlocking issues during batch/COB
SQL performanceTop 5 SQL by buffer gets per execution, SQL Monitor historyExpensive SQL to tune first
Redo & archiveRedo groups, members, threads, archive statusLog switch and archiving health
BackupRMAN job history for 180 days (full, incremental, archivelog, controlfile pieces)Proof that backups are running and succeeding
MaintenanceInvalid objects, stale statistics, indexes analyzed in 5 days, expired/locked usersHousekeeping 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

  1. Save the script below as oracle_db_health_check.sql on the database server.
  2. Connect and run it:
    $ sqlplus / as sysdba
    SQL> @oracle_db_health_check.sql
  3. 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_UTILIZATION close to LIMIT_VALUE means you have already hit the ceiling once.
  • Redo logs: Several groups in ACTIVE status or ARCHIVED = NO on old groups points to slow archiving or checkpoints.
  • RMAN: Any FAILED or COMPLETED WITH ERRORS jobs, 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

About the KILL SESSION commands: sections 14 and 15 only generate ALTER SYSTEM KILL SESSION statements, they do not run them. An inactive session is not always a problem (connection pools keep idle sessions on purpose). Always confirm with the application owner before killing anything. On RAC, add @inst_id or run it on the right node.
  • 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