SCRIPT】Oracle巡检报告html格式样例脚本，带趋势图

来源：这里教程网时间：2026-03-03 17:16:44 作者：

巡检样例如 http://blog.itpub.net/29487349/viewspace-2845000/
巡检脚本如下，主要用于参考，可根据自身需求，整理其他。
``` sql
-- Copyright (c) 2021-2022 . Firsouler All rights reserved.
define fileName=Oracle_DB_Check
COLUMN spool_time NEW_VALUE _spool_time NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') spool_time FROM dual;
COLUMN hostname NEW_VALUE _host_name NOPRINT
select host_name hostname from v$instance;
COLUMN instance_name NEW_VALUE _SID NOPRINT
select instance_name from v$instance;
define pfileName=&_SID._&_spool_time.
prompt
prompt Oracle DB Health Check Starting...
prompt

define reportHeader="<font size=+3 color=darkgreen><b>Oracle Database Check Result</b></font>"
set termout       off
set echo          off
set feedback      off
set verify        off
set wrap          on
set trimspool     on
set escape        on
set pagesize 50000
set long     20000
set lines 2000
set numw 16
set serveroutput on size  unlimited format wrapped
col error format a30
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool &FileName._&_host_name._&_SID._&_spool_time..html
prompt &reportHeader
prompt <!DOCTYPE html>
prompt <html>
prompt <head>
prompt <title>Oracle check </title>
prompt <style type="text/css">
prompt a.awr {font:bold 10pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}
prompt li.awr {font: 12pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;margin-left:24pt;}
prompt h1.awr {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
prompt h4.awr {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
prompt h5.awr {font:bold 16pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;margin-left:2pt;}
prompt h6.awr {font:bold 12pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
prompt th.awrnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;padding-left:4px; padding-right:4px;padding-bottom:2px}
prompt th.awrbg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}
prompt td.awrnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}
prompt td.awrc    {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
prompt th         {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}
prompt table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
prompt table.tdiff {  border_collapse: collapse; }
prompt body {padding: 0 5%;width: 90%;}
prompt  </style>
prompt      
prompt  </head>
prompt  <body style="width:90%;">
prompt  <div id="main_report" style="width:100%;height:200px;">
prompt  
prompt <div id="left" style="width:25%;float:left;">
prompt  <h5 class="awr"> 1. Database base info </h5>
prompt  <li class="awr"> 1.1 Check Time </strong>
prompt  <li class="awr"> 1.2 DB info </strong>
prompt  <li class="awr"> 1.3 Instance info </strong>
prompt  <li class="awr"> 1.4 Data increase stat </strong>
prompt  <li class="awr"> 1.5 PDB info </strong>
prompt  <h5 class="awr"> 2. Tablespace info</h5>
prompt  <li class="awr"> 2.1 Tablespace user_top20</strong>
prompt  <li class="awr"> 2.2 Tablespace all </strong>
prompt 
prompt <div id="centor1" style="width:25%;float:left;">
prompt  <h5 class="awr"> 3. Redo(arch) info </h5>
prompt  <li class="awr"> 3.1 Redo log info </strong>
prompt  <li class="awr"> 3.2 arch info</strong>
prompt 
prompt <div id="centor2" style="width:25%;float:left;">
prompt  <h5 class="awr"> 4. Performance info </h5>
prompt  <li class="awr"> 4.1 User session info </strong>
prompt  <li class="awr"> 4.2 Transactions info </strong>
prompt  <li class="awr"> 4.3 Dbtime info </strong>
prompt  <li class="awr"> 4.4 Wait event </strong>
prompt 
prompt <div id="right" style="width:25%;float:left;">
prompt  <h5 class="awr"> 5. Log info </h5>
prompt  <li class="awr"> 5.1 Alert_log info </strong>
prompt 
prompt  
prompt  
SET MARKUP HTML ON preformat off entmap off
prompt 
prompt <p><h6 class="awr">1.1 Check Time</h6></p>
select sysdate as chk_date,(select banner from v$version) as db_version from dual;
prompt 
prompt <p><h6 class="awr">1.2 DB info</h6></p>
col PLATFORM_NAME for a20
col FORCE_LOG for a9
select cdb,name db_name,
to_char(created,'yyyy-mm-dd') db_created,
LOG_MODE,
OPEN_MODE,
FORCE_LOGGING FORCE_LOG,
PLATFORM_NAME,
supplemental_log_data_min,supplemental_log_data_all,flashback_on,
(select round(sum(bytes/1024/1024/1024),2) from cdb_segments) as db_size_GB 
from v$database;
prompt 
prompt <p><h6 class="awr">1.3.1 Instance info </h6></p>
col host_name for a15
select INST_ID,instance_name,host_name,
to_char(startup_time,'yyyy/mm/dd hh24:mi:ss'),
status
from gv$instance;
prompt <p><h6 class="awr">1.3.2 Portion parameter info </h6></p>
select name,round(value/1024/1024/1024,2)||'GB' as value from v$parameter where name in ('sga_max_size','memory_max_target','pga_aggregate_target','pga_aggregate_target')
union all
select name,value from v$parameter where name in ('db_files','audit_trail');
prompt 
prompt <p><h6 class="awr">1.4 data increase stat</h6></p>
prompt      <div id="db_dbsize" style="width: 90%;height:450px;">
prompt      
prompt 
prompt <p><h6 class="awr">1.5 PDB info</h6></p>
col name for a15
col open_mode for a10
select name,open_mode,to_char(open_time,'yyyy/mm/dd') open_time,to_char(CREATION_TIME,'yyyy/mm/dd') CREATE_TIME,round(TOTAL_SIZE/1024/1024/1024,2) as "disk_size(GB)",
(select pdb_size  from 
(select con_id,round(sum(bytes/1024/1024/1024),2) pdb_size from cdb_segments group by con_id) t where p.con_id=t.con_id) as "pdb_size(GB)"
from v$pdbs p order by open_time;
prompt 
prompt <p><h6 class="awr">2.1 Tablespace user_top20</h6></p>
prompt      <div id="db_tbssize" style="width: 90%;height:450px;">
prompt      
prompt 
prompt <p><h6 class="awr">2.2 Tablespace all</h6></p>
select c.name name,f.tablespace_name tablespace_name,
round((d.sumbytes/1024/1024/1024),2) total_without_extend_GB,
round(((d.sumbytes+d.extend_bytes)/1024/1024/1024),2) total_with_extend_GB,
round((f.sumbytes+d.Extend_bytes)/1024/1024/1024,2) free_with_extend_GB,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_GB,
round((d.sumbytes-f.sumbytes)*100/(d.sumbytes+d.extend_bytes),2) used_percent
from (select con_id,tablespace_name,sum(bytes) sumbytes from cdb_free_space group by con_id,tablespace_name) f, 
(select con_id,tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from
 (select  con_id,nvl(case  when autoextensible ='YES' then (case when (maxbytes-bytes)>=0 then (maxbytes-bytes) end) end,0) Extend_bytes
,tablespace_name,bytes  from cdb_data_files) aa group by con_id,tablespace_name) d,v$containers c
where  (f.con_id=d.con_id and f.tablespace_name=d.tablespace_name) and f.con_id=c.con_id
order by  name,used_percent desc;
prompt 
prompt <p><h6 class="awr">3.1 Redo log info</h6></p>
select thread#, GROUP#,SEQUENCE#,BYTES/1024/1024,STATUS,FIRST_TIME from v$log;
select * from v$logfile;
prompt 
prompt <p><h6 class="awr">3.2 arch info</h6></p>
prompt      <div id="db_archivelog" style="width: 90%;height:450px;">
prompt      
prompt 
prompt <p><h6 class="awr">4.1 Session info </h6></p>
prompt      <div id="db_pdb_session" style="width: 90%;height:450px;">
prompt      
prompt 
prompt <p><h6 class="awr">4.2 Transactions info  </h6></p>
prompt      <div id="db_trans" style="width: 90%;height:450px;">
prompt      
prompt 
prompt <p><h6 class="awr">4.3 Dbtime info </h6></p>
prompt      <div id="db_cpumain" style="width: 90%;height:450px;">
prompt      
prompt 
prompt <p><h6 class="awr" >4.4 Wait event  </h6></p>
prompt      <div id="db_for_wait_class" style="width: 90%;height:450px;">
prompt

prompt 
prompt <p><h6 class="awr">5.1 Alert_log(last 300) info  </h6></p>

set markup html off
exec dbms_output.put_line('</body>');
exec dbms_output.put_line('</html>');
spool off 
!sed -i '/<br>/d' &&FileName._&&_host_name._&&_SID._&&_spool_time..html
column alertfile new_value alertfiledr
select value as alertfile from V$DIAG_INFO where name='Diag Trace';
!tail -500 &&alertfiledr/alert_&&_SID..log | sed 's/^/<p>/g' >> &&FileName._&&_host_name._&&_SID._&&_spool_time..html
quit
```