Tracking Database Feature Usage (DBA_FEATURE_USAGE_STATISTICS)
Identify which database options you are using to see if you comply with your Oracle licensing.
oracle miscconfigurationintermediate
by OracleDba
48 views
📋Steps in this guide1/5
1
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.
2
DBA_FEATURE_USAGE_STATISTICS
It's actually quite simple to see what features are being used in database. Oracle provide the view for just that purpose.
The following query is taken from the feature_usage.sql script. The output displayed is from a test 12c database.
Code/Command (click line numbers to comment):
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980
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>
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>3
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 package. It's not documented, but there are references to it on My Oracle Support (MOS).
Code/Command (click line numbers to comment):
12345
SQL> exec dbms_feature_usage_internal.exec_db_usage_sampling(sysdate);
PL/SQL procedure successfully completed.
SQL>4
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 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.
5
Disabling Options (chopt)
Some options can be turned off using the utility, to make sure they are not used by accident.
I would not recommend doing this.
For more information see:
- DBA_FEATURE_USAGE_STATISTICS
- Oracle License Audit
Hope this helps. Regards Tim...
Comments (0)
Please to add comments
No comments yet. Be the first to comment!