首页 > 代码库 > Oracle下批量生成ADDM报告
Oracle下批量生成ADDM报告
使用方法: 在E盘下面建立addm的目录,将三个文件放入到其中addm.cmd、addm.sql、myaddm.sql.
前提连上的用户需要被赋予两个权限。
grant select any dictionary to user;-----user为生产数据库账号
grant advisor to user;-------user为生产数据库账号
1. 文件名:addm.cmd
内容: cmd.exe /c sqlplus username/password@servername @addm.sql
2. 文件名: addm.sql
内容:
@@E:\addm\myaddm.sql 0 8
@@E:\addm\myaddm.sql 8 12
@@E:\addm\myaddm.sql 12 14
@@E:\addm\myaddm.sql 14 18
@@E:\addm\myaddm.sql 18 24
exit
3. 文件名: myaddm.sql
内容:
set echo off;
set veri off;
set feedback off;
set termout on;
set heading off;
set linesize 300;
var dbid number;
var inst_num number;
var bid number;
var eid number;
var bhour varchar2(10);
var ehour varchar2(10);
var task_name varchar2(40);
begin
:bhour := &1;
:ehour := &2;
if(length(:bhour)=1)
then :bhour := ‘0‘||:bhour;
end if;
if(length(:ehour)=1)
then :ehour := ‘0‘||:ehour;
end if;
end;
/
begin
select min(snap_id) into :bid from dba_hist_snapshot s
where (to_char(sysdate-1 , ‘yyyy-mm-dd‘) || ‘ ‘ ||:bhour) <=
to_char(s.end_interval_time, ‘yyyy-mm-dd HH24‘)
and (to_char(sysdate-1 , ‘yyyy-mm-dd‘) || ‘ ‘ ||:ehour) >=
to_char(s.end_interval_time, ‘yyyy-mm-dd HH24‘);
select max(snap_id) into :eid from dba_hist_snapshot s
where (to_char(sysdate-1 , ‘yyyy-mm-dd‘) || ‘ ‘ ||:bhour) <=
to_char(s.end_interval_time, ‘yyyy-mm-dd HH24‘)
and (to_char(sysdate-1 , ‘yyyy-mm-dd‘) || ‘ ‘ ||:ehour) >=
to_char(s.end_interval_time, ‘yyyy-mm-dd HH24‘);
if(:ehour=24)
then select max(snap_id) into :eid from dba_hist_snapshot s where (to_char(sysdate,‘yyyy-mm-dd‘)||‘ 00‘) =to_char(s.end_interval_time,‘yyyy-mm-dd HH24‘);
end if;
select dbid into :dbid from v$database;
select instance_number into :inst_num from v$instance;
end;
/
column report_name new_value report_name noprint;
select ‘PMS_‘||:inst_num||‘_‘||to_char(sysdate-1 ,‘yyyymmdd‘)||‘_‘||:bhour||‘-‘||:ehour||‘.txt‘ report_name from dual;
begin
declare
id number;
name varchar2(100);
descr varchar2(500);
BEGIN
name := ‘‘;
descr := ‘ADDM run: snapshots [‘ || :bid || ‘, ‘
|| :eid || ‘], instance ‘ || :inst_num
|| ‘, database id ‘ || :dbid;
dbms_advisor.create_task(‘ADDM‘,id,name,descr,null);
:task_name := name;
dbms_advisor.set_task_parameter(name, ‘START_SNAPSHOT‘, :bid);
dbms_advisor.set_task_parameter(name, ‘END_SNAPSHOT‘, :eid);
dbms_advisor.set_task_parameter(name, ‘INSTANCE‘, :inst_num);
dbms_advisor.set_task_parameter(name, ‘DB_ID‘, :dbid);
dbms_advisor.execute_task(name);
end;
end;
/
spool &report_name;
set long 1000000 pagesize 0 longchunksize 1000
column get_clob format a80
select dbms_advisor.get_task_report(:task_name, ‘TEXT‘, ‘TYPICAL‘)
from dual;
spool off;
prompt
prompt End of Report
prompt Report written to &report_name.
set termout on;
clear columns sql;
ttitle off;
btitle off;
repfooter off;
undefine report_name
Oracle下批量生成ADDM报告
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。