Oracle | DBMS_FEATURE_USAGE

Oracle DBMS_FEATURE_USAGE_INTERNAL 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 Internal utilities supporting feature usage monitoring with DBMS_FEATURE_USAGE

AUTHID DEFINER

Dependencies

DBMS_ASSERT	USER$	WRI$_DBU_HIGH_WATER_MARK
DBMS_FEATURE_USAGE	V$DATABASE	WRI$_DBU_HWM_METADATA
DBMS_SQL	V$INSTANCE	X$KSPPI
DBMS_STANDARD	WRI$_DBU_FEATURE_METADATA	X$KSPPSV
OBJ$	WRI$_DBU_FEATURE_USAGE

Documented No

Exceptions

Error Code	Reason
ORA-20015	Cleanup on local Database id for DB Features Usage not allowed

First Available 10.2

Security Model Owned by SYS with no privileges granted

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

Subprograms

CLEANUP_DATABASE	SAMPLE_ONE_FEATURE	SAMPLE_ONE_HWM
EXEC_DB_USAGE_SAMPLING

CLEANUP_DATABASE

Note I originally wrote with respect to this procedure what appeared reasonable at the time.
This function appears to only be executable using TRUE when executed from a remote database ... which makes sense if you are Oracle Corp. and don't want your customers purging past misdeeds.

But Martin Berger has corrected me and it is Martin's description that appears to the left of the demo.

This procedure is, in fact, not "executed from a remote database".

IF CLEANUP_LOCAL is FALSE, all entries for DBIDs which are not current DBID are deleted (only from DBA_FEATURE_USAGE_STATISTICS, not from DBA_HIGH_WATER_STATISTICS).

If CLEANUP_LOCAL != FALSE
IF _awr_corrupt_mode or _awr_restrict_mode are not 'FALSE', DBA_FEATURE_USAGE_STATISTICS is cleared. dbms_feature_usage_internal(cleanup_local IN BOOLEAN);

col name format a55

      

      SELECT name, detected_usages, total_samples

      FROM dba_feature_usage_statistics

      ORDER BY 1;

      

      exec dbms_feature_usage_internal.cleanup_database(FALSE);

      

      SELECT name, detected_usages, total_samples

      FROM dba_feature_usage_statistics

      ORDER BY 1;

      

      exec dbms_feature_usage_internal.cleanup_database(TRUE);

      *

      ERROR at line 1:

      ORA-20015: Cleanup on local Database id for DB Feature Usage not allowed

      ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 839

      ORA-06512: at line 1

EXEC_DB_USAGE_SAMPLING

Collects usage statistics for all features dbms_feature_usage_internal(curr_date IN DATE);

SELECT MAX(last_usage_date)

      FROM dba_feature_usage_statistics;

      

      exec dbms_feature_usage_internal.exec_db_usage_sampling(SYSDATE);

      

      SELECT MAX(last_usage_date)

      FROM dba_feature_usage_statistics;

SAMPLE_ONE_FEATURE

Runs the stored procedure identified as the tool for collecting statistics on the named feature dbms_feature_usage_internal(feat_name IN VARCHAR2);

SELECT name, detected_usages, total_samples, last_usage_date

      FROM dba_feature_usage_statistics

      WHERE name = 'Services';

      

      exec dbms_service.create_service('UW', 'u.washington.edu');

      exec dbms_service.start_service('UW', 'orabase');

      

      exec dbms_feature_usage_internal.sample_one_feature('Services');

      

      SELECT name, detected_usages, total_samples, last_usage_date

      FROM dba_feature_usage_statistics

      WHERE name = 'Services';

      

      exec dbms_service.stop_service('UW', 'orabase');

      exec dbms_service.delete_service('UW');

SAMPLE_ONE_HWM

Runs the stored procedure identified as the tool for collecting statistics on the named high water mark dbms_feature_usage_internal(hwm_name IN VARCHAR2);

SELECT name, highwater, last_value

      FROM dba_high_water_mark_statistics

      WHERE name = 'USER_TABLES';

      

      CREATE TABLE uwclass.user_table(

      testcol DATE);

      

      exec dbms_feature_usage_internal.sample_one_hwm('USER_TABLES');

      

      SELECT name, highwater, last_value

      FROM dba_high_water_mark_statistics

      WHERE name = 'USER_TABLES';

      

      DROP TABLE uwclass.user_table PURGE;