This discussion is archived
8 Replies Latest reply: Feb 18, 2009 1:44 PM by 653864 RSS

AWR REPORT

533779 Newbie
Currently Being Moderated
Hi,

Whar are the privilege required in order to run AWR Report. Also, can a NON DBA user pull out AWR report without having any special privilege?

hare krishna
  • 1. Re: AWR REPORT
    653864 Newbie
    Currently Being Moderated
    GRANT SELECT ON SYS.V_$DATABASE TO ...
    /
    GRANT SELECT ON SYS.V_$INSTANCE TO ...
    /
    GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO ...
    /
    GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO ...
    /
    GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO ...
    /
    GRANT ADVISOR TO ...
    /

    and yes, non dba can pull out awr report using the same scripts. i copied it from unix to windows and run it on my pc - no need to copy or ftp it over the net.

    my oracle version is 10.2.0.2 - this may be important.

    hare rama ;-)

    Edited by: batonoff on Feb 17, 2009 3:53 PM

    Edited by: batonoff on Feb 17, 2009 3:55 PM
  • 2. Re: AWR REPORT
    FinbarFitzgerald Journeyer
    Currently Being Moderated
    I didn't see which version of Oracle you are on. Here are some notes on running AWR reports from the Oracle 10 Database Performance Tuning Guide. There is a link below to the manual for further reading.

    You can view the AWR reports with Oracle Enterprise Manager or by running SQL
    scripts. To run an AWR report, a user must be granted the DBA role.
    The reports are divided into multiple sections. The HTML report includes links that
    can be used to navigate quickly between sections. The content of the report contains
    the workload profile of the system for the selected range of snapshots.
    This section covers the following topics:
    ■ Running Workload Repository Reports Using Enterprise Manager
    ■ Running Workload Repository Compare Period Report Using Enterprise Manager

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm
  • 3. Re: AWR REPORT
    546494 Journeyer
    Currently Being Moderated
    hi,
    You also need to make sure that you have purchased the option from Oracle to use AWR.

    regards

    Alan
  • 4. Re: AWR REPORT
    533779 Newbie
    Currently Being Moderated
    DB VERSION- 10.2.0.3.0

    You mean, in order to see AWR report, I need to grant the following privileges Right ?

    GRANT SELECT ON SYS.V_$DATABASE TO ...
    /
    GRANT SELECT ON SYS.V_$INSTANCE TO ...
    /
    GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO ...
    /
    GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO ...
    /
    GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO ...
    /
    GRANT ADVISOR TO ...

    hare krishna
    Alok
  • 5. Re: AWR REPORT
    533779 Newbie
    Currently Being Moderated
    Also, if I run following, will this generate AWR report?

    SELECT
    output
    FROM
    TABLE
    (dbms_workload_repository.awr_report_text (37933856,1,2900,2911) );


    hare krishna
    Alok
  • 6. Re: AWR REPORT
    546494 Journeyer
    Currently Being Moderated
    hi,
    Are you using Standard edition? AWR should not be used in this version you have to use statspack.

    Enterprise edition without the extra cost option also has to use statspack.

    regards

    Alan
  • 7. Re: AWR REPORT
    653864 Newbie
    Currently Being Moderated
    right. i grant this selects to role, and role is granted to user. then i use scripts from $ORACLE_HOME/rdbms/admin/awrrpt* to create report. you can copy scripts to windows or any other host and run it remotly.
  • 8. Re: AWR REPORT
    653864 Newbie
    Currently Being Moderated
    i have never used this: dbms_workload_repository.awr_report_text. i use awrrpt script.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points