 |
|
Installation or AWR Tips
Oracle Database Tips by Donald Burleson25 January 2013 |
Question:
I have paid the $7,000.00 fee for the packs to use
AWR (the performance pack and the diagnostic pack? Yet I get this
error when trying to run an AWR report:
exec
dbms_workload_repository.create_snapshot;
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_WORKLOAD_REPOSITORY"
has errors
ORA-06508:
PL/SQL: could not find program unit being called:
"SYS.DBMS_WORKLOAD_REPOSITORY"
ORA-06512: at line 1
Answer:
To install AWR (whihc should be installed by
default at dsatabase creation time), there
are only two AWR installation scripts,
which are also located in the $ORACLE_HOME/rdbms/admin directory:
catawr.sql This AWR script creates data dictionary catalog objects for the
AWR.
dbmsawr.sql
This AWR script creates the dbms_workload_repository
package for database administrators.
By design, the AWR is created at the same time the
database is created and is included in the data dictionary. This
shows that the AWR is in the kernel part of the database that cannot
function properly without it, while STATSPACK is a stand-alone
utility that can be installed or removed from the database at any
time.
Privileges to run AWR Reports
Sometime you need to create a user that will be able
to have the system privileges to ruin AWR and you can create a
awr_role for this purpose and grant it to the awr_user:
create role awr_user;
grant select on
sys.v_$database
to awr_user;
grant
select on sys.v_$instance
to awr_user;
grant
execute on sys.dbms_workload_repository to awr_user;
grant select on
sys.dba_hist_database_instance to awr_user;
grant select on sys.dba_hist_snapshot
to awr_user;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
and include the URL for the page.
