AWR Reports Licensing requirement in Oracle
AWR stands for Automatic Workload Repository. It is used to store data related to system information which will help in diagnosis of performance related issue in future.
By default, its data collection is enabled. It help in advisory and fetching tuning reports like ADDM, ASH, SQL tuning etc.
Note: For fetching the direct reports is NOT permitted without the Diagnostic Pack license. You can access implicit some AWR views.
Diagnostic Pack License is required for AWR reports, ADDM reports and ASH reports or tuning reports.
Check the AWR usage
column name format a30
SELECT name,detected_usages,currently_used,
TO_CHAR(last_sample_date,'DD-MON-YYYY:HH24:MI') last_sample
FROM dba_feature_usage_statistics
WHERE name = 'AWR Report' ;
NAME DETECTED_USAGES CURRE LAST_SAMPLE -------------------- --------------- ----- ----------------- AWR Report 1 TRUE 30-DEC-2019:18:07
For Controlling the use of Diagnostic Pack
CONTROL_MANAGEMENT_PACK_ACCESS parameter allows you to control which packs are utilized.
Values:
NONE – None pack is available.
DIAGNOSTIC – DIAGNOSTIC pack is available.
DIAGNOSTIC+TUNING – Both packs are available(Default).
Check the Diagnostic Pack information
SQL> show parameter control_management_pack_access
NAME TYPE VALUE --------------------------------- -------- ---------- control_management_pack_access string NONE
Disable or Enable the Diagnostic pack
-- Disable
ALTER SYSTEM SET control_management_pack_access=NONE;
--Enable
alter system set control_management_pack_access="DIAGNOSTIC+TUNING";