AWR - Oracle Diag & Tuning Pack

AWR reports and their ilk, such as ASH reports are only available in an Enterprise Edition, simply because one has to purchase the Oracle Diagnostic and Tuning Packs, commonly misunderstood as only relating to OEM.

 If these packs, or more appropriately, the licenses for these packs have not been purchased, then AWRs etc should never be ran on a database.

If there is no license agreement, use the following piece of SQL to see if AWR et al have been ran on a database.


col name format A31
col detected format 9999
col samples format 9999
col used format A5
col interval format 9999999

SELECT name,
detected_usages detected,
total_samples samples,
currently_used used,
to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
sample_interval interval
FROM dba_feature_usage_statistics
WHERE name = 'Automatic Workload Repository' OR name like 'SQL%';


If there are results for this on a Standard Edition or Enterprise Edition database where there is no licence agreement, then you could be in trouble.

For databases where there is no licence agreement then you should switch off the AWR capabilities just to protect yourself.

To check this run the following SQL

SQL> show parameter control_management_pack_access

If this is set to DIAGNOSTIC+TUNING then we should turn it off and use the following

SQL> ALTER SYSTEM SET control_management_pack_access=NONE;