Oracle DBMS_MANAGEMENT_PACKS
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 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
AWR_PDB_BASELINE DBMS_ADVISOR DBMS_SYS_ERROR
AWR_PDB_BASELINE_TEMPLATE DBMS_ASH_INTERNAL DBMS_WORKLOAD_REPLAY_I
AWR_PDB_SNAPSHOT DBMS_AUTO_TASK_ADMIN DBMS_WORKLOAD_REPOSITORY
AWR_PDB_WR_CONTROL DBMS_LOB DUAL
BSLN_INTERNAL DBMS_PERF PRVTEMX_CELL
DBA_ADVISOR_EXECUTIONS DBMS_SQLDIAG PRVT_ADVISOR
DBA_ADVISOR_TASKS DBMS_SQLTUNE PRVT_AWR_VIEWER
DBA_AUTOTASK_CLIENT DBMS_SQLTUNE_INTERNAL PRVT_HDM
DBA_AUTOTASK_WINDOW_CLIENTS DBMS_STANDARD PRVT_SMGUTIL
DBA_SQLSET DBMS_SWRF_INTERNAL PRVT_SQLPROF_INFRA
DBA_SQL_PROFILES DBMS_SYSTEM V$PARAMETER
DBMS_ADDM    
Documented No
First Available 11.1
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
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 19c
What's New In 20c-21c

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