【Oracle】-【AWR/Stackpack】-AWR(Stackpack)执行权限
【Oracle】-【AWR/Stackpack】-AWR(Stackpack)执行权限
Oracle 9i执行Stackpack(10g是AWR),当前是一个普通账户,没有DBA权限。
SQL> @awrrpt.sql 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: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ from dba_hist_database_instance wr, v$database cd, v$instance ci * ERROR at line 13: ORA-00942: table or view does not exist
提示无法访问V$,Dynamic Performance Table。
赋予用户权限:
SQL> grant select any dictionary to test; Grant succeeded. SQL> @awrrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 142990149 BISAL 1 bisal 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: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 142990149 1 BISAL bisal liu Using 142990149 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 <return> without specifying a number lists all completed snapshots. Enter value for num_days: Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- bisal BISAL 545 11 Aug 2013 14:11 1 546 11 Aug 2013 15:00 1 547 11 Aug 2013 16:00 1 548 11 Aug 2013 17:00 1 549 11 Aug 2013 18:00 1 550 12 Aug 2013 15:04 1 551 12 Aug 2013 16:00 1 552 12 Aug 2013 17:00 1 553 12 Aug 2013 18:00 1 554 12 Aug 2013 19:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 545 Begin Snapshot Id specified: 545 Enter value for end_snap: 546 End Snapshot Id specified: 546 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_545_546.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: test Using the report name test select output from table(dbms_workload_repository.awr_report_html( :dbid, * ERROR at line 1: ORA-00904: : invalid identifier Report written to test
提示无法执行dbms_workload_repository包。
赋予相应权限:
SQL> grant execute on dbms_workload_repository to test; Grant succeeded.
再尝试就可以执行了。说明Stackpack(/AWR)的执行需要三个条件:
1、connect / create session权限。
2、select any dictionary权限。
3、execute on any dbms_workload_repository权限。