Oracle | DBMS_MANAGEMENT

Oracle DBMS_MANAGEMENT_PACKS 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 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	PURGE	PURGE_SQLSETS
MODIFY_AWR_SETTINGS	PURGE_AWR	REPORT

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;