[20140805]批量生成awr報表.txt

[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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1245322/，如需轉載，請註明出處，否則將追究法律責任。