【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:

提示無法通路V$，Dynamic Performance Table。

賦予使用者權限：

SQL> grant select any dictionary to test;

Grant succeeded.

   DB Id    DB Name Inst Num Instance

----------- ------------ -------- ------------

  142990149 BISAL 1 bisal

   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

548 11 Aug 2013 17:00

549 11 Aug 2013 18:00

550 12 Aug 2013 15:04

551 12 Aug 2013 16:00

552 12 Aug 2013 17:00

553 12 Aug 2013 18:00

554 12 Aug 2013 19:00

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;

再嘗試就可以執行了。說明Stackpack(/AWR)的執行需要三個條件：

1、connect / create session權限。

2、select any dictionary權限。

3、execute on any dbms_workload_repository權限。