“Free” DBA_HIST AWR views in 11g…

I just noticed this in 11g Licensing doc ( http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/options.htm#sthref69 ):

  • All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Diagnostic Pack license.

This exception is not present in 10.2 license guide, so before 11g you can query V$SEGMENT_STATISTICS and V$UNDOSTAT’s history “for free” :)

Of course, collecting this data manually with a 1-line PL/SQL loop script isn’t hard either :)

NB! Dates updated: After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 14-18 December 2015 and 11-15 January 2016 (I had to reschedule the start from November to December). So sign up now if you want to learn new cool stuff!

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

6 Responses to “Free” DBA_HIST AWR views in 11g…

  1. patrick says:

    I was told Oracle has way to know whether AWR tables have been queried ? what if someone export all the AWR data to an external non-Oracle db for
    reporting/consolidation ? would the license apply to this case ?
    interesting topic

  2. Tanel Poder says:

    When you access Oracle AWR (and other licensed features/tables/views) then these do show up in DBA_FEATURE_USAGE_STATISTICS. Exporting the contents is the same as accessing these with your SQL.

    Nevertheless, you don’t want to be breaking your license agreement even if Oracle wouldn’t track the usage. However I think many companies are unknowingly violating the conditions as DBAs and developers like to download random scripts from internet to see whether it fixes their problem or not.

    Oracle introduced a parameter in 11g which allows you to disable access to some separately licensed features, you can set control_management_pack_access = none to make diag/tuning pack stuff unavailable to users.

  3. patrick says:

    I have this on one db
    you can notice the recent upgrade …
    though I can’t see anything for AWR tables
    I guess now with control_management_pack_access = none
    Oracle has a better way to check for AWR use
    I shall make a test with 11gR2 …

    —————————————- ———- —————
    Automatic Database Diagnostic Monitor 16
    Automatic SQL Execution Memory 88
    Automatic Segment Space Management (syst 88

    Automatic Segment Space Management (user 87

    Automatic Storage Manager 88
    Automatic Undo Management 88
    Character Set 88
    Dynamic SGA 80
    Internode Parallel Execution 85
    Locally Managed Tablespaces (system) 88
    Locally Managed Tablespaces (user) 88
    Parallel SQL DDL Execution 2
    Parallel SQL Query Execution 85
    Partitioning (system) 88
    Protection Mode – Maximum Performance 88
    RMAN – Tape Backup 80
    Real Application Clusters (RAC) 87
    Recovery Manager (RMAN) 80
    SQL Tuning Advisor 4
    Segment Advisor 87
    Server Parameter File 87
    Shared Server 20
    Streams (system) 88
    Automatic SQL Execution Memory 1
    Automatic Segment Space Management (syst 1

    Automatic Segment Space Management (user 1

    Automatic Storage Manager 1
    Automatic Undo Management 1
    Character Set 1
    Internode Parallel Execution 1
    LOB 1
    Locally Managed Tablespaces (system) 1
    Locally Managed Tablespaces (user) 1
    Parallel SQL Query Execution 1
    Partitioning (system) 1
    Protection Mode – Maximum Performance 1
    RMAN – Tape Backup 1
    Real Application Clusters (RAC) 1
    Recovery Manager (RMAN) 1
    Segment Advisor 1
    Server Parameter File 1
    Streams (system) 1

  4. patrick says:

    new statictic name have been added in 11g, namely

    AWR Baseline => At least one AWR Baseline has been created by the user

    AWR Baseline Template => At least one AWR Baseline Template has been created by the user

    AWR Report => At least one Workload Repository Report has been created by the user

    CONTROL_MANAGEMENT_PACK_ACCESS specifies which of the Server Manageability Packs should be active. The following packs are available:


    The DIAGNOSTIC pack includes AWR, ADDM, and so on.

    The TUNING pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.

    A license for DIAGNOSTIC is required for enabling the TUNING pack.




    Both packs are not available.


    Only the DIAGNOSTIC pack is available.


    Both packs are available. This is the default.

    so only the DIAGNOSTIC pack license
    is ncessary for using AWR

  5. Chris Brown says:

    I think you interpretation is off. Also the wording in the DOC is obtuse.

    “They can be used without the Diagnostic Pack license” refers to the preceding sentence only and not to every DBA_HIST view…only a subset can be used.

    Do you now agree?

    • Tanel Poder says:

      I haven’t even offered any interpretation in that article, basically just quoted the docs for everyone to interpret themselves. Someone might misinterpret the title of my post (that “free AWR views” means “everything is free now” as opposed to “some AWR views are free”)… but that’s exactly why I posted the document excerpt.

Leave a Reply

Your email address will not be published. Required fields are marked *