Generate AWR reports in batches in Oracle

Source: Internet
Author: User

Generate AWR reports in batches in Oracle

In daily work, we need to monitor AWR reports every day to eliminate potential risks. The following is an automatic O & M script. The ADDM of Oracle also provides some optimization suggestions. For the script to automatically generate the ADDM report, see batch generate the ADDM report in Oracle (SEE ).

Usage: Create an AWR directory under the drive E and put the three files into the directory awr. cmd, awr. SQL, myawr. SQL. create a scheduled task to trigger awr through the job plan of window. cmd.

The connected user must be granted two permissions.

Grant select any dictionary to user; ----- the user is the account of the production database.

Grant execute on dbms_workload_repository to user; ------- the user is the production database account.

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report

1. File Name: awr. cmd

Content: cmd.exe/c sqlplus username/password @ servername @ awr. SQL

2. File Name: awr. SQL


@ E: \ AWR \ myawr. SQL 0 8
@ E: \ AWR \ myawr. SQL 8 12
@ E: \ AWR \ myawr. SQL 12 14
@ E: \ AWR \ myawr. SQL 14 18
@ E: \ AWR \ myawr. SQL 18 24

3. File Name: myawr. SQL


Set echo off;
Set veri off;
Set feedback off;
Set termout on;
Set heading off;
Set linesize 1500;

Variable dbid number;
Variable inst_num number;
Variable bid number;
Variable eid number;
Variable bhour number;
Variable ehour number;
: Bhour: = & 1;
: Ehour: = & 2;
Select min (t. snap_id) +: bhour-1 into: bid from sys. dba_hist_snapshot t where to_char (t. begin_interval_time, 'yyyy-mm-dd') = to_char (sysdate-1, 'yyyy-mm-dd ');
Select min (t. snap_id) +: ehour-1 into: eid from sys. dba_hist_snapshot t where to_char (t. begin_interval_time, 'yyyy-mm-dd') = to_char (sysdate-1, 'yyyy-mm-dd ');
Select dbid into: dbid from v $ database;
Select instance_number into: inst_num from v $ instance;
Set termout off;
Column report_name new_value report_name noprint;
Select 'awrrpt _ '| to_char (sysdate-1, 'yyyymmdd') |' _ '|: bhour |'-'|: ehour | '.html 'report_name from dual;
Set termout on;
Spool & report_name;
Select output from table (dbms_workload_repository.awr_report_html (: dbid,: inst_num,: bid,: eid, 0 ));
Spool off;

Set termout on;
Clear columns SQL;
Ttitle off;
Btitle off;
Repfooter off;
Undefine report_name

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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: and provide relevant evidence. A staff member will contact you within 5 working days.