Batch generation of ADDM reports under Oracle

Source: Internet
Author: User

How to use: Set up the ADDM directory below the e-drive and put three files into it addm.cmd,ADDM. sql,myaddm. sql.

The user needs to be given two permissions to connect the premises.

Grant Select any dictionary to user,-----user for production database account

Grant Advisor to user,-------user for production database account

1. File name:addm. Cmd

content: cmd.exe/c sqlplus username/[email protected] @ADDM. sql

2. File name: ADDM. sql

content:

@@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. File name: myaddm.sql

Content:

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 ') | | ' xx ') =to_char (S.end_interval_time, ' yyyy-mm-dd HH24 ');
End If;
Select dbid into:d bid 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 ' | | :D bid;
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 ',:d Bid);
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 written to &report_name.


Set termout on;
clear columns SQL;
Ttitle off;
Btitle off;
Repfooter off;
Undefine Report_name

Batch generation of ADDM reports under Oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.