【AWR】Oracle批量生成awr报告脚本

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

我们在运维Oracle数据库时，有时候会对比Oracle 的awr报告，那么如何批量生成报告呢，下面几个脚本以供参考。
#### sql脚本,注意时间格式
``` sql
select
'spool awr_'||dbid||'_'||instance_number||'_'||b_snap_id||'_'||e_snap_id||'.html'
||chr(10)||
'select output from table(dbms_workload_repository.awr_report_html('||dbid||', '||instance_number||','||b_snap_id||','||e_snap_id||'));'
||chr(10)||
'spool off'  
from 
(select dbid,instance_number,snap_id b_snap_id,lead(snap_id,1,0) over(partition by instance_number order by snap_id) e_snap_id
from dba_hist_snapshot
where begin_interval_time>=trunc(sysdate-1)
and begin_interval_time<=trunc(sysdate))
where e_snap_id!=0;
```
#### sh脚本，注意脚本开头时间
``` shell
#!/bin/sh
# set the environment
source /home/oracle/.bash_profile
CONNECTSTR=" / as sysdba"
BEGIN_TIME="20211012_09:00:00"
END_TIME="20211012_12:00:00"
 
 
function Dbid {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off
  select DBID from v\$database;
  exit
EOF
}
 
Instnum() {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off
  select instance_number from v\$instance;
  exit
EOF
}
 
Instname() {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off
  select instance_name from v\$instance;
  exit
EOF
}
 
function Snap_id_like_time {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off;
  select SNAP_ID from dba_hist_snapshot
   where to_char(end_interval_time,'hh24') in (9,10,11,12,14,15,17)
     and trunc(end_interval_time,'mi')> trunc(sysdate-1)
     and instance_number in (select instance_number from v\$instance)
   order by snap_id;
  exit
EOF
}
 
function Snap_id_between_time {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off;
  select SNAP_ID from dba_hist_snapshot
   where trunc(end_interval_time,'hh')>= trunc(to_date('$BEGIN_TIME','YYYYMMDD_HH24:MI:SS'),'hh')
     and trunc(end_interval_time,'hh')<= trunc(to_date('$END_TIME','YYYYMMDD_HH24:MI:SS'),'hh')
     and instance_number in (select instance_number from v\$instance)
   order by snap_id;
  exit
EOF
}
 
function Snap_id_between_id {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off;
  select SNAP_ID from dba_hist_snapshot
   where snap_id>= to_number($BEGIN_ID)
     and snap_id<= to_number($END_ID)
     and instance_number in (select instance_number from v\$instance)
   order by snap_id;
  exit
EOF
}
 
 
echo "==========++++++++++++++===========";
 
CMDPM=`echo $1 | awk '{print tolower($1)}'`
case $CMDPM in
  bi | -bi | byid)
    SNAP_ID=`Snap_id_between_id`
    ;;
  bt | -bt | bytime)
    SNAP_ID=`Snap_id_between_time`
    ;;
  lt | -lt | liketime)
    SNAP_ID=`Snap_id_like_time`
    ;;
  *)
    #SNAP_ID=`Snap_id_like_time`
    echo "please usage like : $0 -bt(bytime)"
	;;
esac
 
 
echo "$SNAP_ID";
echo "==========++++++++++++++===========";
 
 
#Lftp to sftpServer .lftp Just fo Linux.
function Lftp_awr_report {
  cd .
  for VAR in *.htm*
   do
     lftp -u ${FTPUSER},${FTPPASS} sftp://${FTPSERVERIP}<<EOF
     cd /u01/docdata/olm/xh/121.160
     put ${VAR}
     bye
EOF
done;
}
 
#FTP to ftpServer  
function Ftp_awr_report {
  cd /tmp
  HOSTNAME=`hostname`;
  LOCALDIR="olm/xh/`grep ${HOSTNAME} /etc/hosts|awk '{print $1;}'|head -1`";
  ftp -n ${FTPSERVERIP} <<EOF
  passive
  prompt
  user ${FTPUSER} ${FTPPASS}
  cd ${LOCALDIR}
  binary
  mput *.html
  ascii
  mput out222*.log
  bye
EOF
}
 
function Create_awr_report {
  for snap_id_line in $SNAP_ID ; do
    bid="$eid"
    eid="$snap_id_line"
    if [ "$bid" != "" -a "$eid" != "" ] ; then
      sqlplus -S $CONNECTSTR <<EOF
        set echo off;
        set veri off;
        set feedback off;
        set termout on;
        set heading off;
        set trimspool on;
        set linesize 1500;
        set termout off;
        column report_name new_value report_name noprint;
        select name1 || name2 as report_name
        from (select a.snap_id as begin_snap_id,a.end_interval_time as begin_time,
                     to_char(a.end_interval_time, 'yyyymmdd_') ||
                     '`Instname`_awr'||to_char(a.end_interval_time, '_hh24')||
                     to_char(a.end_interval_time, 'mi') || '-' as name1
                from dba_hist_snapshot a
               where a.snap_id = $bid
                 and a.instance_number = `Instnum`) t1,
             (select b.snap_id as end_snap_id,b.end_interval_time as end_time,
                     to_char(b.end_interval_time, 'hh24') ||
                     to_char(b.end_interval_time, 'mi') || '.' || 'html ' name2
                from dba_hist_snapshot b
               where b.snap_id = $eid
                 and b.instance_number = `Instnum`) t2
       where rownum < 2
         and end_snap_id - begin_snap_id < 3
         and end_time-begin_time<INTERVAL '2' HOUR;
        set termout off;
        spool &report_name;
        select output from TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(`Dbid`,`Instnum`,$bid, $eid,0 ));
        spool off;
        set termout on;
        clear columns sql;
        ttitle off;
        btitle off;
        repfooter off;
      exit
EOF
    fi
  done
}
 
Create_awr_report;
#Ftp_awr_report;
```
> 内容整理自网络，如有侵权，请联系

编辑推荐：

返回列表

【AWR】Oracle批量生成awr报告脚本

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推