Oracle自动生成html格式awr的报告
Oracle自动生成html格式awr的报告
--
-----------------------------------------------------------------------------------
-- File Name
-- -----------------------------------------------------------------------------------
-- File Name : http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql
-- Author : DR Timothy S Hall
-- Description : Generates AWR reports for all snapsots between the specified start and end point.
-- Requirements : Access to the v$ views, UTL_FILE and DBMS_WORKLOAD_REPOSITORY packages.
-- Call Syntax : Create the directory with the appropriate path.
-- Adjust the start and end snapshots as required.
-- @generate_multiple_awr_reports.sql
-- Last Modified: 02/08/2007
-- -----------------------------------------------------------------------------------
CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/';
DECLARE
-- Adjust before use.
l_snap_start NUMBER := 1;
l_snap_end NUMBER := 10;
l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR';
l_last_snap NUMBER := NULL;
l_dbid v$database.dbid%TYPE;
l_instance_number v$instance.instance_number%TYPE;
l_file UTL_FILE.file_type;
l_file_name VARCHAR(50);
BEGIN
SELECT dbid
INTO l_dbid
FROM v$database;
SELECT instance_number
INTO l_instance_number
FROM v$instance;
FOR cur_snap IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE instance_number = l_instance_number
AND snap_id BETWEEN l_snap_start AND l_snap_end
ORDER BY snap_id)
LOOP
IF l_last_snap IS NOT NULL THEN
l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w', 32767);
FOR cur_rep IN (SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
LOOP
UTL_FILE.put_line(l_file, cur_rep.output);
END LOOP;
UTL_FILE.fclose(l_file);
END IF;
l_last_snap := cur_snap.snap_id;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
--
Author : DR Timothy S Hall
-- Description : Generates AWR reports
for all snapsots between the specified start and end point.
-- Requirements : Access to the v$ views, UTL_FILE and DBMS_WORKLOAD_REPOSITORY
packages.
-- Call Syntax : Create the directory
with the appropriate path.
-- Adjust the start and
end snapshots as required.
--
@generate_multiple_awr_reports.sql
-- Last Modified: 02/08/2007
--
-----------------------------------------------------------------------------------
CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/';
DECLARE
-- Adjust before use.
l_snap_start NUMBER := 1;
l_snap_end NUMBER := 10;
l_dir VARCHAR2(50) :=
'AWR_REPORTS_DIR';
l_last_snap NUMBER := NULL;
l_dbid v$database.dbid%TYPE;
l_instance_number v$instance.instance_number%TYPE;
l_file UTL_FILE.file_type;
l_file_name VARCHAR(50);
BEGIN
SELECT dbid
INTO
l_dbid
FROM
v$database;
SELECT
instance_number
INTO
l_instance_number
FROM
v$instance;
FOR cur_snap IN (SELECT
snap_id
FROM dba_hist_snapshot
WHERE instance_number =
l_instance_number
AND snap_id BETWEEN l_snap_start AND
l_snap_end
ORDER BY
snap_id)
LOOP
IF l_last_snap IS NOT NULL THEN
l_file := UTL_FILE.fopen(l_dir,
'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w',
32767);
FOR cur_rep IN (SELECT
output
FROM
TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid,
l_instance_number, l_last_snap, cur_snap.snap_id)))
LOOP
UTL_FILE.put_line(l_file,
cur_rep.output);
END LOOP;
UTL_FILE.fclose(l_file);
END IF;
l_last_snap :=
cur_snap.snap_id;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file)
THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战