Oracle DBMS_FEATURE_USAGE
Version 18.1.0.0

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
Built-in Functions
Built-in Packages
CARTRIDGE
DBMS_FEATURE_USAGE_INTERNAL
DBMS_FEATURE_USAGE_REPORT
Feature Usage Procedures
What's New In 12cR2
What's New In 18cR1

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-2017 Daniel A. Morgan All Rights Reserved