Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\collections\collections_pkgbodys.sql
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
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de