Oracle DBMS_FEATURE_USAGE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Manageability Monitor Process (MMON). By default samples the data dictionary once a week, to collect database feature usage and high-water mark (HWM) statistics.
AUTHID DEFINER
Constants
Name Data Type Value
install check method flags
DBU_INST_ALWAYS_INSTALLED INTEGER 1
DBU_INST_OBJECT INTEGER 2
flag for the test DB features
DBU_INST_TEST INTEGER 4
detection method flags
DBU_DETECT_BY_SQL INTEGER 1
DBU_DETECT_BY_PROCEDURE INTEGER 2
DBU_DETECT_NULL INTEGER 4
high water mark method flags
DBU_HWM_BY_SQL INTEGER 1
DBU_HWM_BY_PROCEDURE INTEGER 2
DBU_HWM_NULL INTEGER 4
flag for the test high water marks
DBU_HWM_TEST INTEGER 8
Dependencies
DBMS_FEATURE_REGISTER_ALLFEAT V$DATABASE WRI$_DBU_FEATURE_USAGE
DBMS_FEATURE_REGISTER_ALLHWM V$INSTANCE WRI$_DBU_HIGH_WATER_MARK
DBMS_FEATURE_USAGE_INTERNAL WRI$_DBU_FEATURE_METADATA WRI$_DBU_HWM_METADATA
DBMS_STANDARD    
Documented No
First Available 10.2.0.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsfus.plb

Also view: catfusrg.sql
Subprograms
 
REGISTER_DB_FEATURE
Track feature usage using a SQL statement dbms_feature_usage.register_db_feature(
feature_name           IN VARCHAR2,
install_check_method   IN NUMBER,
install_check_logic    IN VARCHAR2,
usage_detection_method IN NUMBER,
usage_detection_logic  IN VARCHAR2
feature_description    IN VARCHAR2);
set linesize 161
col name format a38
col description format a120

SELECT name, description
FROM dba_feature_usage_statistics
ORDER BY 1;

SELECT name, detected_usages
FROM dba_feature_usage_statistics
ORDER BY 1;

-- to register the use of function-based indexes an install check
-- is not required: The detection method is to use a SQL query


DECLARE
 sql_str CONSTANT VARCHAR2(100) := 'SELECT COUNT(*), 0, NULL FROM
 dba_indexes ' || 'WHERE index_type = ''FUNCTION-BASED NORMAL''';
BEGIN
  dbms_feature_usage.register_db_feature('User FB Index',
  dbms_feature_usage.DBU_INST_ALWAYS_INSTALLED, NULL,
  dbms_feature_usage.DBU_DETECT_BY_SQL, sql_str,
  'User created function-based index');
END;
/

SELECT name, description
FROM dba_feature_usage_statistics
ORDER BY 1;

SELECT *
FROM dba_feature_usage_statistics
WHERE name = 'User FB Index';

SELECT name, detected_usages
FROM dba_feature_usage_statistics
ORDER BY 1;

SELECT *
FROM wri$_dbu_feature_metadata
WHERE name LIKE 'User FB%';
Track feature usage using a stored procedure -- to register the HTML_DB (an install check is required and the detection method uses a PL/SQL procedure

CREATE OR REPLACE PROCEDURE oratext_test (
 feature_boolean OUT NOCOPY NUMBER,
 auxiliary_count OUT NOCOPY NUMBER,
 feature_info    OUT NOCOPY CLOB) AUTHID DEFINER IS
BEGIN
  SELECT COUNT(*)
  INTO feature_boolean
  FROM dba_users
  WHERE username = 'CTXSYS';

  auxiliary_count := 0;
  feature_info := NULL;
END oratext_test;
/

DECLARE
 monproc    CONSTANT VARCHAR2(50) := 'OraText_Test';
 is_present CONSTANT VARCHAR2(60) := 'CTXSYS.CONTAINS'
BEGIN
  dbms_feature_usage.register_db_feature('Oracle Text',
  dbms_feature_usage.DBU_INST_OBJECT, is_present,
  dbms_feature_usage.DBU_DETECT_BY_PROCEDURE, monproc, 'Oracle Text');
END;
/
 
REGISTER_HIGH_WATER_MARK
Track high-water marks dbms_feature_usage.register_high_water_mark(
hwm_name   IN VARCHAR2,
hwm_method IN NUMBER,
hwm_logic  IN VARCHAR2,
hwm_desc   IN VARCHAR2);
set linesize 121
col name format a25
col description format a60

SELECT name, version, highwater, description
FROM dba_high_water_mark_statistics;

-- to register the number of user defined function based indexes

DECLARE
 sql_str CONSTANT VARCHAR2(100) := 'SELECT COUNT(*)
 FROM dba_indexes WHERE index_type = ''FUNCTION-BASED NORMAL''';

BEGIN
  dbms_feature_usage.register_high_water_mark('User FBIs',
  dbms_feature_usage.DBU_HWM_BY_SQL, sql_str, 'Number of User Created FBIs');
END;
/

SELECT name, highwater, description
FROM dba_high_water_mark_statistics
WHERE name LIKE 'User%';

Related Topics
ADDM
ASH
AWR
DBMS_FEATURE_USAGE_INTERNAL
DBMS_FEATURE_USAGE_REPORT
Feature Usage Procedures
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved