Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\metric\metric_pkgbody.sql
Rem drv: <create type="pkgbodys" pos="ecm/patch/critical/critical_pkgbodys.sql+"/> Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/metric/metric_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2008/09/26 14:26:37 jsadras Exp $ Rem Rem metric_pkgbody.sql Rem Rem Copyright (c) 2004, 2008, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem metric_pkgbody.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 09/23/08 - Bug:7426027, add dbms_assert calls Rem neearora 07/16/06 - Backport neearora_bug-5108394 from main Rem pmodi 04/27/06 - Bug:3900473 Use function to get default Rem retention window for metric data deletion Rem pmodi 07/07/06 - Backport pmodi_bug-3900473 from main Rem pmodi 12/09/05 - Backport jsadras_bug-4727968 from main Rem neearora 04/23/06 - Bug 5108394. Adding function Rem is_server_gen_alert_metric Rem jsadras 11/17/05 - handle dup_val_on_index on Rem mgmt_snapshot_metric_map Rem rpinnama 09/30/05 - Rem jsadras 09/12/05 - add keys_from_mult_colls Rem gan 09/07/05 - bug 4594233, 4595131 Rem rpinnama 09/01/05 - Add copy_metric Rem rpinnama 08/31/05 - Change the type_meta_ver default for Rem delete_metric Rem rpinnama 08/31/05 - Delete threshold, collection and violation data Rem on deleting metric Rem mfidanbo 08/29/05 - close all current severities after inserting the Rem information violation Rem rmarripa 08/18/05 - Rem pmodi 08/04/05 - Bug:4525814 - Exclude partioned tables while Rem deleting metric data Rem mfidanbo 08/02/05 - dont delete sev history and composite keys Rem gan 07/18/05 - add metric metadata callback Rem jsadras 05/09/05 - Bug:4321899 Rem rkpandey 06/20/05 - Moved upsert_metric to EM_REGISTRY Rem gsbhatia 07/01/05 - New repmgr header impl Rem pmodi 06/17/05 - Using EM_CHECK for exec call and Correcting Rem comments Rem pmodi 06/09/05 - Bug:4406767 - Changing call back singnature Rem rpinnama 05/17/05 - Support is_renderable column in mgmt_metrics Rem rpinnama 05/27/05 - Fix 4399189 : Get more attributes for metric Rem jsadras 05/02/05 - is long running support Rem jsadras 05/09/05 - Bug:4321899 Rem pmodi 04/19/05 - upsert_metric added Rem pmodi 04/06/05 - API for remove key value Rem rpinnama 03/03/05 - Make the column label default NULL Rem rpinnama 02/25/05 - Bug 3924067: Add non_thresholded_alerts and Rem keyonly_thresholds columns Rem rpinnama 02/11/05 - Fix 4164974: Handle the duplicate metric additions Rem rpinnama 01/10/05 - Add trace statements in tgttype callback Rem rpinnama 01/06/05 - Procedures to handle mgmt_metric_versions Rem rpinnama 01/06/05 - Add target type addition callback Rem jsadras 12/10/04 - store_metric Rem jsadras 10/19/04 - metric_timing Rem gan 09/14/04 - add remote parameter Rem rpinnama 08/30/04 - Add get_key_columns() function Rem rpinnama 08/19/04 - Add has_push, has_pull attributes Rem rpinnama 08/18/04 - Add is_test_metric Rem rpinnama 08/12/04 - Add update_* API Rem rpinnama 08/05/04 - Populate eval_func Rem rpinnama 08/03/04 - Add category class and category API Rem rpinnama 07/28/04 - Rem rpinnama 07/26/04 - rpinnama_add_policy_api Rem rpinnama 07/23/04 - Created Rem CREATE OR REPLACE PACKAGE BODY em_metric AS PROCEDURE add_metric_row( p_metric_guid IN RAW, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ', p_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_category_prop_1 IN VARCHAR2 DEFAULT ' ', p_category_prop_2 IN VARCHAR2 DEFAULT ' ', p_category_prop_3 IN VARCHAR2 DEFAULT ' ', p_category_prop_4 IN VARCHAR2 DEFAULT ' ', p_category_prop_5 IN VARCHAR2 DEFAULT ' ', p_metric_type IN NUMBER DEFAULT 0, p_usage_type IN NUMBER DEFAULT 0, p_metric_label IN VARCHAR2 DEFAULT NULL, p_metric_label_nlsid IN VARCHAR2 DEFAULT NULL, p_column_label IN VARCHAR2 DEFAULT NULL, p_column_label_nlsid IN VARCHAR2 DEFAULT NULL, p_key_column IN VARCHAR2 DEFAULT NULL, p_key_order IN NUMBER DEFAULT 0, p_num_keys IN NUMBER DEFAULT 0, p_description IN VARCHAR2 DEFAULT ' ', p_description_nlsid IN VARCHAR2 DEFAULT NULL, p_unit IN VARCHAR2 DEFAULT ' ', p_unit_nlsid IN VARCHAR2 DEFAULT NULL, p_short_name IN VARCHAR2 DEFAULT NULL, p_short_name_nlsid IN VARCHAR2 DEFAULT NULL, p_is_for_summary IN NUMBER DEFAULT 0, p_keys_from_mult_colls IN NUMBER DEFAULT 0, p_statefull IN NUMBER DEFAULT 1, p_is_repository IN NUMBER DEFAULT 0, p_author IN VARCHAR2 DEFAULT 'ORACLE', p_source_type IN NUMBER DEFAULT NULL, p_source IN VARCHAR2 DEFAULT NULL, p_is_transposed IN NUMBER DEFAULT 0, p_is_test_metric IN NUMBER DEFAULT 0, p_has_push IN NUMBER DEFAULT 0, p_has_pull IN NUMBER DEFAULT 0, p_remote IN NUMBER DEFAULT 0, p_non_thresholded_alerts IN NUMBER DEFAULT 0, p_keyonly_thresholds IN NUMBER DEFAULT 0, p_is_long_running IN NUMBER DEFAULT 0, p_is_renderable IN NUMBER DEFAULT 1) IS BEGIN INSERT INTO MGMT_METRICS (metric_guid, target_type, metric_name, metric_column, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, metric_type, usage_type, key_column, key_order, num_keys, column_label, column_label_nlsid, metric_label, metric_label_nlsid, description, description_nlsid, unit, unit_nlsid, short_name, short_name_nlsid, is_for_summary, keys_from_mult_colls, statefull, is_repository, author, source_type, source, eval_func, is_transposed, is_test_metric, has_push, has_pull, remote, non_thresholded_alerts, keyonly_thresholds, is_long_running, is_renderable) VALUES (p_metric_guid, p_target_type, p_metric_name, p_metric_column, p_type_meta_ver, p_category_prop_1, p_category_prop_2, p_category_prop_3, p_category_prop_4, p_category_prop_5, p_metric_type, p_usage_type, p_key_column, p_key_order, p_num_keys, p_column_label, p_column_label_nlsid, p_metric_label, p_metric_label_nlsid, p_description, p_description_nlsid, p_unit, p_unit_nlsid, p_short_name, p_short_name_nlsid, p_is_for_summary, p_keys_from_mult_colls, p_statefull, p_is_repository, p_author, p_source_type, p_source, SUBSTR(p_source, 1, 255), p_is_transposed, p_is_test_metric, p_has_push, p_has_pull, p_remote, p_non_thresholded_alerts, p_keyonly_thresholds, p_is_long_running, p_is_renderable); END add_metric_row; PROCEDURE update_metric_row( p_metric_guid IN RAW, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ', p_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_category_prop_1 IN VARCHAR2 DEFAULT ' ', p_category_prop_2 IN VARCHAR2 DEFAULT ' ', p_category_prop_3 IN VARCHAR2 DEFAULT ' ', p_category_prop_4 IN VARCHAR2 DEFAULT ' ', p_category_prop_5 IN VARCHAR2 DEFAULT ' ', p_metric_type IN NUMBER DEFAULT NULL, p_usage_type IN NUMBER DEFAULT NULL, p_metric_label IN VARCHAR2 DEFAULT NULL, p_metric_label_nlsid IN VARCHAR2 DEFAULT NULL, p_column_label IN VARCHAR2 DEFAULT NULL, p_column_label_nlsid IN VARCHAR2 DEFAULT NULL, p_key_column IN VARCHAR2 DEFAULT NULL, p_key_order IN NUMBER DEFAULT NULL, p_num_keys IN NUMBER DEFAULT NULL, p_description IN VARCHAR2 DEFAULT NULL, p_description_nlsid IN VARCHAR2 DEFAULT NULL, p_unit IN VARCHAR2 DEFAULT NULL, p_unit_nlsid IN VARCHAR2 DEFAULT NULL, p_short_name IN VARCHAR2 DEFAULT NULL, p_short_name_nlsid IN VARCHAR2 DEFAULT NULL, p_is_for_summary IN NUMBER DEFAULT NULL, p_keys_from_mult_colls IN NUMBER DEFAULT NULL, p_statefull IN NUMBER DEFAULT NULL, p_is_repository IN NUMBER DEFAULT NULL, p_author IN VARCHAR2 DEFAULT NULL, p_source_type IN NUMBER DEFAULT NULL, p_source IN VARCHAR2 DEFAULT NULL, p_is_transposed IN NUMBER DEFAULT NULL, p_is_test_metric IN NUMBER DEFAULT NULL, p_has_push IN NUMBER DEFAULT NULL, p_has_pull IN NUMBER DEFAULT NULL, p_remote IN NUMBER DEFAULT NULL, p_non_thresholded_alerts IN NUMBER DEFAULT NULL, p_keyonly_thresholds IN NUMBER DEFAULT NULL, p_is_long_running IN NUMBER DEFAULT NULL, p_is_renderable IN NUMBER DEFAULT NULL) IS BEGIN UPDATE MGMT_METRICS SET metric_type = NVL(p_metric_type, metric_type), usage_type = NVL(p_usage_type, usage_type), metric_label = NVL(p_metric_label, metric_label), metric_label_nlsid = NVL(p_metric_label_nlsid, metric_label_nlsid), column_label = NVL(p_column_label, column_label), column_label_nlsid = NVL(p_column_label_nlsid, column_label_nlsid), key_order = NVL(p_key_order, key_order), num_keys = NVL(p_num_keys, num_keys), description = NVL(p_description, description), description_nlsid = NVL(p_description_nlsid, description_nlsid), unit = NVL(p_unit, unit), unit_nlsid = NVL(p_unit_nlsid, unit_nlsid), short_name = NVL(p_short_name, short_name), short_name_nlsid = NVL(p_short_name_nlsid, short_name_nlsid), is_repository = NVL(p_is_repository, is_repository), author = NVL(p_author, author), source_type = NVL(p_source_type, source_type), source = NVL(p_source, source), keys_from_mult_colls = NVL(p_keys_from_mult_colls, keys_from_mult_colls), is_for_summary = NVL(p_is_for_summary, is_for_summary), statefull = NVL(p_statefull, statefull), is_transposed = NVL(p_is_transposed, is_transposed), is_test_metric = NVL(p_is_test_metric, is_test_metric), has_push = NVL(p_has_push, has_push), has_pull = NVL(p_has_pull, has_pull), remote = NVL(p_remote, remote), non_thresholded_alerts = NVL(p_non_thresholded_alerts, non_thresholded_alerts), keyonly_thresholds = NVL(p_keyonly_thresholds, keyonly_thresholds), is_long_running = NVL(p_is_long_running, is_long_running), is_renderable = NVL(p_is_renderable, is_renderable) WHERE target_type = p_target_type AND metric_name = p_metric_name AND metric_column = p_metric_column AND type_meta_ver = p_type_meta_ver AND category_prop_1 = p_category_prop_1 AND category_prop_2 = p_category_prop_2 AND category_prop_3 = p_category_prop_3 AND category_prop_4 = p_category_prop_4 AND category_prop_5 = p_category_prop_5; END update_metric_row; PROCEDURE remove_metric_row( p_metric_guid IN RAW, p_type_meta_ver IN VARCHAR2 DEFAULT NULL) IS BEGIN DELETE FROM mgmt_metrics WHERE metric_guid = p_metric_guid AND type_meta_ver = NVL(p_type_meta_ver, type_meta_ver); END remove_metric_row; -- Internal procedure to expand the boundaries (start and end) -- for metric versioning. If the new start is lower than the old start -- then the start value will be decreased. If the new end is higher than -- the old end, then the end will be increased. PROCEDURE expand_metric_version_row( p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_start_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_end_type_meta_ver IN VARCHAR2 DEFAULT NULL) IS l_min_ver mgmt_metric_versions.start_type_meta_ver%type; l_max_ver mgmt_metric_versions.end_type_meta_ver%type; BEGIN SELECT start_type_meta_ver, end_type_meta_ver INTO l_min_ver, l_max_ver FROM mgmt_metric_versions WHERE target_type = p_target_type AND metric_name = p_metric_name; IF (l_min_ver IS NOT NULL AND em_target.compare_type_meta_vers(p_start_type_meta_ver, l_min_ver)<0) THEN l_min_ver := p_start_type_meta_ver; END IF; IF (l_max_ver IS NOT NULL AND em_target.compare_type_meta_vers(l_max_ver, p_end_type_meta_ver)<0) THEN l_max_ver := p_end_type_meta_ver; END IF; update_metric_version_row(p_target_type, p_metric_name, l_min_ver, l_max_ver); END expand_metric_version_row; -- Adds a row to the metric versions table (MGMT_METRIC_VERSIONS) -- The default values used here are the defaults specified for the table PROCEDURE add_metric_version_row( p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_start_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_end_type_meta_ver IN VARCHAR2 DEFAULT NULL) IS BEGIN INSERT INTO mgmt_metric_versions (target_type, metric_name, start_type_meta_ver, end_type_meta_ver) VALUES (p_target_type, p_metric_name, p_start_type_meta_ver, p_end_type_meta_ver); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN expand_metric_version_row(p_target_type, p_metric_name, p_start_type_meta_ver, p_end_type_meta_ver); END; END add_metric_version_row; PROCEDURE update_metric_version_row( p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_start_type_meta_ver IN VARCHAR2 DEFAULT NULL, p_end_type_meta_ver IN VARCHAR2 DEFAULT NULL) IS BEGIN UPDATE mgmt_metric_versions SET start_type_meta_ver = NVL(p_start_type_meta_ver, start_type_meta_ver), end_type_meta_ver = NVL(p_end_type_meta_ver, end_type_meta_ver) WHERE target_type = p_target_type AND metric_name = p_metric_name; END update_metric_version_row; -- Deletes a row in the metric versions table (MGMT_METRIC_VERSIONS) -- Deletes using the primary key target type, metric name PROCEDURE remove_metric_version_row( p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2) IS BEGIN DELETE FROM mgmt_metric_versions WHERE target_type = p_target_type AND metric_name = p_metric_name; END remove_metric_version_row; PROCEDURE get_metric_info(p_metric_guid IN RAW, p_metric_info OUT NOCOPY METRIC_INFO_REC ) IS l_proc_name CONSTANT VARCHAR2(30) := 'get_metric_info' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||'Enter ', G_MODULE_NAME) ; END IF ; p_metric_info.key_cols := mgmt_short_string_array() ; p_metric_info.metric_cols := mgmt_namevalue_array() ; FOR metric_rec IN ( SELECT DISTINCT m.metric_guid, m.metric_type, m.metric_name, m.num_keys, m.is_repository, m.is_transposed, m.source_type, m.key_order, m.metric_column,keys_from_mult_colls FROM MGMT_METRICS m WHERE (target_type,m.metric_name,type_meta_ver) = (SELECT target_type,metric_name,MAX(type_meta_ver) FROM mgmt_metrics WHERE metric_guid = p_metric_guid GROUP BY target_type,metric_name ) ORDER BY decode(metric_column,' ',1,2), decode(key_order,0,1,0) , key_order, metric_column) LOOP IF metric_rec.metric_column = ' ' THEN p_metric_info.metric_name := metric_rec.metric_name ; p_metric_info.metric_guid := metric_rec.metric_guid ; p_metric_info.num_keys := metric_rec.num_keys ; p_metric_info.metric_type := metric_rec.metric_type ; p_metric_info.is_repository := metric_rec.is_repository ; p_metric_info.is_transposed := metric_rec.is_transposed ; p_metric_info.source_type := metric_rec.source_type; p_metric_info.keys_from_mult_colls := metric_rec.keys_from_mult_colls ; ELSE p_metric_info.metric_cols.extend(1) ; p_metric_info.metric_cols(p_metric_info.metric_cols.count) := mgmt_namevalue_obj.new( metric_rec.metric_column, metric_rec.metric_type) ; IF metric_rec.key_order > 0 THEN p_metric_info.key_cols.extend(1) ; p_metric_info.key_cols(p_metric_info.key_cols.count) := metric_rec.metric_column ; END IF; END IF; END LOOP; END get_metric_info; PROCEDURE get_metric_info_for_target( p_metric_guid IN RAW, p_target_guid IN RAW, p_metric_info OUT NOCOPY METRIC_INFO_REC) IS l_proc_name CONSTANT VARCHAR2(30) := 'get_metric_info_for_target' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||'Enter ' || ' target_guid =[' || p_target_guid || ']' || ' metric_guid =[' || p_metric_guid || ']', G_MODULE_NAME) ; END IF ; p_metric_info.key_cols := mgmt_short_string_array() ; p_metric_info.metric_cols := mgmt_namevalue_array() ; FOR metric_rec IN ( SELECT m.metric_guid, m.metric_type, m.metric_name, m.num_keys, m.is_repository, m.is_transposed, m.source_type, m.key_order, m.metric_column,m.keys_from_mult_colls FROM MGMT_METRICS m, mgmt_targets t WHERE m.metric_name = (SELECT distinct(metric_name) FROM mgmt_metrics WHERE metric_guid = p_metric_guid) AND t.target_guid = p_target_guid AND m.target_type = t.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 = ' ') ORDER BY decode(metric_column,' ',1,2), decode(key_order,0,1,0) , key_order, metric_column) LOOP IF metric_rec.metric_column = ' ' THEN p_metric_info.metric_name := metric_rec.metric_name ; p_metric_info.metric_guid := metric_rec.metric_guid ; p_metric_info.num_keys := metric_rec.num_keys ; p_metric_info.metric_type := metric_rec.metric_type ; p_metric_info.is_repository := metric_rec.is_repository ; p_metric_info.is_transposed := metric_rec.is_transposed ; p_metric_info.source_type := metric_rec.source_type; p_metric_info.keys_from_mult_colls := metric_rec.keys_from_mult_colls ; ELSE p_metric_info.metric_cols.extend(1) ; p_metric_info.metric_cols(p_metric_info.metric_cols.count) := mgmt_namevalue_obj.new( metric_rec.metric_column,metric_rec.metric_type) ; IF metric_rec.key_order > 0 THEN p_metric_info.key_cols.extend(1) ; p_metric_info.key_cols(p_metric_info.key_cols.count) := metric_rec.metric_column ; END IF; END IF; END LOOP; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' Exit ' || ' target_guid =[' || p_target_guid || ']' || ' metric_guid =[' || p_metric_guid || ']' || ' metric name =[' || p_metric_info.metric_name || ']' || ' metric type =[' || p_metric_info.metric_type || ']' || ' num keys =[' || p_metric_info.num_keys || ']', G_MODULE_NAME) ; END IF ; END get_metric_info_for_target; PROCEDURE add_category_class( p_class_name IN VARCHAR2, p_class_name_nlsid IN VARCHAR2 DEFAULT NULL) IS BEGIN UPDATE mgmt_category_classes SET class_name_nlsid = p_class_name_nlsid WHERE class_name = p_class_name; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO mgmt_category_classes (class_name, class_name_nlsid) VALUES (p_class_name, p_class_name_nlsid); END IF; END add_category_class; PROCEDURE remove_category_class( p_class_name IN VARCHAR2) IS BEGIN DELETE FROM mgmt_category_classes WHERE class_name = p_class_name; END remove_category_class; PROCEDURE add_category( p_class_name IN VARCHAR2, p_category_name IN VARCHAR2, p_category_name_nlsid IN VARCHAR2 DEFAULT NULL) IS BEGIN UPDATE mgmt_categories SET category_name_nlsid = p_category_name_nlsid WHERE class_name = p_class_name AND category_name = p_category_name; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO mgmt_categories (class_name, category_name, category_name_nlsid) VALUES (p_class_name, p_category_name, p_category_name_nlsid); END IF; END add_category; PROCEDURE add_categories( p_class_name IN VARCHAR2, p_category_def_list IN MGMT_CATEGORY_DEF_ARRAY DEFAULT NULL) IS BEGIN IF ( (p_category_def_list IS NOT NULL) AND (p_category_def_list.COUNT > 0) ) THEN FOR i IN p_category_def_list.FIRST..p_category_def_list.LAST LOOP add_category(p_class_name, p_category_def_list(i).category_name, p_category_def_list(i).category_name_nlsid); END LOOP; END IF; END add_categories; PROCEDURE remove_category( p_class_name IN VARCHAR2, p_category_name IN VARCHAR2 DEFAULT NULL) IS BEGIN DELETE FROM mgmt_categories WHERE class_name = p_class_name AND category_name = NVL(p_category_name, category_name); END remove_category; PROCEDURE add_object_category( p_object_guid IN RAW, p_object_type IN NUMBER, p_target_type IN VARCHAR2 DEFAULT NULL, p_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_category_class IN VARCHAR2 DEFAULT NULL, p_category_name IN VARCHAR2 DEFAULT NULL) IS BEGIN UPDATE mgmt_category_map SET category_name = p_category_name WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND object_guid = p_object_guid AND class_name = p_category_class; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO mgmt_category_map (target_type, type_meta_ver, object_type, object_guid, class_name, category_name) VALUES (p_target_type, p_type_meta_ver, p_object_type, p_object_guid, p_category_class, p_category_name); END IF; END add_object_category; PROCEDURE add_object_categories( p_object_guid IN RAW, p_object_type IN NUMBER, p_target_type IN VARCHAR2 DEFAULT NULL, p_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_category_list IN MGMT_CATEGORY_ARRAY DEFAULT NULL) IS BEGIN IF ( (p_category_list IS NOT NULL) AND (p_category_list.COUNT > 0)) THEN FOR i IN p_category_list.FIRST..p_category_list.LAST LOOP add_object_category(p_object_guid, p_object_type, p_target_type, p_type_meta_ver, p_category_list(i).class_name, p_category_list(i).category_name); END LOOP; END IF; END add_object_categories; PROCEDURE remove_object_category( p_object_guid IN RAW, p_object_type IN NUMBER, p_target_type IN VARCHAR2 DEFAULT NULL, p_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_category_class IN VARCHAR2 DEFAULT NULL) IS BEGIN DELETE FROM mgmt_category_map WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND object_guid = p_object_guid AND class_name = p_category_class; END remove_object_category; PROCEDURE remove_object_categories( p_object_guid IN RAW, p_object_type IN NUMBER, p_target_type IN VARCHAR2 DEFAULT NULL, p_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_category_list IN MGMT_CATEGORY_ARRAY DEFAULT NULL) IS BEGIN IF ( (p_category_list IS NOT NULL) AND (p_category_list.COUNT > 0)) THEN FOR i IN p_category_list.FIRST..p_category_list.LAST LOOP remove_object_category(p_object_guid, p_object_type, p_target_type, p_type_meta_ver, p_category_list(i).class_name); END LOOP; ELSE -- Delete all categorization details DELETE FROM mgmt_category_map WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND object_guid = p_object_guid; END IF; END remove_object_categories; -- Metric-Snapshot Map PROCEDURE add_metric_snapshot_assoc( p_metric_guid IN RAW, p_snapshot_target_type IN VARCHAR2, p_snapshot_name IN VARCHAR2, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE ) IS l_store_metric NUMBER ; BEGIN -- any non-zero value for p_store_metric is TRUE IF p_store_metric = 0 THEN l_store_metric := 0 ; ELSE l_store_metric := 1 ; END IF ; INSERT INTO mgmt_snapshot_metric_map (snapshot_target_type, snapshot_name, metric_guid,store_metric) VALUES (p_snapshot_target_type, p_snapshot_name, p_metric_guid,l_store_metric); END add_metric_snapshot_assoc; PROCEDURE add_metric_snapshot_assoc( p_metric_guid IN RAW, p_snapshot IN MGMT_SNAPSHOT_OBJ) IS BEGIN add_metric_snapshot_assoc(p_metric_guid, p_snapshot.target_type, p_snapshot.snapshot_name, p_snapshot.store_metric); END add_metric_snapshot_assoc; PROCEDURE add_metric_snapshot_assocs( p_metric_guid IN RAW, p_snapshot_name_list IN MGMT_SNAPSHOT_ARRAY) IS BEGIN IF ( (p_snapshot_name_list IS NOT NULL) AND (p_snapshot_name_list.COUNT > 0) ) THEN FOR snap_ctr IN p_snapshot_name_list.FIRST..p_snapshot_name_list.LAST LOOP BEGIN add_metric_snapshot_assoc(p_metric_guid, p_snapshot_name_list(snap_ctr)); EXCEPTION -- Ignore duplicate metrics in snapshot WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; END LOOP; END IF; END add_metric_snapshot_assocs; PROCEDURE remove_metric_snapshot_assoc( p_metric_guid IN RAW, p_snapshot_target_type IN VARCHAR2, p_snapshot_name IN VARCHAR2) IS BEGIN DELETE FROM mgmt_snapshot_metric_map WHERE metric_guid = p_metric_guid AND snapshot_target_type = p_snapshot_target_type AND snapshot_name = p_snapshot_name; END remove_metric_snapshot_assoc; PROCEDURE remove_metric_snapshot_assocs( p_metric_guid IN RAW, p_snapshot_name_list IN MGMT_SNAPSHOT_ARRAY DEFAULT NULL) IS l_snapshot MGMT_SNAPSHOT_OBJ; BEGIN IF ( (p_snapshot_name_list IS NOT NULL) AND (p_snapshot_name_list.COUNT > 0) ) THEN FOR snap_ctr IN p_snapshot_name_list.FIRST..p_snapshot_name_list.LAST LOOP l_snapshot := p_snapshot_name_list(snap_ctr); remove_metric_snapshot_assoc(p_metric_guid, l_snapshot.target_type, l_snapshot.snapshot_name); END LOOP; ELSE DELETE FROM mgmt_snapshot_metric_map WHERE metric_guid = p_metric_guid; END IF; END remove_metric_snapshot_assocs; PROCEDURE delete_metric_data(p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_type_meta_ver IN VARCHAR2 DEFAULT NULL) IS l_metric_guid mgmt_metrics.metric_guid%TYPE; l_metric_guid_list MGMT_TARGET_GUID_ARRAY; l_proc_name CONSTANT VARCHAR2(30) := 'delete_metric_data' ; l_delete_stmt VARCHAR2(4000); l_counter INTEGER :=0; BEGIN --TO DO : Implement delete metric exception table to exclude tables -- for deletion of metric data. IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Enter ttype = ' || p_target_type || ' (metric_name = ' || p_metric_name || ')' || ' (meta_ver = ' || p_type_meta_ver || ')', G_MODULE_NAME) ; END IF ; -- Get the list of metric guids SELECT DISTINCT metric_guid BULK COLLECT INTO l_metric_guid_list FROM mgmt_metrics WHERE target_type = p_target_type AND metric_name = p_metric_name AND type_meta_ver = NVL(p_type_meta_ver, type_meta_ver); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Metric guid list COUNT ' || l_metric_guid_list.COUNT, G_MODULE_NAME) ; END IF ; -- Delete target metric thresholds FOR assoc_rec IN (SELECT object_guid, object_type, policy_guid FROM mgmt_policy_assoc WHERE object_guid IN (SELECT target_guid FROM MGMT_TARGETS WHERE target_type = p_target_type AND type_meta_ver = NVL(p_type_meta_ver, type_meta_ver)) AND policy_guid IN (SELECT * FROM TABLE(CAST(l_metric_guid_list AS MGMT_TARGET_GUID_ARRAY))) AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET ORDER BY object_guid, policy_guid) LOOP -- Remove object-policy associations, and decrement ctr for any CA assoc. EM_POLICY.remove_object_policy_assoc( p_object_guid => assoc_rec.object_guid, p_policy_guid => assoc_rec.policy_guid, p_coll_name => NULL, -- Remove all collections p_remove_ca_assoc => MGMT_GLOBAL.G_TRUE); -- Remove policy eval details IF (assoc_rec.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN EM_POLICY.remove_policy_eval_details( p_target_guid => assoc_rec.object_guid, p_policy_guid => assoc_rec.policy_guid); END IF; END LOOP; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Removed associations ', G_MODULE_NAME) ; END IF ; -- Get all tables that have both target_guid and metric_guid/policy_guid FOR crec IN ( SELECT o.name table_name, tgc.name target_column_name, mgc.name metric_column_name FROM sys.obj$ o, sys.tab$ t, sys.col$ tgc, sys.col$ mgc WHERE o.owner# = userenv('SCHEMAID') AND tgc.name like ('%TARGET_GUID%') AND tgc.obj# = o.obj# AND ( mgc.name like ('%METRIC_GUID%') OR mgc.name like ('%POLICY_GUID%') ) AND o.name NOT IN ('MGMT_METRICS_RAW', 'MGMT_METRICS_1HOUR', 'MGMT_METRICS_1DAY' ) AND mgc.obj# = o.obj# AND o.name like ('MGMT_%') AND o.obj# = t.obj# AND bitand(t.property, 1) = 0 AND bitand(tgc.property,32) = 0 AND bitand(tgc.property,512) = 0 AND bitand(mgc.property,32) = 0 AND bitand(mgc.property,512) = 0 ORDER BY o.name) LOOP l_delete_stmt := 'DELETE FROM ' || DBMS_ASSERT.ENQUOTE_NAME(crec.table_name,FALSE) || ' WHERE ' || DBMS_ASSERT.ENQUOTE_NAME(crec.target_column_name,FALSE) || ' IN (SELECT target_guid FROM MGMT_TARGETS WHERE target_type = :1 AND type_meta_ver = NVL(:2, type_meta_ver)) ' || ' AND ' || DBMS_ASSERT.ENQUOTE_NAME(crec.metric_column_name,FALSE) || ' IN (SELECT DISTINCT metric_guid FROM MGMT_METRICS WHERE target_type = :3 AND metric_name = :4 AND type_meta_ver = NVL(:5, type_meta_ver)) AND ROWNUM <= ' || mgmt_global.MAX_COMMIT; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' SQL ' || l_delete_stmt, G_MODULE_NAME) ; END IF ; LOOP EXECUTE IMMEDIATE l_delete_stmt USING p_target_type, p_type_meta_ver, p_target_type, p_metric_name, p_type_meta_ver; l_counter := SQL%ROWCOUNT; COMMIT; IF l_counter < mgmt_global.MAX_COMMIT THEN EXIT; END IF; END LOOP; END LOOP; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Exit ttype = ' || p_target_type || ' (metric_name = ' || p_metric_name || ')' || ' (meta_ver = ' || p_type_meta_ver || ')', G_MODULE_NAME) ; END IF ; END delete_metric_data; PROCEDURE copy_metric(p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_from_type_meta_ver IN VARCHAR2, p_to_type_meta_ver IN VARCHAR2) IS l_proc_name CONSTANT VARCHAR2(64) := 'em_metric.copy_metric ' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' Copying metric ' || p_metric_name || ' to version ' || p_to_type_meta_ver, G_MODULE_NAME) ; END IF ; -- NOTE: -- Cannot use INSERT INTO SELECT FROM as it is raising a 4091 mutating table error on -- mgmt_metrics table. FOR met_rec IN (SELECT metric_guid, target_type, metric_name, metric_column, p_to_type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, metric_type, usage_type, metric_label, metric_label_nlsid, column_label, column_label_nlsid, key_column, key_order, num_keys, description, description_nlsid, unit, unit_nlsid, short_name, short_name_nlsid, is_for_summary, keys_from_mult_colls, statefull, is_repository, author, source_type, source, eval_func, is_transposed, is_test_metric, has_push, has_pull, remote, non_thresholded_alerts, keyonly_thresholds, is_long_running, is_renderable FROM mgmt_metrics WHERE target_type = p_target_type AND metric_name = p_metric_name AND type_meta_ver = p_from_type_meta_ver ORDER BY metric_column) LOOP BEGIN add_metric_row( p_metric_guid => met_rec.metric_guid, p_target_type => met_rec.target_type, p_metric_name => met_rec.metric_name, p_metric_column => met_rec.metric_column, p_type_meta_ver => p_to_type_meta_ver, p_category_prop_1 => met_rec.category_prop_1, p_category_prop_2 => met_rec.category_prop_2, p_category_prop_3 => met_rec.category_prop_3, p_category_prop_4 => met_rec.category_prop_4, p_category_prop_5 => met_rec.category_prop_5, p_metric_type => met_rec.metric_type, p_usage_type => met_rec.usage_type, p_metric_label => met_rec.metric_label, p_metric_label_nlsid => met_rec.metric_label_nlsid, p_column_label => met_rec.column_label, p_column_label_nlsid => met_rec.column_label_nlsid, p_key_column => met_rec.key_column, p_key_order => met_rec.key_order, p_num_keys => met_rec.num_keys, p_description => met_rec.description, p_description_nlsid => met_rec.description_nlsid, p_unit => met_rec.unit, p_unit_nlsid => met_rec.unit_nlsid, p_short_name => met_rec.short_name, p_short_name_nlsid => met_rec.short_name_nlsid, p_is_for_summary => met_rec.is_for_summary, p_keys_from_mult_colls => met_rec.keys_from_mult_colls, p_statefull => met_rec.statefull, p_is_repository => met_rec.is_repository, p_author => met_rec.author, p_source_type => met_rec.source_type, p_source => met_rec.source, p_is_transposed => met_rec.is_transposed, p_is_test_metric => met_rec.is_test_metric, p_has_push => met_rec.has_push, p_has_pull => met_rec.has_pull, p_remote => met_rec.remote, p_non_thresholded_alerts => met_rec.non_thresholded_alerts, p_keyonly_thresholds => met_rec.keyonly_thresholds, p_is_long_running => met_rec.is_long_running, p_is_renderable => met_rec.is_renderable); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- NOTE : As the condition for start_type_meta_ver is <= and not < -- there could be a chance for a PK violation. So ignore any such errors IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' PK error while adding metric ' || ' target type = ' || met_rec.target_type || ' metric_name = ' || met_rec.metric_name || ' metric_column = ' || met_rec.metric_column || ' from version ' || p_from_type_meta_ver || ' to version ' || p_to_type_meta_ver, G_MODULE_NAME) ; END IF ; END; END LOOP; /** INSERT INTO MGMT_METRICS (metric_guid, target_type, metric_name, metric_column, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, metric_type, usage_type, key_column, key_order, num_keys, column_label, column_label_nlsid, metric_label, metric_label_nlsid, description, description_nlsid, unit, unit_nlsid, short_name, short_name_nlsid, is_for_summary, keys_from_mult_colls, statefull, is_repository, author, source_type, source, eval_func, is_transposed, is_test_metric, has_push, has_pull, remote) SELECT metric_guid, target_type, metric_name, metric_column, p_type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, metric_type, usage_type, key_column, key_order, num_keys, column_label, column_label_nlsid, metric_label, metric_label_nlsid, description, description_nlsid, unit, unit_nlsid, short_name, short_name_nlsid, is_for_summary, keys_from_mult_colls, statefull, is_repository, author, source_type, source, eval_func, is_transposed, is_test_metric, has_push, has_pull, remote FROM mgmt_metrics WHERE target_type = p_target_type AND metric_name = l_metric_names(l_ctr) AND type_meta_ver = l_start_meta_vers(l_ctr) ORDER BY metric_column; **/ END copy_metric; PROCEDURE copy_metric_category_map(p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_start_version IN VARCHAR2, p_to_version IN VARCHAR2) AS l_proc_name VARCHAR2(64) := 'copy_metric_category_map'; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' Enter ' || p_target_type || ', ' || p_metric_name || ', ' || p_start_version || ', ' || p_to_version, G_MODULE_NAME) ; END IF ; FOR rec IN (SELECT p.class_name, p.category_name, p.object_guid FROM mgmt_category_map p, mgmt_metrics m WHERE p.target_type = p_target_type AND p.type_meta_ver = p_start_version AND p.object_type = 1 AND p.object_guid = m.metric_guid AND p.target_type = m.target_type AND p.type_meta_ver = m.type_meta_ver AND m.metric_name = p_metric_name ) LOOP add_object_category( p_object_guid => rec.object_guid, p_object_type => 1, p_target_type => p_target_type, p_type_meta_ver => p_to_version, p_category_class => rec.class_name, p_category_name => rec.category_name); END LOOP; IF emdw_log.p_is_info_set THEN emdw_log.info(l_proc_name || ' Exit.', G_MODULE_NAME); END IF; END copy_metric_category_map; -- Callback to handle the addition of new target type version PROCEDURE handle_tgttype_addition(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2) IS l_metric_names MGMT_SHORT_STRING_TABLE; l_start_meta_vers MGMT_SHORT_STRING_TABLE; l_proc_name CONSTANT VARCHAR2(64) := 'em_metric.handle_tgttype_addition' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||'Enter ' || p_target_type || ', ' || p_type_meta_ver, G_MODULE_NAME) ; END IF ; -- Get the list of metrics that have to be added SELECT metric_name, start_type_meta_ver BULK COLLECT INTO l_metric_names, l_start_meta_vers FROM mgmt_metric_versions WHERE target_type = p_target_type AND (em_target.compare_type_meta_vers(start_type_meta_ver, p_type_meta_ver) <= 0) AND ( (end_type_meta_ver IS NULL) OR (em_target.compare_type_meta_vers(p_type_meta_ver, end_type_meta_ver) <= 0) ); IF ( (l_metric_names IS NOT NULL) AND (l_metric_names.COUNT > 0) ) THEN FOR l_ctr IN l_metric_names.FIRST..l_metric_names.LAST LOOP IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' Adding metric ' || l_metric_names(l_ctr) || ' to version ' || p_type_meta_ver, G_MODULE_NAME) ; END IF ; copy_metric(p_target_type => p_target_type, p_metric_name => l_metric_names(l_ctr), p_from_type_meta_ver => l_start_meta_vers(l_ctr), p_to_type_meta_ver => p_type_meta_ver); copy_metric_category_map(p_target_type => p_target_type, p_metric_name => l_metric_names(l_ctr), p_start_version => l_start_meta_vers(l_ctr), p_to_version => p_type_meta_ver); END LOOP; END IF; END handle_tgttype_addition; PROCEDURE set_repo_timing( p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS BEGIN em_check.check_range(p_value=>p_enabled, p_min_value=>0, p_max_value=>1, p_param_name=>' p_enabled ') ; UPDATE mgmt_metrics SET repo_timing_enabled = p_enabled WHERE target_type = p_target_type AND metric_name = p_metric_name ; IF SQL%NOTFOUND THEN raise_application_error(MGMT_GLOBAL.NO_SUCH_METRIC_ERR, MGMT_GLOBAL.NO_SUCH_METRIC_ERR_M) ; END IF ; END set_repo_timing ; -- Internal procedure to delete data for metric key-value in synchronous mode. -- One should include a API call if data needs to be deleted in synchronous mode. -- All registred call back wwould be executed in asynchronous mode PROCEDURE metric_keyval_deletion_sync ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guids IN SMP_EMD_STRING_ARRAY, p_key_value IN mgmt_metrics_raw.key_value%TYPE ) IS l_metric_guids mgmt_guid_array := mgmt_guid_array(); l_lock_target_guids mgmt_guid_array := mgmt_guid_array(); BEGIN l_metric_guids.extend(p_metric_guids.COUNT); FOR i IN 1..p_metric_guids.count LOOP l_metric_guids(i) := mgmt_guid_obj(hextoraw(p_metric_guids(i)), ' '); END LOOP; SELECT mgmt_guid_obj(target_guid, ' ') BULK COLLECT INTO l_lock_target_guids FROM mgmt_current_metrics WHERE target_guid = p_target_guid AND key_value = p_key_value AND metric_guid IN (SELECT guid FROM TABLE(CAST(l_metric_guids as mgmt_guid_array)) ) ORDER BY metric_guid FOR UPDATE; FOR m IN 1..l_metric_guids.count LOOP -- Delete from mgmt_current_metrics DELETE FROM mgmt_current_metrics WHERE target_guid = p_target_guid AND metric_guid = l_metric_guids(m).guid AND key_value = p_key_value ; -- Since we dont delete violation history, insert a dummy row which gives the user -- the info that the key value has been removed at this point in time. /* BEGIN EM_SEVERITY.insert_violation ( p_target_guid => p_target_guid, p_policy_guid => l_metric_guids(m).guid, p_collection_timestamp => mgmt_target.sysdate_target(p_target_guid), p_key_value => p_key_value, p_violation_level => MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_message => 'This key value has been removed from system. All previous violations are obsolete.' ); EXCEPTION WHEN MGMT_GLOBAL.DUPLICATE_RECORD THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Encountered a duplicate info severity while deleting key_value: '|| p_key_value || '. Skipping insertion.',G_MODULE_NAME) ; END IF; END; */ -- Delete from mgmt_current_violation EM_SEVERITY.del_keyval_curr_violation ( p_target_guid => p_target_guid, p_metric_guid => l_metric_guids(m).guid, p_key_value => p_key_value ) ; END LOOP; END metric_keyval_deletion_sync ; -- -- PROCEDURE: del_keyval_metrics_raw / del_keyval_metrics_1hour / del_keyval_metrics_1day -- -- PURPOSE -- Internal procedure to delete data from mgmt_metrics_raw/mgmt_metrics_1hour/mgmt_metrics_1day -- table based on a key-value of a metric. -- -- IN Parameters: -- p_target_guid - Target for which data need to be deleted -- -- p_metric_guid - Metric for which data need to be deleted -- -- p_key_value - Key value of metric for which data need to be removed -- -- p_from_timestamp - From time of time range for which key value data need to be deleted. Default NULL -- -- p_to_timestamp - To time of time range for which key value data need to be deleted. Default NULL -- -- p_batchsize - Number fo record to be deleted in one go. Default is MGMT_GLOBAL.MAX_COMMIT, -- -- p_interval - Time intervalk for which partiotion is maintained. -- Default is 1/24 day for mgmt_metrics_raw and mgmt_metrics_1hour -- Default is 1 day for mgmt_metrics_1day -- PROCEDURE del_keyval_metrics_raw ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_key_value IN mgmt_metrics_raw.key_value%TYPE, p_from_timestamp IN mgmt_metrics_raw.collection_timestamp%TYPE DEFAULT NULL, p_to_timestamp IN mgmt_metrics_raw.collection_timestamp%TYPE DEFAULT NULL, p_batchsize IN NUMBER DEFAULT MGMT_GLOBAL.MAX_COMMIT, p_interval IN NUMBER DEFAULT 1/24 ) IS l_start_date DATE; l_end_date DATE; l_curr_date DATE; l_to_curr_date DATE; l_counter NUMBER; BEGIN IF p_from_timestamp IS NULL THEN l_start_date := TRUNC(SYSDATE - (EMD_LOADER.get_raw_retention_window + 1)); ELSE l_start_date := p_from_timestamp; END IF; IF p_to_timestamp IS NULL THEN l_end_date := TRUNC(SYSDATE + 1/12, 'HH24'); ELSE l_end_date := p_to_timestamp; END IF; l_curr_date := l_start_date; WHILE (l_curr_date <= l_end_date) LOOP IF l_end_date < l_curr_date + p_interval THEN l_to_curr_date := l_end_date ; ELSE l_to_curr_date := l_curr_date + p_interval; END IF; LOOP DELETE FROM mgmt_metrics_raw WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value AND collection_timestamp >= l_curr_date AND collection_timestamp < l_to_curr_date AND ROWNUM <= MGMT_GLOBAL.MAX_COMMIT; l_counter := SQL%ROWCOUNT; COMMIT; IF l_counter < MGMT_GLOBAL.MAX_COMMIT THEN EXIT; END IF; END LOOP; --endless loop l_curr_date := l_curr_date + p_interval; END LOOP; --While loop -- Delete data from overflow segments. IF p_from_timestamp IS NULL THEN -- This loop will delete data from the lower range LOOP DELETE FROM MGMT_METRICS_RAW WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value AND collection_timestamp < l_start_date AND ROWNUM <= mgmt_global.MAX_COMMIT; l_counter := SQL%ROWCOUNT; COMMIT; IF l_counter < mgmt_global.MAX_COMMIT THEN EXIT; END IF; END LOOP; END IF; IF p_to_timestamp IS NULL THEN -- This loop will delete data from the upper range LOOP DELETE FROM MGMT_METRICS_RAW WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value AND collection_timestamp > l_end_date AND ROWNUM <= mgmt_global.MAX_COMMIT; l_counter := SQL%ROWCOUNT; COMMIT; IF l_counter < mgmt_global.MAX_COMMIT THEN EXIT; END IF; END LOOP; END IF; END del_keyval_metrics_raw; -- Internal procedure to remove data from mgmt_metric_1hour PROCEDURE del_keyval_metrics_1hour ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_key_value IN mgmt_metrics_raw.key_value%TYPE, p_from_timestamp IN mgmt_metrics_raw.collection_timestamp%TYPE DEFAULT NULL, p_to_timestamp IN mgmt_metrics_raw.collection_timestamp%TYPE DEFAULT NULL, p_batchsize IN NUMBER DEFAULT MGMT_GLOBAL.MAX_COMMIT, p_interval IN NUMBER DEFAULT 1/24 ) IS l_start_date DATE; l_end_date DATE; l_curr_date DATE; l_to_curr_date DATE; l_counter NUMBER; BEGIN IF p_from_timestamp IS NULL THEN l_start_date := TRUNC(SYSDATE - (EMD_LOADER.get_1hour_retention_window + 1)); ELSE l_start_date := p_from_timestamp; END IF; IF p_to_timestamp IS NULL THEN l_end_date := TRUNC(SYSDATE + 2); ELSE l_end_date := p_to_timestamp; END IF; l_curr_date := l_start_date; WHILE (l_curr_date <= l_end_date) LOOP IF l_end_date < l_curr_date + p_interval THEN l_to_curr_date := l_end_date ; ELSE l_to_curr_date := l_curr_date + p_interval; END IF; LOOP DELETE FROM mgmt_metrics_1hour WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value AND rollup_timestamp >= l_curr_date AND rollup_timestamp < l_to_curr_date AND ROWNUM <= mgmt_global.MAX_COMMIT; l_counter := SQL%ROWCOUNT; COMMIT; IF l_counter < mgmt_global.MAX_COMMIT THEN EXIT; END IF; END LOOP; --endless loop l_curr_date := l_curr_date + p_interval; END LOOP; --While loop END del_keyval_metrics_1hour; -- Internal procedure to remove data from mgmt_metric_1day PROCEDURE del_keyval_metrics_1day ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_key_value IN mgmt_metrics_raw.key_value%TYPE, p_from_timestamp IN mgmt_metrics_raw.collection_timestamp%TYPE DEFAULT NULL, p_to_timestamp IN mgmt_metrics_raw.collection_timestamp%TYPE DEFAULT NULL, p_batchsize IN NUMBER DEFAULT MGMT_GLOBAL.MAX_COMMIT, p_interval IN NUMBER DEFAULT 1 ) IS l_start_date DATE; l_end_date DATE; l_curr_date DATE; l_to_curr_date DATE; l_counter NUMBER; BEGIN IF p_from_timestamp IS NULL THEN l_start_date := TRUNC(SYSDATE - (EMD_LOADER.get_1day_retention_window + 1)); ELSE l_start_date := p_from_timestamp; END IF; IF p_to_timestamp IS NULL THEN l_end_date := TRUNC(SYSDATE + 2); ELSE l_end_date := p_to_timestamp; END IF; l_curr_date := l_start_date; WHILE (l_curr_date <= l_end_date) LOOP IF l_end_date < l_curr_date + p_interval THEN l_to_curr_date := l_end_date ; ELSE l_to_curr_date := l_curr_date + p_interval; END IF; LOOP DELETE FROM mgmt_metrics_1day WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value AND rollup_timestamp >= l_curr_date AND rollup_timestamp < l_to_curr_date AND ROWNUM <= mgmt_global.MAX_COMMIT; l_counter := SQL%ROWCOUNT; COMMIT; IF l_counter < mgmt_global.MAX_COMMIT THEN EXIT; END IF; END LOOP; --endless loop l_curr_date := l_curr_date + p_interval; END LOOP; --While loop END del_keyval_metrics_1day; -- Ascynchronous callback for metric key-val deletion -- This callback is only for framework related table data deletion in asynchronus mode. -- If table is not a framework related then please register a separate callback. PROCEDURE handle_metric_keyval_deletion ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_key_value IN mgmt_metrics_raw.key_value%TYPE ) IS l_counter NUMBER; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('handle_metric_keyval_deletion:Enter',G_MODULE_NAME) ; END IF; del_keyval_metrics_raw ( p_target_guid => p_target_guid, p_metric_guid => p_metric_guid, p_key_value => p_key_value ); del_keyval_metrics_1hour ( p_target_guid => p_target_guid, p_metric_guid => p_metric_guid, p_key_value => p_key_value ); del_keyval_metrics_1day ( p_target_guid => p_target_guid, p_metric_guid => p_metric_guid, p_key_value => p_key_value ); -- Delete from MGMT_METRIC_THRESHOLDS/policy tables EM_POLICY.del_keyval_policy ( p_target_guid => p_target_guid, p_metric_guid => p_metric_guid, p_key_value => p_key_value ); -- Delete from MGMT_STRING_METRIC_HISTORY LOOP DELETE FROM mgmt_string_metric_history WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND key_value = p_key_value AND ROWNUM <= mgmt_global.MAX_COMMIT; l_counter := SQL%ROWCOUNT; COMMIT; IF l_counter < mgmt_global.MAX_COMMIT THEN EXIT; END IF; END LOOP; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('handle_metric_keyval_deletion:Exit',G_MODULE_NAME) ; END IF; END handle_metric_keyval_deletion ; PROCEDURE exec_cbk_metric_keyval ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guids IN SMP_EMD_STRING_ARRAY, p_key_value IN mgmt_metrics_raw.key_value%TYPE ) IS l_task_param MGMT_NAMEVALUE_ARRAY := MGMT_NAMEVALUE_ARRAY() ; l_task_id NUMBER ; BEGIN -- NOT NULL checks EM_CHECK.check_not_null(p_target_guid, 'p_target_guid'); EM_CHECK.check_not_null(p_key_value, 'p_key_value'); IF p_metric_guids IS NULL OR p_metric_guids.count = 0 THEN IF (EMDW_LOG.p_is_error_set)THEN EMDW_LOG.error('Invalid parameter', G_MODULE_NAME); END IF; raise_application_error(MGMT_GLOBAL.invalid_params_err, 'Invalid parameter'); END IF; -- Synchronous deletion metric_keyval_deletion_sync ( p_target_guid => p_target_guid, p_metric_guids => p_metric_guids, p_key_value => p_key_value ); -- Asynchronous deletion FOR m IN 1..p_metric_guids.count LOOP l_task_id := NULL; l_task_param := MGMT_NAMEVALUE_ARRAY( mgmt_namevalue_obj.new('p_target_guid', p_target_guid), mgmt_namevalue_obj.new('p_metric_guid', p_metric_guids(m)), mgmt_namevalue_obj.new('p_key_value', p_key_value) ) ; l_task_id := EM_TASK.create_task ( p_coll_schedule => MGMT_COLL_SCHEDULE_OBJ.one_time(SYSDATE), p_task_type => EM_TASK.G_TASK_TYPE_ADHOC, p_task_proc => 'EM_METRIC.TASK_EXEC_CBK_METRIC_KEYVAL', p_context => l_task_param, p_task_class => EM_TASK.G_TASK_CLASS_LONG_RUNNING ) ; END LOOP; END exec_cbk_metric_keyval ; PROCEDURE task_exec_cbk_metric_keyval ( p_context IN MGMT_NAMEVALUE_ARRAY ) IS l_current_user mgmt_targets.owner%TYPE := MGMT_USER.get_current_em_user() ; l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_key_value mgmt_metrics_raw.key_value%TYPE; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Task_exec_cbk_metric_keyval:Enter',G_MODULE_NAME) ; END IF; IF p_context IS NULL or p_context.count != 3 THEN IF (EMDW_LOG.p_is_error_set)THEN EMDW_LOG.error('Invalid parameter', G_MODULE_NAME); END IF; raise_application_error(MGMT_GLOBAL.invalid_params_err, 'Invalid parameter'); END IF; FOR p IN 1..p_context.count LOOP IF UPPER(p_context(p).name) = 'P_TARGET_GUID' THEN l_target_guid := p_context(p).value ; ELSIF UPPER(p_context(p).name) = 'P_METRIC_GUID' THEN l_metric_guid := p_context(p).value ; ELSIF UPPER(p_context(p).name) = 'P_KEY_VALUE' THEN l_key_value := p_context(p).value ; ELSE IF (EMDW_LOG.p_is_error_set)THEN EMDW_LOG.error('Invalid parameter', G_MODULE_NAME); END IF; raise_application_error(MGMT_GLOBAL.invalid_params_err, 'Invalid parameter'); END IF; END LOOP; -- Execute callback FOR callback IN (SELECT callback_name FROM mgmt_callbacks WHERE callback_type = MGMT_GLOBAL.G_METRIC_KEYVAL_DEL_CALLBACK ORDER BY eval_order ) LOOP BEGIN -- Enter super-user mode. This is necessary because some callbacks -- involves making calls to the security system that only super-users -- are allowed to make SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, MGMT_USER.OP_SET_IDENTIFIER); EXECUTE IMMEDIATE 'BEGIN ' || EM_CHECK.qualified_sql_name(callback.callback_name) || '(:1, :2, :3); END; ' USING l_target_guid, l_metric_guid, l_key_value ; EXCEPTION -- Ignore exceptions so that main transaction is not affected; we do not -- treat callback exceptions on par with repository exceptions. In other -- words, we will not let some callback mess up repository operations. WHEN OTHERS THEN -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); IF (EMDW_LOG.p_is_error_set)THEN EMDW_LOG.error('Error executing callback : '||sqlerrm , G_MODULE_NAME); END IF; END; END LOOP; --of callback -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Task_exec_cbk_metric_keyval:Exit',G_MODULE_NAME) ; END IF; END task_exec_cbk_metric_keyval; -- Any callback added would be deleted in Asynchronus mode PROCEDURE add_metric_keyval_callback ( p_callback_name IN VARCHAR2, p_callback_eval_order IN NUMBER DEFAULT 0 ) IS l_arg_type_list MGMT_SHORT_STRING_ARRAY; l_err_msg VARCHAR2(4096); BEGIN -- NOT NULL checks EM_CHECK.check_not_null(p_callback_name, 'p_callback_name'); l_arg_type_list := MGMT_SHORT_STRING_ARRAY('RAW','RAW','VARCHAR2'); IF (EM_CHECK.is_valid_signature(p_callback_name, l_arg_type_list, l_err_msg) = FALSE) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid callback ' || p_callback_name || ' . Error = ' || l_err_msg); END IF; BEGIN INSERT INTO mgmt_callbacks (callback_type, callback_name, selector_1, selector_2, selector_3, eval_order) VALUES (MGMT_GLOBAL.G_METRIC_KEYVAL_DEL_CALLBACK, UPPER(p_callback_name), ' ', ' ', ' ', p_callback_eval_order); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN IF (emdw_log.p_is_error_set)THEN emdw_log.error('Callback '|| p_callback_name||' already exists', G_MODULE_NAME); END IF; raise_application_error(mgmt_global.invalid_params_err, 'callback already exist'); END; END add_metric_keyval_callback; PROCEDURE del_metric_keyval_callback ( p_callback_name IN VARCHAR2 ) IS BEGIN -- NOT NULL checks EM_CHECK.check_not_null(p_callback_name, 'p_callback_name'); DELETE FROM mgmt_callbacks WHERE callback_type = MGMT_GLOBAL.G_METRIC_KEYVAL_DEL_CALLBACK AND callback_name = UPPER(p_callback_name) ; END del_metric_keyval_callback ; PROCEDURE add_metric_metadata_reg_cb(p_callback_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT ' ') AS l_arg_type_list mgmt_short_string_array; l_err_msg VARCHAR2(4096); BEGIN EM_CHECK.check_not_null(p_callback_name, 'p_callback_name'); l_arg_type_list := MGMT_SHORT_STRING_ARRAY('VARCHAR2', 'VARCHAR2'); IF (EM_CHECK.is_valid_signature(p_callback_name, l_arg_type_list, l_err_msg) = FALSE) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid callback ' || p_callback_name || ' . Error = ' || l_err_msg); END IF; BEGIN INSERT INTO mgmt_callbacks (callback_type, callback_name, selector_1, selector_2, selector_3) VALUES (MGMT_GLOBAL.G_METRIC_METADATA_REG_CALLBACK, UPPER(p_callback_name), NVL(p_target_type, ' '), ' ', ' '); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Ignore duplicate callback registrations.. NULL; END; END add_metric_metadata_reg_cb; PROCEDURE del_metric_metadata_reg_cb(p_callback_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT ' ') AS l_target_type mgmt_targets.target_type%TYPE; BEGIN EM_CHECK.check_not_null(p_callback_name, 'p_callback_name'); l_target_type := NVL(p_target_type, ' '); DELETE FROM mgmt_callbacks WHERE callback_type = MGMT_GLOBAL.G_METRIC_METADATA_REG_CALLBACK AND callback_name = UPPER(p_callback_name) AND selector_1 = l_target_type; END del_metric_metadata_reg_cb; PROCEDURE exec_metric_metadata_reg_cb(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2) AS BEGIN FOR cbs IN (SELECT callback_name FROM mgmt_callbacks WHERE callback_type = mgmt_global.G_METRIC_METADATA_REG_CALLBACK AND (selector_1 = p_target_type OR selector_1 = ' ') ORDER BY callback_name ) LOOP BEGIN EXECUTE IMMEDIATE 'BEGIN ' || EM_CHECK.qualified_sql_name(cbs.callback_name) || '(:1, :2); END;' USING EM_CHECK.NOOP(p_target_type), p_type_meta_ver; EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(v_module_name_in => 'LOADER', v_error_code_in => 0, v_error_msg_in => 'In exec_metric_metadata_reg_cb ' || ' Error while calling callback ' || cbs.callback_name || ' with target type ' || p_target_type || ' type meta ver ' || p_type_meta_ver || 'Error msg is ' || SUBSTR(SQLERRM, 1, 2000)); END; END LOOP; END exec_metric_metadata_reg_cb; FUNCTION is_server_gen_alert_metric ( p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_target_guid IN mgmt_targets.target_guid%TYPE ) RETURN BOOLEAN IS server_gen_alert_metric BOOLEAN := false; l_has_pull NUMBER; BEGIN BEGIN SELECT distinct(m.has_push) INTO l_has_pull FROM mgmt_targets t, mgmt_metrics m WHERE t.target_guid = p_target_guid AND m.metric_guid = p_metric_guid 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 = ' '); IF(l_has_pull = 1) THEN server_gen_alert_metric := true; ELSE server_gen_alert_metric := false; END IF; EXCEPTION WHEN OTHERS THEN server_gen_alert_metric := false; END; RETURN server_gen_alert_metric; END is_server_gen_alert_metric; END em_metric; / show errors
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de