Linux查看存储过程快照,通过存储过程自动生成AWR报告

以下是存储过程内容：

CREATE OR REPLACE PROCEDURE sp_auto_awrreport(reporttime varchar2 default null,

rpt_interval number default 2)

AS

----用途：自动生成AWR报告

----创建人：cavlin

----创建日期：20130210

----reporttime 报告结束时间

----rpt_interval 报告间隔 1:30分钟,2:60分钟

start_snap number;

end_snap number;

start_time varchar2(14);

end_time varchar2(14);

awr_file utl_file.file_type;

n_dbid  number;

BEGIN

if reporttime is null then

---查询最大的快照id

select max(snap_id) into end_snap from dba_hist_snapshot;

start_snap:=end_snap-rpt_interval;

else

---取输入时间对应的snap_id

select snap_id

into end_snap

from dba_hist_snapshot

where to_char(end_interval_time, 'yyyymmddhh24mi') = reporttime

and instance_number = 1;

start_snap:=end_snap-rpt_interval;

end if;

---格式化快照时间

select dbid,to_char(end_interval_time-rpt_interval/48, 'yyyymmddhh24mi'),to_char(end_interval_time, 'yyyymmddhh24mi')

into n_dbid,start_time,end_time

from dba_hist_snapshot

where snap_id = end_snap and instance_number=1;

---实例1

awr_file:=utl_file.fopen('DIR_AWRREPORT','AWR_NODE1_'||start_time||'_'||end_time||'.html', 'a',32767);

for awr_info in (select output from table(dbms_workload_repository.awr_report_html(n_dbid,1,start_snap,end_snap,0))) loop

UTL_FILE.put_line(awr_file,awr_info.output);

end loop;

utl_file.fclose(awr_file);

---实例2

awr_file:=utl_file.fopen('DIR_AWRREPORT','AWR_NODE2_'||start_time||'_'||end_time||'.html', 'a',32767);

for awr_info in (select output from table(dbms_workload_repository.awr_report_html(n_dbid,2,start_snap,end_snap,0))) loop

UTL_FILE.put_line(awr_file,awr_info.output);

end loop;

utl_file.fclose(awr_file);

END sp_auto_awrreport;

可以直接在数据库中调用，也可以通过shell调用：

cat  /opt/cron/autoawr.sh

#!/bin/bash

sqlplus -s / as sysdba << EOF

exec sp_auto_awrreport;

exit

EOF

find /opt/awrreport/  -mtime +7 -name "*.html" -exec rm -f {} \;

通过crontab调用：

$ crontab -l

5 22 * * * sh /opt/cron/autoawr.sh

这样每天22点05分就会在/opt/awrreport目录自动生成21：00--22：00的awr报告了。