server2$[/home/oracle]mkdir awrreport

create or replace directory DIR_AWRREPORT as '/home/oracle/awrreport';

grant read,write on directory DIR_AWRREPORT to mydba;                    -- mydba为具有dba权限的用户

grant select on v_$database to mydba;

grant select on dba_hist_snapshot to mydba;

grant execute on sys.dbms_workload_repository to mydba;

CREATE OR REPLACE PROCEDURE auto_awrreport

AS

/*

用途 ：自动生成AWR报告

创建人 ：gegeman

创建日期：2017-12-08

*/

start_snap number;

end_snap number;

rpt_interval number := 24; --报告间隔(小时)

start_time varchar2(14);

end_time varchar2(14);

awr_file utl_file.file_type;

v_dbid number;

v_dbname varchar2(20);

begin

--确定数据库名称与id

select dbid,name into v_dbid,v_dbname from v$database;

---查询起始的快照id

select max(snap_id) into end_snap from dba_hist_snapshot;

start_snap := end_snap-rpt_interval;

---格式化快照时间

select to_char(end_interval_time-rpt_interval/24, 'yyyymmddhh24'),to_char(end_interval_time, 'yyyymmddhh24')

into 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',lower(v_dbname)||'_1_'||start_time||'_'||end_time||'.html', 'a',32767);

for awr_info in (select output from table(dbms_workload_repository.awr_report_html(v_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',lower(v_dbname)||'_2_'||start_time||'_'||end_time||'.html', 'a',32767);

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

UTL_FILE.put_line(awr_file,awr_info.output);

end loop;

utl_file.fclose(awr_file);

--全局报告

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

for awr_info in (select output from table(dbms_workload_repository.awr_global_report_html(l_dbid => v_dbid,l_inst_num => '',l_bid => start_snap,l_eid => end_snap,l_options => 0))) loop

UTL_FILE.put_line(awr_file,awr_info.output);

end loop;

utl_file.fclose(awr_file);

end auto_awrreport;

grant execute on mydba.auto_awrreport to mydba;

declare

job1 number;

begin

sys.dbms_job.submit(job => job1,

what => 'auto_awrreport;',

next_date => sysdate,

interval => 'TRUNC(SYSDATE+1) + 30/(24*60)',

instance => 2

);

end;