Rem drv: <create type="pkgbodys" pos="beacon/beacon_pkgbodys.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_pkgbodys.sql /st_emcore_10.2.0.4.2db11.2/2 2009/02/26 20:39:36 jsadras Exp $
Rem
Rem collections_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem collections_pkgbodys.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem jsadras 11/19/08 - remove obsolete code
Rem jsadras 09/23/08 - Bug:7425928, remove execute immediate
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem jsadras 01/20/05 - bug_4133682,mgmt_metric.get_metric_guid
Rem ktlaw 01/11/05 - add repmgr header
Rem jsadras 09/23/04 - deprecate run_collections
Rem rpinnama 09/13/04 - Remove is_repsoitory check as we are joining
Rem mgmt_collections_rep
Rem aholser 05/17/04 - better tracing
Rem aholser 05/14/04 -
Rem lgloyd 11/06/03 - shadow rep collections table
Rem lgloyd 11/05/03 -
Rem lgloyd 11/03/03 - performance work
Rem dcawley 09/12/03 - Check for invalid eval func
Rem streddy 07/10/03 - Added message and message_nlsid
Rem aholser 03/26/03 - set error level on collection errors
Rem dcawley 02/18/03 - Add delete_thresholds
Rem ancheng 02/14/03 - target version fix
Rem dcawley 01/28/03 - Fix metric versioning
Rem vnukal 01/15/03 - changes for agent collection metadata -streddy
Rem dcawley 09/23/02 - Update last_load_time
Rem skini 07/12/02 - Change in target_name column size
Rem dcawley 06/04/02 - Remove example evaluation procedure.
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/15/02 - Created
Rem
----------------------
-- The package body --
----------------------
CREATE OR REPLACE PACKAGE BODY EMD_COLLECTION
IS
--
-- PURPOSE
-- Function to format a default message to add to a severity when
-- no message is provided in the associated threshold. See
-- FORMAT_SEVERITY_MESSAGE for more details.
--
-- PARAMETERS
-- V_METRIC_NAME - name of the metric
-- V_METRIC_COLUMN - name of the metric column
-- V_METRIC_LABEL - label of the metric
-- V_COLUMN_LABEL - label of the metric column
-- V_KEY_VALUE - name of the key column value
-- V_THRESHOLD - the threshold that was above or below
-- V_VALUE - the value of the metric
-- V_TYPE - the type of the severity
-- 0 - CLEAR
-- 1 - WARNING
-- 2 - CRITICAL
FUNCTION FORMAT_DFLT_SEVERITY_MESSAGE(v_metric_name IN VARCHAR2,
v_metric_column IN VARCHAR2,
v_metric_label IN VARCHAR2,
v_column_label IN VARCHAR2,
v_key_value IN VARCHAR2,
v_threshold IN VARCHAR2,
v_value IN VARCHAR2,
v_type IN NUMBER)
RETURN VARCHAR2
IS
msg VARCHAR2(4000);
BEGIN
IF v_column_label IS NOT NULL AND v_column_label <> ' '
THEN
msg := v_column_label;
ELSIF v_metric_column IS NOT NULL AND v_metric_column <> ' '
THEN
msg := v_metric_column;
ELSIF v_metric_label IS NOT NULL AND v_metric_label <> ' '
THEN
msg := v_metric_label;
ELSE
msg := v_metric_name;
END IF;
IF v_key_value IS NOT NULL AND v_key_value <> ' '
THEN
msg := msg || ' for ' || v_key_value;
END IF;
IF v_type = 0
THEN
msg := msg || ' is below the warning threshold (' ||
v_threshold ||'). Current value: ' || v_value;
ELSIF v_type = 1
THEN
msg := msg || ' exceeded the warning threshold (' ||
v_threshold ||'). Current value: ' || v_value;
ELSIF v_type = 2
THEN
msg := msg || ' exceeded the critical threshold (' ||
v_threshold ||'). Current value: ' || v_value;
END IF;
RETURN msg;
END FORMAT_DFLT_SEVERITY_MESSAGE;
--
-- PURPOSE
-- Returns an URL encoding string
-- The only characters that are encoded are '&', '%', and ';'
-- Used by FORMAT_SEVERITY_MESSAGE
--
FUNCTION URL_ENCODE_STRING(v_string IN VARCHAR2)
RETURN VARCHAR2
IS
l_mod_string VARCHAR2(4000) := v_string;
BEGIN
IF (v_string IS NULL) THEN
RETURN NULL;
END IF;
IF (INSTR(l_mod_string, '&') != 0) THEN
l_mod_string := REPLACE(l_mod_string, '&', '%26');
END IF;
IF (INSTR(l_mod_string, '%') != 0) THEN
l_mod_string := REPLACE(l_mod_string, '%', '%25');
END IF;
IF (INSTR(l_mod_string, ';') != 0) THEN
l_mod_string := REPLACE(l_mod_string, ';', '%3b');
END IF;
RETURN l_mod_string;
END URL_ENCODE_STRING;
--
-- PURPOSE
-- Appends a value to nls_params
-- Used by FORMAT_SEVERITY_MESSAGE
--
PROCEDURE APPEND_MESSAGE_PARAM(v_nls_params IN OUT VARCHAR2,
v_param IN VARCHAR2)
IS
BEGIN
IF (v_nls_params IS NULL) THEN
v_nls_params := v_param;
ELSE
v_nls_params := v_nls_params || '&' || v_param;
END IF;
END;
-- PURPOSE
-- Appends nlsid to nls_params
-- Used by FORMAT_SEVERITY_MESSAGE
PROCEDURE APPEND_MESSAGE_PARAM_NLSID(v_nls_params IN OUT VARCHAR2,
v_nlsid IN VARCHAR2)
IS
BEGIN
IF (v_nlsid IS NULL) THEN
RETURN;
END IF;
IF (v_nls_params IS NULL) THEN
v_nls_params := v_nlsid;
ELSE
v_nls_params := v_nls_params || ';' || v_nlsid;
END IF;
END;
-- PURPOSE
-- Returns NLSID for a metric
FUNCTION GET_METRIC_NLSID(v_target_guid IN RAW,
v_metric_name IN VARCHAR2)
RETURN VARCHAR2
IS
l_nlsid VARCHAR2(64);
BEGIN
-- Get the NLS ID from the mgmt_metrics table
SELECT m.metric_label_nlsid
INTO l_nlsid
FROM mgmt_targets t, mgmt_metrics m
WHERE t.target_guid = v_target_guid
AND m.target_type = t.target_type
AND m.metric_name = v_metric_name
AND m.metric_column = ' '
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ');
RETURN l_nlsid;
EXCEPTION
-- nlsid not defined
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
-- PURPOSE
-- Returns NLSID for a metric/column
FUNCTION GET_METRIC_COLUMN_NLSID(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_metric_column IN VARCHAR2)
RETURN VARCHAR2
IS
l_nlsid VARCHAR2(64);
BEGIN
-- Get the NLS ID from the mgmt_metrics table
SELECT m.column_label_nlsid
INTO l_nlsid
FROM mgmt_targets t, mgmt_metrics m
WHERE t.target_guid = v_target_guid
AND m.target_type = t.target_type
AND m.metric_name = v_metric_name
AND m.metric_column = v_metric_column
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ');
RETURN l_nlsid;
EXCEPTION
-- nlsid not defined
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
--
-- PURPOSE
-- Builds the message for a severity. This implementation is based on
-- how agent formats the message based on a templated message specified in
-- the metadata file. See nmecc.c for agent side implementation.
--
PROCEDURE FORMAT_SEVERITY_MESSAGE(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_metric_column IN VARCHAR2,
v_metric_label IN VARCHAR2,
v_column_label IN VARCHAR2,
v_key_value IN VARCHAR2,
v_warning_threshold IN VARCHAR2,
v_critical_threshold IN VARCHAR2,
v_num_occurences IN NUMBER,
v_value IN VARCHAR2,
v_sev_type IN NUMBER,
v_msg_template IN VARCHAR2,
v_metric_result IN MGMT_METRIC_RESULTS,
v_message OUT VARCHAR2,
v_nls_params OUT VARCHAR2)
IS
l_idx1 NUMBER := 0;
l_idx2 NUMBER := 0;
l_start_pos NUMBER := 1;
l_keyword VARCHAR2(64);
l_value VARCHAR2(256);
l_nlsid VARCHAR2(64);
l_threshold VARCHAR2(256);
BEGIN
dbms_output.put_line('FORMAT_SEVERITY_MESSAGE:' ||v_metric_name || '/' || v_metric_column ||
'/msg=' || v_msg_template);
IF (v_msg_template IS NULL) THEN
l_threshold := v_warning_threshold;
IF (v_sev_type = 2) THEN
l_threshold := v_critical_threshold;
END IF;
v_message := FORMAT_DFLT_SEVERITY_MESSAGE(v_metric_name, v_metric_column,
v_metric_label, v_column_label,
v_key_value, l_threshold,
v_value, v_sev_type);
RETURN;
END IF;
l_idx1 := INSTR(v_msg_template, '%', l_idx2 + 1, 1);
v_message := '';
v_nls_params := NULL;
WHILE (l_idx1 <> 0) LOOP
l_nlsid := NULL;
l_idx2 := INSTR(v_msg_template, '%', l_idx1+1, 1);
-- End the loop if the end % is missing.
IF (l_idx2 = 0) THEN
GOTO end_loop;
END IF;
-- Copy over the template data to output message
IF (l_start_pos < l_idx1) THEN
v_message := v_message ||
SUBSTR(v_msg_template, l_start_pos, (l_idx1 - l_start_pos));
END IF;
l_start_pos := l_idx2 + 1;
l_keyword := SUBSTR(v_msg_template, l_idx1+1, (l_idx2 - l_idx1-1));
IF (l_keyword IS NULL) THEN
l_value := '%';
ELSIF (l_keyword = 'value') THEN
l_value := v_value;
ELSIF (l_keyword = 'target') THEN
SELECT target_name INTO l_value
FROM mgmt_targets
WHERE target_guid = v_target_guid;
ELSIF (l_keyword = 'metric_id') THEN
l_value := v_metric_name;
l_nlsid := get_metric_nlsid(v_target_guid, v_metric_name);
ELSIF (l_keyword = 'warning_threshold') THEN
l_value := v_warning_threshold;
ELSIF (l_keyword = 'critical_threshold') THEN
l_value := v_critical_threshold;
ELSIF (l_keyword = 'num_of_occur') THEN
l_value := v_num_occurences;
ELSIF (l_keyword = 'severity') THEN
IF (v_sev_type = 0) THEN
l_value := 'CLEAR';
ELSIF (v_sev_type = 1) THEN
l_value := 'WARNING';
ELSIF (v_sev_type = 2) THEN
l_value := 'CRITICAL';
ELSE
l_value := 'UNDEFINED';
END IF;
ELSIF (l_keyword = 'threshold') THEN
IF (v_sev_type = 0 OR v_sev_type = 1) THEN
l_value := v_warning_threshold;
ELSE
l_value := v_critical_threshold;
END IF;
ELSIF (l_keyword = 'columnName') THEN
l_value := v_column_label;
l_nlsid :=
get_metric_column_nlsid(v_target_guid, v_metric_name, v_metric_column);
ELSIF (l_keyword = 'keyValue') THEN
l_value := v_key_value;
ELSE
l_value := '%' || l_keyword;
-- Check if the keyword is some other column name
-- within the same metric
FOR i in 1..v_metric_result.COUNT LOOP
IF (v_metric_result(i).metric_column = l_keyword) THEN
l_value := v_metric_result(i).metric_value;
END IF;
END LOOP;
END IF;
dbms_output.put_line('keyword = ' || l_keyword || ';value = ' || l_value);
-- Substitute the keyword with the value
v_message := v_message || l_value;
append_message_param(v_nls_params, url_encode_string(l_value));
-- Also add the value to the NLS params
IF (l_nlsid IS NOT NULL) THEN
append_message_param_nlsid(v_nls_params, url_encode_string(l_nlsid));
END IF;
l_idx1 := INSTR(v_msg_template, '%', l_idx2+1, 1);
END LOOP;
<<end_loop>>
IF (l_start_pos < LENGTH(v_msg_template)) THEN
v_message := v_message || SUBSTR(v_msg_template, l_start_pos);
END IF;
dbms_output.put_line('FORMAT_SEVERITY_MESSAGE: message = ' || v_message
|| ' params = ' || v_nls_params);
END FORMAT_SEVERITY_MESSAGE;
-- PURPOSE
-- To determine and run all scheduled collections
PROCEDURE RUN_COLLECTIONS
IS
iSQL VARCHAR2(4000);
metric_result MGMT_METRIC_RESULTS;
key_val VARCHAR2(64) := ' ';
tkey_val VARCHAR2(64) := ' ';
mguid RAW(16);
num_values NUMBER := 0;
tname MGMT_TARGETS.target_name%TYPE;
ttype MGMT_TARGETS.target_type%TYPE;
last_sev_code NUMBER := 0;
new_sev_code NUMBER := 0;
mtype NUMBER := 0;
warn_op NUMBER;
warn_threshold VARCHAR2(256);
crit_op NUMBER;
crit_threshold VARCHAR2(256);
m_label VARCHAR2(64);
c_label VARCHAR2(64);
start_time DATE;
end_time DATE;
last_time DATE;
num_oc NUMBER := 1;
num_WAs NUMBER := 0;
num_CAs NUMBER := 0;
message_template VARCHAR2(4000);
message VARCHAR2(4000);
nls_params VARCHAR2(4000);
message_nlsid VARCHAR2(64);
perf_start DATE;
duration NUMBER;
cnt NUMBER := 0;
CURSOR collections IS
SELECT c.target_guid, c.metric_guid, c.store_metric, c.schedule,
c.coll_name, m.metric_name, m.eval_func
FROM MGMT_METRIC_COLLECTIONS_REP r, MGMT_METRIC_COLLECTIONS c, MGMT_METRICS m
WHERE c.suspended = 0 AND
(c.last_collected_timestamp IS NULL
OR c.last_collected_timestamp + c.schedule / 1440 < SYSDATE) AND
c.metric_guid = m.metric_guid AND
r.target_guid = c.target_guid AND
r.metric_guid = c.metric_guid AND
r.coll_name = c.coll_name;
BEGIN
raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,
'emd_collections deprecated please use mgmt_collection') ;
END RUN_COLLECTIONS;
--
-- PURPOSE
-- To add the thresholds for a given collection for one or more columns
-- and/or one or more key values of a metric
--
PROCEDURE ADD_THRESHOLDS(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2,
v_thresholds IN MGMT_THRESHOLDS,
v_delete IN NUMBER DEFAULT 0)
IS
ttype VARCHAR2(64);
mguid RAW(16);
key_val VARCHAR2(64);
BEGIN
-- Check if the old thresholds need to be deleted
IF v_delete = 1
THEN
DELETE_ALL_THRESHOLDS(v_target_guid, v_metric_name, v_collection_name);
END IF;
-- Get the target type
SELECT target_type INTO ttype FROM MGMT_TARGETS
WHERE target_guid = v_target_guid;
-- Add each threshold
FOR i IN v_thresholds.FIRST..v_thresholds.LAST
LOOP
-- Get the metric GUID
mguid := mgmt_metric.get_metric_guid(p_target_type=>ttype,
p_metric_name=>v_metric_name,
p_metric_column=>
v_thresholds(i).metric_column);
IF v_thresholds(i).key_column_value IS NULL
THEN
key_val := ' ';
ELSE
key_val := v_thresholds(i).key_column_value;
END IF;
-- Add the threshold
INSERT INTO MGMT_METRIC_THRESHOLDS (target_guid, metric_guid,
coll_name, key_value,
warning_operator, warning_threshold,
critical_operator, critical_threshold,
num_occurences,
message, message_nlsid)
VALUES (v_target_guid, mguid, v_collection_name, key_val,
v_thresholds(i).warning_operator,
v_thresholds(i).warning_threshold,
v_thresholds(i).critical_operator,
v_thresholds(i).critical_threshold,
v_thresholds(i).num_occurrences,
v_thresholds(i).message,
v_thresholds(i).message_nlsid);
END LOOP;
END ADD_THRESHOLDS;
-- PURPOSE
-- To delete all thresholds for a number of collections
PROCEDURE DELETE_THRESHOLDS(v_collections IN MGMT_COLLECTION_IDS)
IS
tguid RAW(16);
BEGIN
IF v_collections IS NOT NULL AND v_collections.COUNT > 0
THEN
FOR i IN v_collections.FIRST..v_collections.LAST
LOOP
SELECT target_guid INTO tguid FROM MGMT_TARGETS
WHERE target_name = v_collections(i).target_name AND
target_type = v_collections(i).target_type;
DELETE FROM MGMT_METRIC_THRESHOLDS
WHERE target_guid = tguid AND
coll_name = v_collections(i).collection_name AND
metric_guid IN (SELECT metric_guid FROM MGMT_METRICS
WHERE metric_name = v_collections(i).metric_name
AND target_type = v_collections(i).target_type);
END LOOP;
END IF;
END DELETE_THRESHOLDS;
-- PURPOSE
-- To delete all thresholds for a collection
PROCEDURE DELETE_ALL_THRESHOLDS(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2)
IS
BEGIN
DELETE FROM MGMT_METRIC_THRESHOLDS
WHERE target_guid = v_target_guid AND coll_name = v_collection_name AND
metric_guid IN (SELECT metric_guid FROM MGMT_METRICS
WHERE metric_name = v_metric_name AND
target_type=(SELECT target_type FROM MGMT_TARGETS
WHERE target_guid = v_target_guid));
END DELETE_ALL_THRESHOLDS;
-- PURPOSE
-- To start a collection
PROCEDURE START_COLLECTION(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2,
v_store_metric IN VARCHAR2,
v_schedule IN NUMBER DEFAULT 15,
v_schedule_ex IN VARCHAR2 DEFAULT ' ')
IS
mguid RAW(16);
is_rep NUMBER;
BEGIN
-- Get the metric name and column
SELECT m.metric_guid, DECODE(metric_type, 5, 1, 6, 1, 7, 1, 0)
INTO mguid, is_rep
FROM MGMT_TARGETS t, MGMT_METRICS m
WHERE t.target_guid = v_target_guid
AND m.metric_name = v_metric_name
AND m.metric_column = ' '
AND t.target_type = m.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');
INSERT INTO MGMT_METRIC_COLLECTIONS (target_guid, metric_guid, coll_name,
is_repository, store_metric, schedule,
schedule_ex)
VALUES (v_target_guid, mguid, v_collection_name, is_rep, v_store_metric,
v_schedule, v_schedule_ex);
END START_COLLECTION;
-- PURPOSE
-- To modify a collection
PROCEDURE MODIFY_COLLECTION(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2,
v_store_metric IN VARCHAR2,
v_schedule IN NUMBER DEFAULT 15,
v_schedule_ex IN VARCHAR2 DEFAULT ' ')
IS
mguid RAW(16);
BEGIN
SELECT m.metric_guid INTO mguid
FROM MGMT_TARGETS t, MGMT_METRICS m
WHERE t.target_guid = v_target_guid
AND m.metric_name = v_metric_name
AND m.metric_column = ' '
AND t.target_type = m.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');
UPDATE MGMT_METRIC_COLLECTIONS SET store_metric = v_store_metric,
schedule = v_schedule
WHERE target_guid = v_target_guid AND metric_guid = mguid AND
coll_name = v_collection_name;
END MODIFY_COLLECTION;
-- PURPOSE
-- Procedure to describe a collection
PROCEDURE DESCRIBE_COLLECTION(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2,
v_eval_proc OUT VARCHAR2,
v_schedule OUT NUMBER,
v_schedule_ex OUT VARCHAR2,
v_store_metric OUT VARCHAR2,
v_key_column OUT VARCHAR2,
v_thresholds OUT MGMT_THRESHOLDS)
IS
mguid RAW(16);
BEGIN
SELECT m.metric_guid, eval_func, key_column
INTO mguid, v_eval_proc, v_key_column
FROM MGMT_TARGETS t, MGMT_METRICS m
WHERE t.target_guid = v_target_guid
AND m.metric_name = v_metric_name
AND m.metric_column = ' '
AND t.target_type = m.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');
SELECT schedule, schedule_ex, store_metric
INTO v_schedule, v_schedule_ex, v_store_metric
FROM MGMT_METRIC_COLLECTIONS
WHERE target_guid = v_target_guid AND metric_guid = mguid
AND coll_name = v_collection_name;
BEGIN
SELECT MGMT_THRESHOLD(thr.key_value, met.metric_column,
thr.warning_operator, thr.warning_threshold,
thr.critical_operator, thr.critical_threshold,
thr.num_occurences, thr.message, thr.message_nlsid)
BULK COLLECT INTO v_thresholds
FROM MGMT_METRIC_THRESHOLDS thr, MGMT_METRICS met
WHERE thr.target_guid = v_target_guid AND
thr.coll_name = v_collection_name AND
thr.metric_guid = met.metric_guid AND
met.metric_name = v_metric_name
ORDER BY met.metric_column, thr.key_value;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Do nothing
NULL;
END;
END DESCRIBE_COLLECTION;
-- PURPOSE
-- To verify if a collection exists on the given metric.
FUNCTION HAS_COLLECTION(v_target_guid IN RAW,
v_metric_name IN VARCHAR2)
RETURN BOOLEAN
IS
mguid RAW(16);
result NUMBER(2);
BEGIN
SELECT m.metric_guid INTO mguid
FROM MGMT_TARGETS t, MGMT_METRICS m
WHERE t.target_guid = v_target_guid
AND m.metric_name = v_metric_name
AND m.metric_column = ' '
AND t.target_type = m.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');
SELECT count(coll_name) INTO result
FROM MGMT_METRIC_COLLECTIONS
WHERE target_guid = v_target_guid AND metric_guid = mguid;
IF result > 0 THEN
return TRUE;
END IF;
return FALSE;
END HAS_COLLECTION;
-- PURPOSE
-- To stop a collection
PROCEDURE STOP_COLLECTION(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2)
IS
mguid RAW(16);
BEGIN
SELECT m.metric_guid INTO mguid
FROM MGMT_TARGETS t, MGMT_METRICS m
WHERE t.target_guid = v_target_guid
AND m.metric_name = v_metric_name
AND m.metric_column = ' '
AND t.target_type = m.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');
-- Delete the collection
DELETE FROM MGMT_METRIC_COLLECTIONS
WHERE target_guid = v_target_guid AND metric_guid = mguid AND
coll_name = v_collection_name;
END STOP_COLLECTION;
PROCEDURE STOP_COLLECTION(v_target_name IN VARCHAR2,
v_target_type IN VARCHAR2,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2)
IS
tguid RAW(16);
BEGIN
-- Get the target GUID
SELECT target_guid INTO tguid FROM MGMT_TARGETS
WHERE target_name = v_target_name AND target_type = v_target_type ;
STOP_COLLECTION(tguid, v_metric_name, v_collection_name);
END;
-- PURPOSE
-- To stop a collection
--
PROCEDURE STOP_COLLECTIONS(v_collections IN MGMT_COLLECTION_IDS,
v_delete_thresholds IN NUMBER)
IS
tguid RAW(16);
BEGIN
IF v_collections IS NOT NULL AND v_collections.COUNT > 0
THEN
FOR i IN v_collections.FIRST..v_collections.LAST
LOOP
STOP_COLLECTION(v_collections(i).target_name,
v_collections(i).target_type,
v_collections(i).metric_name,
v_collections(i).collection_name);
IF v_delete_thresholds = 1
THEN
SELECT target_guid INTO tguid FROM MGMT_TARGETS
WHERE target_name = v_collections(i).target_name AND
target_type = v_collections(i).target_type;
DELETE_ALL_THRESHOLDS(tguid, v_collections(i).metric_name,
v_collections(i).collection_name);
END IF;
END LOOP;
END IF;
END STOP_COLLECTIONS;
-- PURPOSE
-- To suspend or resume a collection
--
PROCEDURE SUSPEND_COLLECTION(v_target_guid IN RAW,
v_metric_name IN VARCHAR2,
v_collection_name IN VARCHAR2,
v_suspend IN NUMBER)
IS
ttype VARCHAR2(64);
mguid RAW(16);
BEGIN
-- Get the target type
SELECT target_type INTO ttype FROM MGMT_TARGETS
WHERE target_guid = v_target_guid;
SELECT metric_guid INTO mguid FROM MGMT_METRICS
WHERE target_type = ttype AND metric_name = v_metric_name AND
metric_column = ' ';
-- Update the collection
UPDATE MGMT_METRIC_COLLECTIONS SET suspended = v_suspend
WHERE target_guid = v_target_guid AND metric_guid = mguid AND
coll_name = v_collection_name;
END SUSPEND_COLLECTION;
-- PURPOSE
-- To suspend or resume all collections for a target
--
PROCEDURE SUSPEND_COLLECTION(v_target_guid IN RAW,
v_suspend IN NUMBER)
IS
ttype VARCHAR2(64);
mguid RAW(16);
BEGIN
-- Update the collection
UPDATE MGMT_METRIC_COLLECTIONS SET suspended = v_suspend
WHERE target_guid = v_target_guid;
END SUSPEND_COLLECTION;
-- PURPOSE
-- To get a list of key values that have thresholds defined on them
FUNCTION GET_KEY_VALUES_WITH_THRESHOLDS(v_target_guid IN RAW,
v_metric_guid IN RAW)
RETURN SMP_EMD_STRING_ARRAY
IS
key_vals SMP_EMD_STRING_ARRAY;
BEGIN
SELECT DISTINCT key_value BULK COLLECT INTO key_vals
FROM MGMT_METRIC_THRESHOLDS
WHERE key_value IS NOT NULL AND key_value <> ' '
AND target_guid = v_target_guid AND metric_guid IN
(SELECT metric_guid FROM mgmt_metrics
WHERE metric_name = (SELECT metric_name FROM mgmt_metrics
WHERE metric_guid = v_metric_guid));
RETURN key_vals;
END GET_KEY_VALUES_WITH_THRESHOLDS;
END EMD_COLLECTION;
/
show errors