Yuan Yao, An Oracle ACE's Blog

It’s important to recognize that a license for Oracle Database Enterprise Edition does not automatically grant access to Automatic Workload Repository (AWR) and Active Session History (ASH). These features are part of the Oracle Diagnostics Pack, an additional paid option that requires a separate license on top of the Enterprise Edition.

Accessing AWR and ASH functionality—whether through the Enterprise Manager Console, command-line APIs, or directly querying the underlying data (e.g., V$ACTIVE_SESSION_HISTORY or DBA_HIST_* )—requires a valid Diagnostics Pack license. Note that the Diagnostics Pack alone is sufficient; a combined Diagnostics and Tuning Pack license is not mandatory for AWR and ASH usage.

Oracle provides the CONTROL_MANAGEMENT_PACK_ACCESS parameter to control access to licensed packs. It accepts three values:

• DIAGNOSTIC+TUNING (default): Enables both Diagnostics and Tuning Pack features.
• DIAGNOSTIC: Enables only Diagnostics Pack features. This is sufficient for AWR and ASH.
• NONE: Disables both Diagnostics and Tuning Pack features, preventing AWR and ASH access.

Note: The Tuning Pack cannot be enabled independently, as tuning functionality depends on diagnostics capabilities.

Setting CONTROL_MANAGMENT_PACK_ACCESS to NONE blocks new AWR and ASH data generation. For ASH, querying ASH data in memory (V$ACTIVE_SESSION_HISTORY) yields no results when the parameter is set to NONE:

As shown, setting the parameter to NONE prevents access to in-memory ASH data. Restoring it to DIAGNOSTIC immediately re-enables ASH visibility.

For AWR, snapshots continue to be generated when set to NONE, but no new metrics are captured. The following query demonstrates this by checking the latest snapshot and its associated data in DBA_HIST_ACTIVE_SESS_HISTORY:

This output confirms that while the snapshot (SNAP_ID 4933) was created, no data was captured in DBA_HIST_ACTIVE_SESS_HISTORY (actually all DBA_HIST_* views) due to the NONE setting.