Oracle批量导出AWR报告(2)

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"
    ;;
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`'||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;

国外哥们儿写的SQL参考：