Tracking Database Feature Usage (DBA_FEATURE_USAGE_STATISTICS)

来源：互联网发布：linux 查看重启动原因编辑：程序博客网时间：2024/06/05 07:36

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
DBMS_FEATURE_USAGE_INTERNAL
Feature Availability by Edition
Disabling Options (chopt)

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 A60COLUMN detected_usages FORMAT 999999999999SELECT u1.name,       u1.detected_usages,       u1.currently_used,       u1.versionFROM   dba_feature_usage_statistics u1WHERE  u1.version = (SELECT MAX(u2.version)                     FROM   dba_feature_usage_statistics u2                     WHERE  u2.name = u1.name)AND    u1.detected_usages > 0AND    u1.dbid = (SELECT dbid FROM v$database)ORDER BY name;NAME                                                         DETECTED_USAGES CURRE VERSION------------------------------------------------------------ --------------- ----- -----------------Adaptive Plans                                                             1 TRUE  12.1.0.2.0Automatic Maintenance - Optimizer Statistics Gathering                     1 TRUE  12.1.0.2.0Automatic Maintenance - SQL Tuning Advisor                                 1 TRUE  12.1.0.2.0Automatic Maintenance - Space Advisor                                      1 TRUE  12.1.0.2.0Automatic Reoptimization                                                   1 TRUE  12.1.0.2.0Automatic SGA Tuning                                                       1 TRUE  12.1.0.2.0Automatic SQL Execution Memory                                             1 TRUE  12.1.0.2.0Automatic Segment Space Management (system)                                1 TRUE  12.1.0.2.0Automatic Undo Management                                                  1 TRUE  12.1.0.2.0Backup Rollforward                                                         1 TRUE  12.1.0.2.0Backup and Restore of plugged database                                     1 TRUE  12.1.0.2.0NAME                                                         DETECTED_USAGES CURRE VERSION------------------------------------------------------------ --------------- ----- -----------------Character Set                                                              1 TRUE  12.1.0.2.0Deferred Segment Creation                                                  1 TRUE  12.1.0.2.0Flashback Database                                                         1 TRUE  12.1.0.2.0Job Scheduler                                                              1 TRUE  12.1.0.2.0LOB                                                                        1 TRUE  12.1.0.2.0Locally Managed Tablespaces (system)                                       1 TRUE  12.1.0.2.0Locally Managed Tablespaces (user)                                         1 TRUE  12.1.0.2.0Logfile Multiplexing                                                       1 TRUE  12.1.0.2.0Oracle Java Virtual Machine (system)                                       1 TRUE  12.1.0.2.0Oracle Managed Files                                                       1 TRUE  12.1.0.2.0Oracle Multitenant                                                         2 TRUE  12.1.0.1.0NAME                                                         DETECTED_USAGES CURRE VERSION------------------------------------------------------------ --------------- ----- -----------------Oracle Pluggable Databases                                                 1 TRUE  12.1.0.2.0Parallel SQL Query Execution                                               1 TRUE  12.1.0.2.0Partitioning (system)                                                      1 TRUE  12.1.0.2.0Recovery Area                                                              1 TRUE  12.1.0.2.0Result Cache                                                               1 TRUE  12.1.0.2.0SQL Plan Directive                                                         1 TRUE  12.1.0.2.0SecureFiles (system)                                                       1 TRUE  12.1.0.2.0SecureFiles (user)                                                         1 TRUE  12.1.0.2.0Server Parameter File                                                      1 TRUE  12.1.0.2.0Traditional Audit                                                          1 TRUE  12.1.0.2.0Unified Audit                                                              1 TRUE  12.1.0.2.033 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.

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)

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.

Enabling and Disabling Database Options (11gR2)
Enabling and Disabling Database Options (12cR1)

For more information see:

DBA_FEATURE_USAGE_STATISTICS
Oracle DBMS_FEATURE_USAGE_INTERNAL
Oracle License Audit
Oracle License Management Services

原文地址：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.

0 0