Oracle DBMS_MANAGEMENT_PACKS
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
BSLN_INTERNAL DBMS_ADDM DBMS_WORKLOAD_REPLAY
DBA_ADVISOR_EXECUTIONS DBMS_ADVISOR DBMS_WORKLOAD_REPOSITORY
DBA_ADVISOR_TASKS DBMS_ASH_INTERNAL DUAL
DBA_AUTOTASK_CLIENT DBMS_AUTO_TASK_ADMIN PRVTEMX_CELL
DBA_AUTOTASK_WINDOW_CLIENTS DBMS_LOB PRVT_ADVISOR
DBA_HIST_BASELINE DBMS_PERF PRVT_AWR_VIEWER
DBA_HIST_BASELINE_TEMPLATE DBMS_SQLDIAG PRVT_HDM
DBA_HIST_SNAPSHOT DBMS_SQLTUNE PRVT_SMGUTIL
DBA_HIST_WR_CONTROL DBMS_STANDARD PRVT_SQLPROF_INFRA
DBA_SQLSET DBMS_STREAMS_ADV_ADM_UTL V$DATABASE
DBA_SQLSET_REFERENCES DBMS_SWRF_INTERNAL V$PARAMETER
DBA_SQL_PROFILES DBMS_SYSTEM  
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;
 
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
AWR
DBMS_WORKLOAD_REPOSITORY
Packages

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-2014 Daniel A. Morgan All Rights Reserved