Oracle | DBMS_MANAGEMENT

Oracle DBMS_MANAGEMENT_PACKS Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

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	DBMS_WORKLOAD_REPLAY_I
AWR_PDB_SNAPSHOT	DBMS_ASH_INTERNAL	DBMS_WORKLOAD_REPOSITORY
AWR_PDB_WR_CONTROL	DBMS_AUTO_TASK_ADMIN	DUAL
BSLN_INTERNAL	DBMS_LOB	PRVTEMX_CELL
DBA_ADVISOR_EXECUTIONS	DBMS_PERF	PRVT_ADVISOR
DBA_ADVISOR_TASKS	DBMS_SQLDIAG	PRVT_AWR_VIEWER
DBA_AUTOTASK_CLIENT	DBMS_SQLTUNE	PRVT_HDM
DBA_AUTOTASK_WINDOW_CLIENTS	DBMS_SQLTUNE_INTERNAL	PRVT_SMGUTIL
DBA_SQLSET	DBMS_STANDARD	PRVT_SQLPROF_INFRA
DBA_SQL_PROFILES	DBMS_SWRF_INTERNAL	V$PARAMETER
DBMS_ADDM	DBMS_SYSTEM

Documented No

First Available 11.1

Security Model Owned by SYS with EXECUTE granted to DBSNMP

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%';

      

      NAME                             VALUE

      -------------------------------- ------------------

      control_management_pack_acccess  DIAGNOSTIC+TUNING

      

      exec dbms_management_packs.check_pack_enabled('DIAGNOSTIC');

      

      PL/SQL procedure successfully completed.

      

      exec dbms_management_packs.check_pack_enabled('TUNING');

      

      PL/SQL procedure successfully completed.

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 and the DBMS_WORKLOAD_REPOISTORY a more logical place for a DBA to make any changes to AWR settings. 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;

      

      RETENTION          SNAP_INTERVAL         TOPNSQL

      ------------------ ------------------ ----------

      +00008 00:00:00.0  +00000 01:00:00.0   200000000

      

      -- calculation

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

      

      exec dbms_management_packs.modify_awr_settings(1051920, 20);

      

      PL/SQL procedure successfully completed.

      

      SELECT retention, snap_interval, topnsql

      FROM wrm$_wr_control;

      

      RETENTION          SNAP_INTERVAL         TOPNSQL

      ------------------ ------------------ ----------

      +00730 12:00:00.0  +00000 00:20:00.0   200000000

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');

      

      PL/SQL procedure successfully completed

PURGE_AWR

Purge all AWR data from the system dbms_management_packs.purge_awr;

exec dbms_management_packs.purge_awr;

      

      PL/SQL procedure successfully completed

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');

      

      DBMS_MANAGEMENT_PACKS.REPORT('DIAGNOSTIC')

      -----------------------------------------------------------

      List of objects to be deleted by DBMS_MANAGEMENT_PACK.PURGE

      Requested license level is diagnostic

      -----------------------------------------------------------

      

      Automatic SQL Tuning Task executions

         total of 19 task executoins to be deleted

      

      Automatic SQL Tuning Task status

         automatic sql tuning task will be disabled in all maintenance windows

      

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

      

      DBMS_MANAGEMENT_PACKS.REPORT('DIAGNOSTIC_TUNING')

      -----------------------------------------------------------

      List of objects to be deleted by DBMS_MANAGEMENT_PACK.PURGE

      Requested license level is diagnostic

      -----------------------------------------------------------