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