CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_MANAGEMENT_PACKS
Version 11.1
 
General
Note: A package for limited control of manageability features to be used even when diagnostic and tuning pack licenses are not available
Source {ORACLE_HOME}/rdbms/admin/dbmsmp.sql
First Available 11.1
Constants
Name Data Type Value
DIAGNOSTIC_PACK VARCHAR2(30) 'DIAGNOSTIC'
TUNING_PACK VARCHAR2(30) 'TUNING'

Dependencies
BSLN_INTERNAL DBMS_LOB
DBA_ADVISOR_EXECUTIONS DBMS_SQLDIAG
DBA_ADVISOR_TASKS DBMS_SQLTUNE
DBA_AUTOTASK_CLIENT DBMS_STREAMS_ADV_ADM_UTL
DBA_AUTOTASK_WINDOW_CLIENTS DBMS_SWRF_INTERNAL
DBA_HIST_BASELINE DBMS_SYSTEM
DBA_HIST_BASELINE_TEMPLATE DBMS_WORKLOAD_REPOSITORY
DBA_HIST_SNAPSHOT DUAL
DBA_HIST_WR_CONTROL GV$PARAMETER
DBA_SQLSET PRVT_ADVISOR
DBA_SQLSET_REFERENCES PRVT_HDM
DBA_SQL_PROFILES PRVT_SMGUTIL
DBMS_ADDM PRVT_SQLPROF_INFRA
DBMS_ADVISOR V$DATABASE
DBMS_AUTO_TASK_ADMIN  
Security Model Owned by SYS. No privileges are granted.
 
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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [134 users online]    © 2010 psoug.org