8 Replies Latest reply: Feb 18, 2009 3:44 PM by 653864 RSS

    AWR REPORT

    533779
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        i have never used this: dbms_workload_repository.awr_report_text. i use awrrpt script.