由于权限问题无法生成AWR报告

生成AWR报告的时候遇到如下错误:

~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_7796_7797.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_7796_7797.html
 select output from table(dbms_workload_repository.awr_report_html( :dbid,
 *
 ERROR at line 1:
 ORA-00904: : invalid identifier
Report written to awrrpt_1_7796_7797.html

这是因为用户没有生成AWR报告的权限。如果希望一个用户能生成AWR报告,最少需要几个权限呢?
我认为应是三个:
1.connect/create session
2.select any dictionary(查询字表)
3.execute on any dbms_workload_repository(负载信息库)

通过以下实验证明:

Last login: Tue Dec 13 16:00:29 2011 from 58.242.2.52
 [[email protected] ~]$ sql
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 14 07:51:45 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user test1 cascade;
User dropped.
SQL> create user awr identified by awr;
User created.
SQL> grant connect to awr;
Grant succeeded.
SQL> grant select any dictionary to awr;
Grant succeeded.
SQL> grant execute on DBMS_WORKLOAD_REPOSITORY to awr;
Grant succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
 ----------- ------------ -------- ------------
 1288916317 ORCL 1 orcl
Specify the Report Type
 ~~~~~~~~~~~~~~~~~~~~~~~
 Would you like an HTML report, or a plain text report?
 Enter 'html' for an HTML report, or 'text' for plain text
 Defaults to 'html'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
 ------------ -------- ------------ ------------ ------------
 * 1288916317 1 ORCL orcl hpserver
Using 1288916317 for database Id
 Using 1 for instance number
Specify the number of days of snapshots to choose from
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Entering the number of days (n) will result in the most recent
 (n) days of snapshots being listed. Pressing without
 specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
 Instance DB Name Snap Id Snap Started Level
 ------------ ------------ --------- ------------------ -----
 orcl ORCL 2136 14 Dec 2011 00:00 1
 2137 14 Dec 2011 01:00 1
 2138 14 Dec 2011 02:00 1
 2139 14 Dec 2011 03:00 1
 2140 14 Dec 2011 04:00 1
 2141 14 Dec 2011 05:00 1
 2142 14 Dec 2011 06:00 1
 2143 14 Dec 2011 07:00 1
Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Enter value for begin_snap: 2142
 Begin Snapshot Id specified: 2142
Enter value for end_snap: 2143
 End Snapshot Id specified: 2143
Specify the Report Name
 ~~~~~~~~~~~~~~~~~~~~~~~
 The default report file name is awrrpt_1_2142_2143.html. To use this name,
 press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_2142_2143.html
 …省略…
End of Report
Report written to awrrpt_1_2142_2143.html

网上有人说连查询字典表的权限都不需要,我们测试下:

SQL> revoke select any dictionary from awr;
Revoke succeeded.
SQL> conn awr/awr
 Connected.
 SQL> @?/rdbms/admin/awrrpt
Current Instance
 ~~~~~~~~~~~~~~~~
 v$instance i
 *
 ERROR at line 6:
 ORA-00942: table or view does not exist
Specify the Report Type
 ~~~~~~~~~~~~~~~~~~~~~~~
 Would you like an HTML report, or a plain text report?
 Enter 'html' for an HTML report, or 'text' for plain text
 Defaults to 'html'
 Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 from dba_hist_database_instance wr, v$database cd, v$instance ci
 *
 ERROR at line 14:
 ORA-00942: table or view does not exist

报错,提示没有此表或view。在什么情况下会提示呢?
1.真的没有这个对象(我们知道,这个可以有)
2.用户没权限查看此对象。

综上所述,如果一个用户需要生成awr,需要至少对他进行如下赋权:
grant connect to [user];
grant select any dictionary to [user];
grant execute on DBMS_WORKLOAD_REPOSITORY to [user];

参考资料:
http://cyzhang1983.itpub.net/post/40572/499634
http://www.itpub.net/thread-1330158-1-1.html

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress