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