8i | 9i | 10g | 11g | 12c | 13c | 18c | 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 Oracle License Management Services.

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

DBA_FEATURE_USAGE_STATISTICS

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)

SQL> 

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,
       u1.detected_usages,
       u1.currently_used,
       u1.version
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  12.1.0.2.0
Automatic Maintenance - Optimizer Statistics Gathering                     1 TRUE  12.1.0.2.0
Automatic Maintenance - SQL Tuning Advisor                                 1 TRUE  12.1.0.2.0
Automatic Maintenance - Space Advisor                                      1 TRUE  12.1.0.2.0
Automatic Reoptimization                                                   1 TRUE  12.1.0.2.0
Automatic SGA Tuning                                                       1 TRUE  12.1.0.2.0
Automatic SQL Execution Memory                                             1 TRUE  12.1.0.2.0
Automatic Segment Space Management (system)                                1 TRUE  12.1.0.2.0
Automatic Undo Management                                                  1 TRUE  12.1.0.2.0
Backup Rollforward                                                         1 TRUE  12.1.0.2.0
Backup and Restore of plugged database                                     1 TRUE  12.1.0.2.0

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

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

33 rows selected.

SQL>

DBMS_FEATURE_USAGE_INTERNAL

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 Morgan's Library has some notes about it.

SQL> EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

PL/SQL procedure successfully completed.

SQL>

Feature Availability by Edition

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 Oracle Licence Management Services, so you don't get any unpleasant surprises.

Disabling Options (chopt)

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.