Batch generation of ADDM reports under Oracle

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