Tracking Database Feature Usage (DBA_FEATURE_USAGE_STATISTICS)

最新推荐文章于 2025-07-22 18:55:55 发布

转载最新推荐文章于 2025-07-22 18:55:55 发布 · 1.6k 阅读

文章标签：

#Oracle #FEATURE_USAGE

基础学习专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用Oracle提供的工具来查看数据库中被使用的特性，并通过比较特性可用性文档检查是否符合许可要求。

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:

原文地址：https://oracle-base.com/articles/misc/tracking-database-feature-usage

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select * from v$option ;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters TRUE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management TRUE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE
Data Redaction TRUE

65 rows selected.

SQL> show parameter control_management_pack_access

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING

SQL> SELECT name,
2 detected_usages detected,
3 total_samples samples,
4 currently_used used,
5 to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
6 sample_interval interval
7 FROM dba_feature_usage_statistics
8 WHERE name = 'Automatic Workload Repository';

NAME DETECTED SAMPLES USED LAST_SAMPLE INTERVAL
------------------------------------------------------------ ---------- ---------- ----- -------------- ----------
Automatic Workload Repository 0 14 FALSE 11272015:00:45 604800

SQL> COLUMN name FORMAT A60
SQL> COLUMN detected_usages FORMAT 999999999999
SQL>
SQL> SELECT u1.name,
2 u1.detected_usages,
3 u1.currently_used,
4 u1.version
5 FROM dba_feature_usage_statistics u1
6 WHERE u1.version = (SELECT MAX(u2.version)
7 FROM dba_feature_usage_statistics u2
8 WHERE u2.name = u1.name)
9 AND u1.detected_usages > 0
10 AND u1.dbid = (SELECT dbid FROM v$database)
11 ORDER BY name;

NAME DETECTED_USAGES CURRE VERSION
------------------------------------------------------------ --------------- ----- -----------------
AWR Report 1 FALSE 11.2.0.4.0
Audit Options 14 TRUE 11.2.0.4.0
Automatic Maintenance - Optimizer Statistics Gathering 14 TRUE 11.2.0.4.0
Automatic Maintenance - SQL Tuning Advisor 14 TRUE 11.2.0.4.0
Automatic Maintenance - Space Advisor 14 TRUE 11.2.0.4.0
Automatic SGA Tuning 14 TRUE 11.2.0.4.0
Automatic SQL Execution Memory 14 TRUE 11.2.0.4.0
Automatic SQL Tuning Advisor 14 TRUE 11.2.0.4.0
Automatic Segment Space Management (system) 14 TRUE 11.2.0.4.0
Automatic Segment Space Management (user) 14 TRUE 11.2.0.4.0
Automatic Storage Management 14 TRUE 11.2.0.4.0
Automatic Undo Management 14 TRUE 11.2.0.4.0
Character Set 14 TRUE 11.2.0.4.0
Deferred Segment Creation 14 TRUE 11.2.0.4.0
Flashback Database 14 TRUE 11.2.0.4.0
LOB 14 TRUE 11.2.0.4.0
Locally Managed Tablespaces (system) 14 TRUE 11.2.0.4.0
Locally Managed Tablespaces (user) 14 TRUE 11.2.0.4.0
Oracle Java Virtual Machine (system) 14 TRUE 11.2.0.4.0
Oracle Utility Datapump (Export) 13 TRUE 11.2.0.4.0
Oracle Utility Datapump (Import) 1 FALSE 11.2.0.4.0
Oracle Utility Metadata API 14 TRUE 11.2.0.4.0
Parallel SQL Query Execution 14 TRUE 11.2.0.4.0
Partitioning (system) 14 TRUE 11.2.0.4.0
RMAN - Disk Backup 2 TRUE 11.2.0.4.0
RMAN - Tape Backup 11 TRUE 11.2.0.4.0
Real Application Clusters (RAC) 14 TRUE 11.2.0.4.0
Recovery Area 14 TRUE 11.2.0.4.0
Recovery Manager (RMAN) 11 TRUE 11.2.0.4.0
Result Cache 13 TRUE 11.2.0.4.0
SecureFiles (system) 14 TRUE 11.2.0.4.0
SecureFiles (user) 14 TRUE 11.2.0.4.0
Server Parameter File 14 TRUE 11.2.0.4.0
Virtual Private Database (VPD) 14 TRUE 11.2.0.4.0

34 rows selected.