[20140805]批量生成awr报表.txt
--工作需要生成1天awr的报表,我记得以前在itpub上有人问过类似的问题,我当时也google,找到链接
--再翻了一下帖子,找到如下:
http://www.dbaglobe.com/2011/09/how-to-generate-awr-report-in-batch.html
--注意:我修改了少量的细节:日期选择中文的格式,partition by加入了s.startup_time
--这样避免重启数据库的问题.
---------------------------------------------------------------------
-- script awrrpt_donghua.sql
---------------------------------------------------------------------
set serveroutput on
spool master_awr_control.sql
declare
cursor c is
select to_char(s.startup_time,'yyyy/mm/dd hh24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number,s.startup_time order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat
, to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s
, dba_hist_database_instance di
,gv$instance i
,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time between '&1' and '&2' -- 采用两个参数加入时间间隔.
order by di.db_name, i.instance_name, s.snap_id;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('spool '||c1.inst_name||'_'
||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||
c1.instance_number||','||
c1.begin_snap_id||','||
c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/
spool off;
set heading off
set pages 50000
set linesize 1500
set trimspool on
set trimout on
set term off
set verify off;
set feedback off;
@master_awr_control.sql
exit
--我个人喜欢设置参数
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
这样执行:
@awrrpt_donghua.sql '2014-08-03' '2014-08-04'
--我自己重新改写一些如下:
---------------------------------------------------------------------
-- script awrrpt_batch.sql
---------------------------------------------------------------------
set heading off
--set pages 50000
set linesize 1500
set trimspool on
set trimout on
set term off
set verify off;
set feedback off;
spool /tmp/master_awr_control.sql
with a as (
select to_char(s.startup_time,'yyyy/mm/dd hh24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number,s.startup_time order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat
, to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s
, dba_hist_database_instance di
,gv$instance i
,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time between '&1' and '&2' -- 采用两个参数加入时间间隔.
order by di.db_name, i.instance_name, s.snap_id)
select 'spool '||a.inst_name||'_'||a.begin_snap_id||'_'||a.end_snap_id||'_'||a.beginsnapdat||'_'||a.endsnapdat||'.html'||chr(10)||
'select output from table(dbms_workload_repository.awr_report_html( '||a.dbid||','||a.instance_number||','||a.begin_snap_id||','||a.end_snap_id||',0 ));'||chr(10)||
'spool off'||chr(10) cc
from a where begin_snap_id > 0;
spool off
@/tmp/master_awr_control.sql
exit
