做了一个存储过程，封装了awr的report的功能。
通过输入指定时间点和目录名称，就可以在指定的位置生成awr的html报告，对于RAC环境会同时生成所有节点的awr报告。
SQL> CREATE OR REPLACE PROCEDURE P_AWR_REPORT (P_BEGIN IN DATE, P_END IN DATE, D_DIR IN VARCHAR2) AS
2 TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
3 V_REPORT T_VARCHAR;
4 V_FILE UTL_FILE.FILE_TYPE;
5 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 V_INSTANCE T_NUM;
7 V_SNAP_BEG T_NUM;
8 V_SNAP_END T_NUM;
9 V_DBID NUMBER;
10 V_DBNAME V$DATABASE.NAME%TYPE;
11 BEGIN
12 SELECT DBID, NAME
13 INTO V_DBID, V_DBNAME
14 FROM V$DATABASE;
15
16 SELECT INSTANCE_NUMBER, MAX(SNAP_ID)
17 BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG
18 FROM DBA_HIST_SNAPSHOT
19 WHERE END_INTERVAL_TIME <= P_BEGIN
20 AND DBID = V_DBID
21 GROUP BY INSTANCE_NUMBER
22 ORDER BY INSTANCE_NUMBER;
23 SELECT DISTINCT INSTANCE_NUMBER,
24 FIRST_VALUE(SNAP_ID) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - P_BEGIN))
25 BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG
26 FROM DBA_HIST_SNAPSHOT
27 WHERE DBID = V_DBID
28 ORDER BY INSTANCE_NUMBER;
29
30
31 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
32 BEGIN
33 SELECT DISTINCT FIRST_VALUE(SNAP_ID) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - P_END))
34 INTO V_SNAP_END(I)
35 FROM DBA_HIST_SNAPSHOT
36 WHERE SNAP_ID > V_SNAP_BEG(I)
37 AND DBID = V_DBID
38 AND INSTANCE_NUMBER = V_INSTANCE(I);
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 V_INSTANCE.DELETE(I);
42 END;
43 END LOOP;
44
45 IF V_INSTANCE.COUNT = 1
46 THEN
47 V_FILE := UTL_FILE.FOPEN(
48 D_DIR,
49 'awr_' || V_DBNAME || '_' || V_INSTANCE(1) || '_' || V_SNAP_BEG(1) || '_' || V_SNAP_END(1) || '.html',
50 'w',
51 32767);
52
53 SELECT OUTPUT
54 BULK COLLECT INTO V_REPORT
55 FROM TABLE(
56 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
57 V_DBID,
58 V_INSTANCE(1),
59 V_SNAP_BEG(1),
60 V_SNAP_END(1),
61 0));
62 FOR I IN 1..V_REPORT.COUNT LOOP
63 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
64 END LOOP;
65 UTL_FILE.FCLOSE(V_FILE);
66 ELSE
67 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
68 V_FILE := UTL_FILE.FOPEN(
69 D_DIR,
70 'awr_' || V_DBNAME || '_' || V_INSTANCE(I) || '_' || V_SNAP_BEG(I) || '_' || V_SNAP_END(I) || '.html',
71 'w',
72 32767);
73
74 SELECT OUTPUT
75 BULK COLLECT INTO V_REPORT
76 FROM TABLE(
77 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
78 V_DBID,
79 V_INSTANCE(I),
80 V_SNAP_BEG(I),
81 V_SNAP_END(I),
82 0));
83 FOR I IN 1..V_REPORT.COUNT LOOP
84 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
85 END LOOP;
86 UTL_FILE.FCLOSE(V_FILE);
87 END LOOP;
88 END IF;
89 END;
90 /
过程已创建。
创建一个目录，就可以运行这个过程：
SQL> CREATE DIRECTORY D_OUTPUT AS 'D:\SQL';
目录已创建。
SQL> BEGIN
2 P_AWR_REPORT(TO_DATE('2010-12-31 10', 'YYYY-MM-DD HH24'),
3 TO_DATE('2010-12-31 11', 'YYYY-MM-DD HH24'),
4 'D_OUTPUT');
5 END;
6 /
PL/SQL 过程已成功完成。
检查对应的目录，可以看到，report文件已经创建：
D:\>cd sql
D:\sql>dir
驱动器 D 中的卷没有标签。
卷的序列号是 F4E9-218A
D:\sql 的目录
2011/01/04 23:40 .
2011/01/04 23:40 ..
2011/01/04 23:40 233,586 awr_YTK102_1_118_119.html
1 个文件 233,586 字节
2 个目录 8,983,412,736 可用字节
D:\sql>type awr_ytk102_1_118_119.html|more
AWR Report for DB: YTK102, Inst: ytk102, Snaps: 118-119</TITL<BR>E><style. type="text/css">body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-se<BR>rif;color:black; background:White;}<BR>pre.awr {font:8pt Courier;color:black; background:White;}h1.awr {font:bold 20<BR>pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border<BR>-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px<BR>0px;}<BR>h2.awr {font:bold 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;backgro<BR>und-color:White;margin-top:4pt; margin-bottom:0pt;}<BR>h3.awr {font:bold 16pt Arial,Helvetica,Geneva,sans-serif;color:#336699;backgroun<BR>d-color:White;margin-top:4pt; margin-bottom:0pt;}li.awr {font: 8pt Arial,Helveti<BR>ca,Geneva,sans-serif; color:black; background:White;}<BR>th.awrnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; backgr<BR>ound:White;padding-left:4px; padding-right:4px;padding-bottom:2px}th.awrbg {font<BR>:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;pad<BR>ding-left:4px; padding-right:4px;padding-bottom:2px}<BR>td.awrnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:Whit<BR>e;vertical-align:top;}<BR>td.awrc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#F<BR>FFFCC; vertical-align:top;}a.awr {font:bold 8pt Arial,Helvetica,sans-serif;color<BR>:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}<BR></style></HEAD><BODY class='awr'><BR>WARNING: Since the DB Time is less than one second, there was<BR> minimal foreground activity in the snapshot period.<BR>.<BR>.<BR>.</FONT></SPAN></P>
<P style="TEXT-INDENT: 21pt; MARGIN: 0cm 0cm 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 宋体; mso-ascii-theme-font: minor-fareast; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-theme-font: minor-fareast" lang=EN-US><FONT size=3> </FONT></SPAN></P>
<P style="TEXT-INDENT: 21pt; MARGIN: 0cm 0cm 0pt" class=MsoNormal><SPAN style="FONT-FAMILY: 宋体; mso-ascii-theme-font: minor-fareast; mso-fareast-font-family: 宋体; mso-fareast-theme-font: minor-fareast; mso-hansi-theme-font: minor-fareast" lang=EN-US><FONT size=3> </FONT></SPAN></P></DIV>
</div>
<p style="clear:both;"></p>
<p class="translate">
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-689953/
，如需转载，请注明出处，否则将追究法律责任。
</p>
<!--
-->
</div>
</div>
<div class="preview-footer clearfix">
<!--已点赞、收藏-->
<div class="icon-praise" id="praise" onclick="blogObj.praise($(this),'/praise/')"><span
class="text"></span><span class="num">0</span></div>
<div class="icon-collect" id="collect" onclick="blogObj.praise($(this),'/favour/')"><span
class="text"></span><span class="num">0</span></div>
<div class="share-group fr">
<div class="bdsharebuttonbox bdshare-button-style0-16" data-bd-bind="1522216707615">
<span class="fl">分享到：</span>
<a href="javascript:;" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a>
<a href="javascript:;" class="bds_sqq" data-cmd="sqq" title="分享到QQ好友"></a>
<a href="javascript:;" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间"></a>
<a href="javascript:;" class="bds_weixin" data-cmd="weixin" title="分享到微信"></a>
</div>
<script>
window._bd_share_config = {
"common": {
"bdSnsKey": {},
"bdText": "",
"bdDesc": "",
"bdMini": "",
"bdMiniList": false,
"bdPic": "",
"bdStyle": "0",
"bdSize": "32"
}, "share": {"bdSize": 16}
};
with (document)
0[(getElementsByTagName('head')[0] || body).appendChild(createElement('script')).src = '/static/api/js/share.js?v=89860593.js?cdnversion=' + ~(-new Date() / 36e5)];
if ($(window).width() < 1320) {
if ($(window).width() < 1200) {
$(".sideslip").css({
"left": "20px",
"margin-left": 0
})
} else {
$(".sideslip").css({
"margin-left": "-590px"
})
}
}</script>
</div>
</div>
<div class="article-context">
<div class="fl">
<span class="color77">上一篇：</span>
<a href="https://blog.itpub.net/4227/viewspace-689817/">ORA-600(krvxbpx20)错误</a>
</div>
<div class="fr">
<span class="color77">下一篇：</span>
<a href="https://blog.itpub.net/4227/viewspace-690002/">用存储过程封装awrrpt脚本（二）</a>
</div>
</div>
<input type="hidden" id="blogId" name="blogId" value="689953">
<!--评论开始-->
<div class="blog-comment">
<div class="new-comment">
<input type="hidden" id="hid" name="hid" value="689953">
<input type="hidden" name="_token" value="RvYZI0iGlpyzTYbhf49b0P9A0HrAe1MIRbKTQ0fw">
<a class="avatar" href="javascript:;">
<img src="/images/user_pic_default.png"></a>
<!--用户未登录-->
<div class="sign-container">
<span>请登录后发表评论</span>
<a class="btn btn-sign">
<a class="layui-btn ml30" onclick="blog.login(event)">登录</a>
</a>
</div>
<!--用户已登录-->
<textarea placeholder="请写下你的评论…" onfocus="blogObj.textareaFocus($(this),200)"
maxlength=200></textarea>
</div>
<div class="comment-list">
<div class="top-title"><span class="all-comment">全部评论</span> <span
class="colorbb ml20"></span>
</div>
<div class="comment-items" id="comment_items">
<script type="text/template" id='blog_template'>
<%for(var i=0;i
<items.length;i++){%>
<div class="comment-item">
<div class="author">
<a target="_blank" href="/<%=items[i].uid%>" class="avatar">
<img src="<%=items[i].headimg%>" alt="">
</a>
<div class="info">
<div class="name"><a target="_blank" href="/<%=items[i].uid%>"><%=items[i].username%></a>
<span class="fr reply-btn"
onclick="blogObj.newComment($(this),'<%=items[i].username%>',<%=items[i].id%>)">回复</span>
</div>
<div class="time colorbb"><%=items[i].createtime%></div>
</div>
</div>
<p class="comment-wrap"><%=items[i].content%></p>
<%if(items[i].items.items.length) { %>
<div class="sub-comment-list">
<%for(var j=0;j
<items
[i].items.items.length;j++){%>
<div class="sub-comment">
<div class="color77 time-reply">
<span class="time"><%=items[i].items.items[j].createtime%></span>
<span class="reply-btn fr"
onclick="blogObj.newComment($(this),'<%=items[i].items.items[j].username%>',<%=items[i].items.items[j].id%>)">回复</span>
</div>
<p><a href="/<%=items[i].uid%>"><%=items[i].items.items[j].username%></a> 回复 <a
href="/<%=items[i].touid%>"><%=items[i].items.items[j].tousername%></a>：
<span class="content"><%=items[i].items.items[j].content%></span></p>
</div>
<%}%>
<%if(items[i].items.total > 5) { %>
<div class="more-comment"><span class="sub-comment-count">还有<i class="count"><%=items[i].items.total-5%></i>条评论</span><span
class="more-comment-btn"
onclick=blogObj.loadSubComment($(this),<%=items[i].id%>) data-count=1 data-flag=true>点击查看</span>
</div>
<%}%>
</div>
<%}%>
</div>
<%}%>
</script>
</div>
</div>
</div>
<!--评论结束-->
</div>
<script>
</script>
<div class="fr w290">
<!--作者信息开始-->
<!--作者信息开始-->
<div class="author-info right-fixed ">
<div class="icon-expert"></div>
<div class="head-img">
<a href="https://blog.itpub.net/4227/"><img src="http://account.itpub.net/api/avatar.php?uid=4227" alt=""></a>
</div>
<div class="author-name"><a href="https://blog.itpub.net/4227/">yangtingkun</a></div>
<div class="author-intro">
暂无介绍
</div>
<p class="register-time"><span class="color77">注册时间：</span>2007-12-29</p>
<ul class="tree-list clearfix">
<li>
<div class="item-tt">博文量</div>
<a href="https://blog.itpub.net/4227/"><span class="item-num blognum">1964</span></a>
</li>
<li>
<div class="item-tt">访问量</div>
<div class="item-num blogviewnum">11075930</div>
</li>
</ul>
</div>
<!--作者信息结束-->
<script>
var data = {uid: 4227};
$._ajax({
url: '/getAuthorInfo/',
data: data,
type: 'get',
dataType: 'json',
success: function (data) {
if (data.code == 200) {
var data = data.data;
$(".blognum").text(data.blognum);
$(".blogviewnum").text(data.visitednum);
}
}
});
</script> <!--作者信息结束-->
<!--博文推荐开始-->
<div class="blog-choice right-fixed">
<h3 class="choice-title">最新文章</h3>
<ul class="newul">
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060809/"
class="clearfix">
20130725 上海OOW第四日
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060793/"
class="clearfix">
20130724 上海OOW第三日
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060792/"
class="clearfix">
20130723 上海OOW第二日
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060790/"
class="clearfix">
20130722 上海OOW开幕
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060789/"
class="clearfix">
20130721 OOW媒体发布会
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060788/"
class="clearfix">
密码延迟验证导致的系统HANG住
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060787/"
class="clearfix">
IP地址被清空导致实例重启
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060762/"
class="clearfix">
ORA-7445(kjbcrcomplete)错误
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060754/"
class="clearfix">
Streams AQ: qmn coordinator waiting for slave to start等待
</a>
</li>
<li class="new-item">
<a href="https://blog.itpub.net/4227/viewspace-1060746/"
class="clearfix">
ORA-600(13310)错误
</a>
</li>
<ul>
</div>
<!--大牛精选结束-->
</div>
</div>
<div class="icon-back-top" onclick="$('html,body').animate({scrollTop:0},'slow');"></div>
<!--main部分结束-->
<!--百度推送-->
<script>
(function () {
var bp = document.createElement('script');
var curProtocol = window.location.protocol.split(':')[0];
if (curProtocol === 'https') {
bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
} else {
bp.src = 'http://push.zhanzhang.baidu.com/push.js';
}
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(bp, s);
})();
</script>
<!--footer部分开始-->
<div class="blog-footer">
<div class="w1200 pr">
<div class="footer-links">
<a class="icon-blog icon-wx mr30">
<div class="wx-qrcode">
<img src="/images/qrcode.jpg" alt="">
</div>
</a>
<a href="https://weibo.com/itpub2001?from=myfollow_all" class="icon-blog icon-wb" target="_blank"></a>
</div>
<p class="footer-nav">
<a href="http://www.it168.com/bottomfile/it168.shtml" target="_blank">支持我们</a>
<a href="http://www.it168.com/bottomfile/tgzn.shtml" target="_blank">作者招募</a>
<a href="http://www.it168.com/bottomfile/sytk.shtml" target="_blank">用户协议</a>
<a href="//blog.itpub.net/31509949/viewspace-2157750/" target="_blank">FAQ</a>
<a href="http://edu.itpub.net/contactus.html" target="_blank">Contact Us</a>
<script src="https://s22.cnzz.com/z_stat.php?id=1274521965&web_id=1274521965" language="JavaScript"></script>
<a target="_blank" href="https://www.itpub.net/editorial-standards.html" rel="nofollow">编辑准则</a>
</p>
<p>北京盛拓优讯信息技术有限公司. 版权所有 <a style="color:#777777;" target="_blank" href="http://beian.miit.gov.cn">京ICP备16024965号-8</a> 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567
<a style="color:#777777;" target="_blank" href="http://www.it168.com/jubao.html"> 未成年人举报专区</a></p>
<p>广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员</p>
<p>违法和不良信息举报电话:18101376593</p>
</div>
</div>
<!--footer部分结束-->
<script>
var _hmt = _hmt || [];
(function() {
var hm = document.createElement("script");
hm.src = "https://hm.baidu.com/hm.js?5016281862f595e78ffa42f085ea0f49";
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(hm, s);
})();
</script>
<script>
var _hmt = _hmt || [];
(function() {
var hm = document.createElement("script");
hm.src = "https://hm.baidu.com/hm.js?540bee2c1a1b922df9e0a0b922440b73";
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(hm, s);
})();
</script>
<!-- END STAT PV -->
</body>
</html>