Oracle | DBMS_FEATURE

Oracle DBMS_FEATURE_USAGE Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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