Detect Oracle features used Oracle Database Tips by Donald BurlesonUpdated 24 April 2013

At installation time, Oracle Corporation does not require license keys and Oracle shops must be aware of those features that some in Standard Edition (SE), Enterprise Edition (EE), and all of the extra cost packs.

See this importanrt script to detect features used

Also see:

• Detect what options are being used
• Detect if data guard is used
• Detect if RAC is used
• Detect if spatial is used
• Detect if partitioning is used
• Detect features used

This query will tell whether a database is using any extra cost options:

col name format a50 heading "Option"
col value format a5 heading "?" justify center wrap
break on value dup skip 1

select parameter name, value
from v$option
order by 2 desc, 1;

 

Auditing for Oracle features used

The DBA_FEATURE_USAGE_STATISTICS view will display information about database feature usage statistics.

set pages 999

col c1 heading 'feature'    format a45
col c2 heading 'times|used' format 999,999
col c3 heading 'first|used'
col c4 heading 'used|now'

select
   name             c1,
   detected_usages  c2,
   first_usage_date c3,
   currently_used   c4
from
   dba_feature_usage_statistics
where
   first_usage_date is not null;

                                              times    first     used
feature                                       used     used      now
--------------------------------------------- -------- --------- -----
Protection Mode - Maximum Performance         24       18-AUG-09 TRUE
Recovery Area                                 24       18-AUG-09 TRUE
Server Parameter File                         24       18-AUG-09 TRUE
Shared Server                                  4       18-AUG-09 FALSE
Streams (system)                              24       18-AUG-09 TRUE
Virtual Private Database (VPD)                24       18-AUG-09 TRUE
Automatic Segment Space Management (system)   24       18-AUG-09 TRUE
Automatic Segment Space Management (user)     24       18-AUG-09 TRUE
Automatic SQL Execution Memory                24       18-AUG-09 TRUE
Automatic Undo Management                     24       18-AUG-09 TRUE
Character Set                                 24       18-AUG-09 TRUE
Dynamic SGA                                    1       18-AUG-09 FALSE
Locally Managed Tablespaces (system)          24       18-AUG-09 TRUE
Locally Managed Tablespaces (user)            24       18-AUG-09 TRUE
Multiple Block Sizes                           7       25-DEC-12 TRUE
Partitioning (system)                         24       18-AUG-09 TRUE

As we see above, starting in 10g, Oracle  will keep track of any of its features that are being used.

As an example, if you SELECT from any of the Oracle Diagnostic Pack and Tuning Pack views like V$ACTIVE_SESSION_HISTORY, the database will record your usage of it in this DBA view.

Some of the information tracked are

o Name of the feature
o Number of times the system has detected usage for the feature
o First sample time the system detected usage of the feature
o Last sample time the system detected usage of the feature

These are just some of the features it tracks

Name - Description
Audit Options - Audit options in use.
Automatic Database Diagnostic Monitor - A task for the Automatic Database Diagnostic Monitor has been executed.
Automatic Segment Space Management (system) - Extents of locally managed tablespaces are managed automatically by Oracle.
Automatic Segment Space Management (user) - Extents of locally managed user tablespaces are managed automatically by Oracle.
Automatic SQL Execution Memory - Sizing of work areas for all dedicated sessions (PGA) is automatic.
Automatic Storage Manager - Automatic Storage Management has been enabled
Automatic Undo Management - Oracle automatically manages undo data using an UNDO tablespace.
Automatic Workload Repository - A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.
Change-Aware Incremental Backup - Track blocks that have changed in the database.
Locally Managed Tablespaces (system) - There exists tablespaces that are locally managed in the database.
Locally Managed Tablespaces (user) - There exists user tablespaces that are locally managed in the database.
SQL Access Advisor - A task for SQL Access Advisor has been executed.
SQL Tuning Advisor - SQL Tuning Advisor has been used."
 

 

---