Oracle | DBMS_FEATURE

Oracle DBMS_FEATURE_USAGE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvsfus.plb

Also view: catfusrg.sql

Subprograms

REGISTER_DB_FEATURE

REGISTER_HIGH_WATER_MARK

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%';