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 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.
- 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.
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.