Privileges:
STEP-1
Please connect to sys and give these privileges to Scott.
GRANT SELECT ON SYS.V_$DATABASE TO SCOTT;
GRANT SELECT ON SYS.V_$INSTANCE TO SCOTT;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO SCOTT;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO SCOTT;
GRANT ADVISOR TO SCOTT;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO SCOTT;
STEP-2 :
SET LINES 400
SET PAGES 400
COL BEGIN_INTERVAL_TIME FOR A40
COL END_INTERVAL_TIME FOR A40
Spool d:\awr.txt
SELECT DBID,INSTANCE_NUMBER,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 5 DESC;
Spool off;
Note: Use above query to get low snap, high snap details. DBID and Instance number
STEP 3:
Query to Generate AWR report:
SELECT OUTPUT FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (DBID,INSTANCE_NUMBER, LOW_SNAP_ID,HIGH_SNAP_ID,8));
Note : Please put all the details which you have got from step :2
Generate the AWR report on HTML format using below query:
Ex:
SET HEADING OFF;
SET FEEDBACK OFF;
SET LINESIZE 1500;
PROMPT GETTING HTML FORMAT REPORT:
SPOOL d:\AWR_REPORT_HTML.HTML;
SELECT OUTPUT FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (1318580148,1,798,800,8));
SPOOL OFF;
METHOD 2:
Also we can run awrrpt.sql which is located in ORACLE_HOME\RDBMS\ADMIN location.
