8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Tracking Database Feature Usage (DBA_FEATURE_USAGE_STATISTICS)

Oracle licensing is a complicated business. The notes here are only a guide. You should always discuss your licensing with an expert. Most people do this via a Licensing partner.

There is a discussion of what I did in preparation for an Oracle License Audit here.

Checking for Feature, Option, and Management Pack Usage (Oracle Support)

As described in the documentation, if you have an Oracle Support account, you can download a script called "options_packs_usage_statistics.sql" to produce a more comprehensive report on feature usage. To learn more about this, check out MOS Doc ID 1317265.1.

Thanks to Richard Allison for mentioning this.


It's actually quite simple to see what features are being used in database. Oracle provide the DBA_FEATURE_USAGE_STATISTICS view for just that purpose.

SQL> desc dba_feature_usage_statistics
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DBID                                                  NOT NULL NUMBER
 NAME                                                  NOT NULL VARCHAR2(64)
 VERSION                                               NOT NULL VARCHAR2(17)
 DETECTED_USAGES                                       NOT NULL NUMBER
 TOTAL_SAMPLES                                         NOT NULL NUMBER
 CURRENTLY_USED                                                 VARCHAR2(5)
 FIRST_USAGE_DATE                                               DATE
 LAST_USAGE_DATE                                                DATE
 AUX_COUNT                                                      NUMBER
 FEATURE_INFO                                                   CLOB
 LAST_SAMPLE_DATE                                               DATE
 LAST_SAMPLE_PERIOD                                             NUMBER
 SAMPLE_INTERVAL                                                NUMBER
 DESCRIPTION                                                    VARCHAR2(128)


The following query is taken from the feature_usage.sql script. The output displayed is from a test 12c database.

column name  format a60
column detected_usages format 999999999999

select u1.name,
from   dba_feature_usage_statistics u1
where  u1.version = (select max(u2.version)
                     from   dba_feature_usage_statistics u2
                     where  u2.name = u1.name)
and    u1.detected_usages > 0
and    u1.dbid = (select dbid from v$database)
order by name;

NAME                                                         DETECTED_USAGES CURRE VERSION
------------------------------------------------------------ --------------- ----- -----------------
Adaptive Plans                                                             1 TRUE
Automatic Maintenance - Optimizer Statistics Gathering                     1 TRUE
Automatic Maintenance - SQL Tuning Advisor                                 1 TRUE
Automatic Maintenance - Space Advisor                                      1 TRUE
Automatic Reoptimization                                                   1 TRUE
Automatic SGA Tuning                                                       1 TRUE
Automatic SQL Execution Memory                                             1 TRUE
Automatic Segment Space Management (system)                                1 TRUE
Automatic Undo Management                                                  1 TRUE
Backup Rollforward                                                         1 TRUE
Backup and Restore of plugged database                                     1 TRUE

NAME                                                         DETECTED_USAGES CURRE VERSION
------------------------------------------------------------ --------------- ----- -----------------
Character Set                                                              1 TRUE
Deferred Segment Creation                                                  1 TRUE
Flashback Database                                                         1 TRUE
Job Scheduler                                                              1 TRUE
LOB                                                                        1 TRUE
Locally Managed Tablespaces (system)                                       1 TRUE
Locally Managed Tablespaces (user)                                         1 TRUE
Logfile Multiplexing                                                       1 TRUE
Oracle Java Virtual Machine (system)                                       1 TRUE
Oracle Managed Files                                                       1 TRUE
Oracle Multitenant                                                         2 TRUE

NAME                                                         DETECTED_USAGES CURRE VERSION
------------------------------------------------------------ --------------- ----- -----------------
Oracle Pluggable Databases                                                 1 TRUE
Parallel SQL Query Execution                                               1 TRUE
Partitioning (system)                                                      1 TRUE
Recovery Area                                                              1 TRUE
Result Cache                                                               1 TRUE
SQL Plan Directive                                                         1 TRUE
SecureFiles (system)                                                       1 TRUE
SecureFiles (user)                                                         1 TRUE
Server Parameter File                                                      1 TRUE
Traditional Audit                                                          1 TRUE
Unified Audit                                                              1 TRUE

33 rows selected.



By default the feature usage view is updated about once per week. You can force the view to be updated by using the DBMS_FEATURE_USAGE_INTERNAL package. It's not documented, but there are references to it on My Oracle Support (MOS).

SQL> exec dbms_feature_usage_internal.exec_db_usage_sampling(sysdate);

PL/SQL procedure successfully completed.


Feature Availability by Edition/Offering

The documentation for each database version has an "Oracle Database Licensing Information" manual. One of the sections in that manual is called "Feature Availability by Edition". As the name suggests, this lists most of the database options and tells you which database editions they are valid for, as well as the licensing for that option. By comparing the output from the DBA_FEATURE_USAGE_STATISTICS view and this document you can check you are complying with your licensing.

Licensing of options does change between versions, so be sure to check with the relevant documentation and always discuss your licensing with an expert.

Disabling Options (chopt)

Some options can be turned off using the chopt utility, to make sure they are not used by accident.

I would not recommend doing this.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.