CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_FEATURE_USAGE

Version 11.1
 
General Information
Note: MMON collects statistics 
Source {ORACLE_HOME}/rdbms/admin/prvsfus.plb
First Available 10.1

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
dba_feature_usage_statistics gv$instance 
dba_high_water_mark_statistics wri$_dbu_feature_metadata
dbms_feature_register_allfeat wri$_dbu_feature_usage
dbms_feature_register_allhwm wri$_dbu_high_water_mark
dbms_feature_usage wri$_dbu_hwm_metadata
dbms_feature_usage_interval wri$_dbu_usage_sample
gv$database
Object Privileges execute on dbms_feature_usage
GRANT execute ON dbms_feature_usage TO uwclass;
Process Manageability Monitor Process (MMON). By default samples the data dictionary once a week, to collect database feature usage and high-water mark (HWM) statistics.
Related Scripts catfusrg.sql
 
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 170
col name format a43
col description format a126

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
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);
-- 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) 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

SELECT name, version, highwater
FROM dba_high_water_mark_statistics;

col description format a60

SELECT name, highwater, description
FROM dba_high_water_mark_statistics
WHERE version = '10.1.0.3.0';

-- 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 version = '10.2.0.3.0';
 
Related Topics
ADDM
ASH
AWR
DBMS_FEATURE_USAGE_REPORT
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [251 users online]    © 2010 psoug.org