Oracle DBMS_MANAGEMENT_PACKS
Version 19.3

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 Provides limited control of manageability features to be used even when diagnostic and tuning pack licenses are not available. What this verbiage actually means, given the package is undocumented, is far from clear.
AUTHID DEFINER
Constants
Name Data Type Value
DIAGNOSTIC_PACK VARCHAR2(30) 'DIAGNOSTIC'
TUNING_PACK VARCHAR2(30) 'TUNING'
Dependencies
DBA_ADVISOR_EXECUTIONS DBMS_AUTO_TASK_ADMIN DBMS_SYNC_REFRESH_INTERNAL
DBA_ADVISOR_TASKS DBMS_IREFRESH DBMS_SYSTEM
DBA_AUTOTASK_CLIENT DBMS_ISNAPSHOT DBMS_SYS_ERROR
DBA_AUTOTASK_WINDOW_CLIENTS DBMS_LOB DBMS_WORKLOAD_REPOSITORY
DBA_HIST_BASELINE DBMS_MVIEW_STATS DUAL
DBA_HIST_BASELINE_TEMPLATE DBMS_MVIEW_STATS_INTERNAL PRVT_ADVISOR
DBA_HIST_SNAPSHOT DBMS_REFRESH PRVT_HDM
DBA_HIST_WR_CONTROL DBMS_SNAPSHOT_KKXRCA PRVT_SMGUTIL
DBA_SQLSET DBMS_SQLTUNE PRVT_SQLPROF_INFRA
DBA_SQLSET_REFERENCES DBMS_SQLTUNE_INTERNAL V$DATABASE
DBA_SQL_PROFILES DBMS_STANDARD V$PARAMETER
DBMS_ADVISOR DBMS_SWRF_INTERNAL  
Documented No
First Available 11.1.0.6
Security Model Owned by SYS with EXECUTE granted to the DBSNMP role
Source {ORACLE_HOME}/rdbms/admin/dbmsmp.sql
Subprograms
 
CHECK_PACK_ENABLED
Check if pack license is declared to the system via the system parameter "control_management_pack_license" dbms_management_packs.check_pack_enabled(pack_name IN VARCHAR2);
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%management_pack%';

exec dbms_management_packs.check_pack_enabled('DIAGNOSTIC');

exec dbms_management_packs.check_pack_enabled('TUNING');
 
MODIFY_AWR_SETTINGS
Modify the AWR snapshot settings

Interval of 0 disables shapshots

Note: This functionality also exists in DBMS_WORKLOAD_REPOSITORY's
MODIFY_SNAPSHOT_SETTINGS proc
dbms_management_packs.modify_awr_settings(
retention IN NUMBER DEFAULT NULL,  -- in minutes (1 day-100 yrs)
interval  IN NUMBER DEFAULT NULL); -- in minutes (10 min-100 yrs)
set linesize 121
col retention format a20
col snap_interval format a20

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;

-- 2 years = 60*24*365.25*2 = 1051920

exec dbms_management_packs.modify_awr_settings(1051920, 20);

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control;
 
PURGE
Remove/deactivate objects in the database that are inconsistent with the proposed setting of the "control_management_pack_access" parameter dbms_management_packs.purge(license_level IN VARCHAR2);
exec dbms_management_packs.purge('TUNING');
 
PURGE_AWR
Purge all AWR data from the system dbms_management_packs.purge_awr;
exec dbms_management_packs.purge_awr;
 
PURGE_SQLSETS (new 18c)
Purge all SQLSETS from the system dbms_management_packs.purge_sqlsets(buffer IN OUT NOCOPY CLOB);
TBD
 
REPORT
Get a text report of what changes will be done to the system if the "purge" procedure is called with a specific level dbms_management_packs.report(license_level IN VARCHAR2)
RETURN CLOB;
set long 1000000

SELECT dbms_management_packs.report('DIAGNOSTIC+TUNING')
FROM dual;

Related Topics
Active Session History
ASH Report
AWRINFO_UTIL
AWR Report
Built-in Functions
Built-in Packages
DBMS_AWR_REPORT_LAYOUT
DBMS_MANAGEMENT_BOOTSTRAP
DBMS_WORKLOAD_REPOSITORY
Files of Interest
Startup Parameters
What's New In 18cR3
What's New In 19cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
DBSecWorx