8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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)
- DBA_FEATURE_USAGE_STATISTICS
- DBMS_FEATURE_USAGE_INTERNAL
- Feature Availability by Edition/Offering
- Disabling Options (chopt)
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.
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 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. SQL>
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.
- Feature Availability by Edition (10gR1)
- Feature Availability by Edition (10gR2)
- Feature Availability by Edition (11gR1)
- Feature Availability by Edition (11gR2)
- Feature Availability by Edition (12cR1)
- Permitted Features, Options, and Management Packs by Oracle Database Offering (12cR2)
- Permitted Features, Options, and Management Packs by Oracle Database Offering (18c)
- Permitted Features, Options, and Management Packs by Oracle Database Offering (19c)
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...