Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/basic/basic_mntr_pkgdef.sql /st_emcore_10.2.0.4.2db11.2/1 2009/06/17 22:26:41 paachary Exp $ Rem Rem basic_mntr_pkgdef.sql Rem Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem basic_mntr_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem paachary 05/21/08 - Fixing bug 6503132 Rem paachary 06/10/09 - Backport paachary_bug-6503132 from Rem st_emcore_10.2.0.1.0 Rem groyal 07/18/07 - Bug 4863138: Update get_*_lib_mntr_settings to Rem retrieve info about repository metric collections Rem vmotamar 05/23/07 - Added new functions : "check_metric_parent", "check_for_warn_crit_tip" Rem acgopala 04/19/07 - Bug-5519492 Add support for server generated metric tip in UI Rem For 10g DB and above Rem pratagar 01/10/06 - Delete UDM Support for delete_udm Rem pratagar 07/25/06 - Backport pratagar_bug-4653111 from main Rem jgwang 12/01/05 - Backport jgwang_bug-4620368 from main Rem jgwang 09/22/05 - disable collection for UDM if needed Rem vkgarg 08/30/05 - add get_target_timestamp_info Rem dtsao 08/24/05 - Add get_console_homepage_info_simple procedure Rem jgwang 08/22/05 - support modify_threshold verb Rem ancheng 08/19/05 - pending update execution Rem njuillar 08/08/05 - Add get_template_apply_operations Rem ancheng 07/20/05 - modify get_pending_changes Rem jgwang 07/11/05 - Rem ancheng 07/12/05 - bug 4261421 Rem njuillar 07/06/05 - Add get_has_template_priv Rem jaysmith 06/14/05 - load violation context def to monitoring Rem settings Rem jgwang 06/13/05 - add create_udm Rem bram 06/13/05 - Fix-4287713-add get_metric_errors Rem rpatti 05/18/05 - move all metrics query out Rem ancheng 05/05/05 - add collection schedule to All Metrics page Rem njuillar 05/02/05 - add get_pending_changes Rem jvishen 04/13/05 - update get_metric_associated_ca for fixit job Rem rzazueta 04/12/05 - Add get_template_copy_tgt_settings Rem ancheng 04/07/05 - get agent version Rem njuillar 04/06/05 - Fix bug 4203215 Rem jgwang 04/04/05 - add delete_udm procedure Rem ancheng 04/01/05 - remove old APIs Rem jvishen 03/30/05 - Add get_unconfig_targets Rem jvishen 03/08/05 - get the broken corrective action count Rem njuillar 02/28/05 - Fix bug 4087311 Rem ancheng 02/14/05 - bug 4176489 Rem ancheng 02/08/05 - modify get_target_settings Rem vkgarg 01/24/05 - pass date as varchar in get_metric_details and Rem get_metric_details_ts Rem vkgarg 01/20/05 - add proc to get target info Rem vkgarg 01/17/05 - update get_key_value_avg_details to get last Rem collected timestamp Rem vkgarg 01/11/05 - add omstimezone to get_metric_details and get_metric_details_ts Rem vkgarg 01/11/05 - add timezone to custom dates Rem vkgarg 01/06/05 - get target load and last load timestamp Rem ancheng 01/17/05 - add more APIs Rem jabramso 01/07/05 - Add ts_mk Rem mfidanbo 12/09/04 - get test bcn summary for a targettype Rem jvishen 12/03/04 - update get_metric_details for CAs Rem njuillar 12/03/04 - Add get_has_template_privilege Rem njuillar 11/23/04 - Add get_template_pending_changes Rem groyal 11/22/04 - Add get_target_policy_settings, get_library_policy_settings Rem jaysmith 11/13/04 - get CA execution id Rem jpyang 11/05/04 - add member type cursor Rem vkgarg 11/03/04 - remove mode param from get_metric_details and Rem get_metric_details_ts Rem ancheng 11/02/04 - add get_metric_collection_info Rem ancheng 10/25/04 - add get_target_collection_info Rem jaysmith 10/22/04 - multitask CAs Rem groyal 10/15/04 - Add policy support to get_console_homepage_info Rem asawant 09/20/04 - Modify get_targets_summary() signature. Rem rpinnama 10/10/04 - Replace template_ with mntr_ Rem rzazueta 09/26/04 - Add get_template_copy_settings, Rem get_mntr_settings_copy Rem jaysmith 09/26/04 - initialize corrective actions Rem njuillar 09/30/04 - Add get_template_settings Rem groyal 09/28/04 - Add get_policy_settings Rem ancheng 09/24/04 - add save_target_settings Rem groyal 09/17/04 - Enhance get_*_mntr_settings to get additional Rem policy attribute Rem asaraswa 09/16/04 - removing unused procedure get_ecm_homepage_info Rem asawant 07/13/04 - Adding support for extended search Rem ancheng 09/01/04 - change API Rem ancheng 08/17/04 - add get_monitoring_settings Rem pmaddi 07/28/04 - Moving RCA functions to a different package. Rem pmaddi 07/21/04 - AddAdding 2 more parameters to show rac Rem dependent services for the service/target. Rem pmaddi 07/07/04 - adding code for getting rca alerts. Rem vkgarg 07/23/04 - updating get_metric_details and Rem get_metric_details_ts for custom raw data Rem vkgarg 07/23/04 - adding start and end time parameters Rem jpyang 03/31/04 - add mgmt pack license support Rem jpyang 12/04/03 - Rem jpyang 11/23/03 - add license table sql Rem joguo 11/14/03 - cleanup Rem joguo 11/03/03 - create new group pages Rem hasriniv 10/16/03 - Moving proc get_lsnr_homepage_info to mgmt_dbnet Rem rpatti 09/25/03 - cleanup Rem jpyang 08/29/03 - add multiple targets deletion Rem jpyang 08/27/03 - add resource bundle map Rem hasriniv 08/19/03 - Fix for bug 3021974 Rem jpyang 07/30/03 - target selection query Rem ancheng 07/31/03 - severity/annotation change Rem ancheng 06/16/03 - manual clear of alerts Rem ancheng 06/03/03 - change get_key_value_avg_details Rem ancheng 05/19/03 - manual clear of severity Rem aarasu 02/14/03 - added new function for ecm home page Rem xuliu 01/22/03 - review chg Rem xuliu 01/20/03 - rac status details Rem rpatti 04/14/03 - add proc to get metric detai timeseries Rem agor 11/14/02 - get some more counts for console home page Rem ancheng 09/09/02 - console homepage group avail change Rem rpatti 09/14/02 - move folders pl/sql Rem yaofeng 08/28/02 - remove oracle_context and associated files Rem ancheng 07/16/02 - add procedure for console home page Rem ancheng 06/24/02 - modify get_current_metric_details Rem yaofeng 06/26/02 - dos2unix Rem yaofeng 06/24/02 - remove passing label and images out Rem ancheng 06/18/02 - support composite key Rem rpatti 06/21/02 - support composite keys Rem ancheng 06/13/02 - add severity table for string metrics Rem ancheng 05/22/02 - add proc for all metrics Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Restructured. Rem ancheng 05/06/02 - fix lsnr host name Rem rpatti 04/23/02 - remove big ip support Rem rpatti 04/23/02 - add get_metric_threshold_info Procedure Rem ancheng 04/05/02 - dummy cursor for get_databases_info Rem tjaiswal 04/01/02 - Misc group fixes Rem ancheng 03/01/02 - add procedure Rem rpatti 03/06/02 - support mcAvg, mcCurrent pages Rem yaofeng 01/25/02 - add get_ebs_sitemap_info Rem rzazueta 01/18/02 - Update get_forms6_sitemap_info Rem edemembe 01/08/02 - Metrics are now target independent Rem edemembe 12/26/01 - Removing TARGET_NAME,TARGET_TYPE,etc Rem ancheng 12/18/01 - changes for listener Rem njuillar 12/13/01 - Split file Rem njuillar 12/11/01 - Get needed properties for metric details Rem rmarripa 12/07/01 - To fix the order in watch list table.. Rem rmarripa 12/06/01 - To fix the query which retrieves the filter. bug#2135073. Rem rmarripa 12/06/01 - Fix query to get metric guid in getMostFavTimeSeries.. Rem yaofeng 12/11/01 - add get_reports6_sitemap_info for oracle_reports6 Rem rmarripa 11/29/01 - emd_user_mntr.getMostFavResponseTimeSeries and Rem getMetricsResponseTimeseries. Changed where clause Rem from !='Status' to "=Timing".. Rem rpatti 12/05/01 - move host sql out Rem rpatti 11/28/01 - do not show number of alerts Rem starng 11/23/01 - Rem lgloyd 11/19/01 - fix cursor leak Rem lgloyd 11/16/01 - fix min hits prob, handle nulls Rem lgloyd 11/14/01 - more chronos procedures Rem lgloyd 11/13/01 - add procedures Rem tjaiswal 11/09/01 - Add get_comp_tgt_children_data Rem lgloyd 11/12/01 - chronos performance work Rem yaofeng 11/06/01 - changed metric name Rem njuillar 10/25/01 - Get occurrences in get_metric_details Rem tjaiswal 10/31/01 - Remove usage of em$target_ui_status Rem njuillar 10/22/01 - Added back Memory Scan Rate to Host homepage Rem smishra 10/19/01 - Getstatuscode fixed Rem smishra 10/18/01 - Blackout status added Rem rzazueta 10/17/01 - add get_forms6_sitemap_info for oracle_forms6 Rem yaofeng 10/10/01 - use hourly completed requests Rem njuillar 10/08/01 - Get key name for get_metric_details() Rem yaofeng 10/05/01 - add get_cmanager_sitemap_info for oracle_cmanager Rem rmarripa 09/28/01 - To modify chronosDataExists to use targetGuid. Rem lgloyd 09/26/01 - remove target_name from exchange and website pack Rem rmarripa 09/27/01 - modify chronosDataExists. Rem rmarripa 09/26/01 - To change chronosDataExists function to look Rem for property in properties table.. Rem lgloyd 09/25/01 - use guids for chronsDataExists Rem rpatti 09/24/01 - remove references to perf_code Rem njuillar 09/19/01 - Separate query for metric details thresholds Rem rpatti 09/14/01 - use the global timestamp format Rem rpatti 09/10/01 - use consistent timestamp format Rem vnukal 09/14/01 - update hostsitemap query Rem njuillar 08/31/01 - Use 1-day rollups for 31-day metric details Rem rpinnama 08/24/01 - Use timestamp instead of date Rem ancheng 08/30/01 - reset initial values Rem njuillar 08/21/01 - Change latest value handling in get_metric_details Rem njuillar 08/20/01 - Added get_metric_details() in emd_mntr package Rem lgloyd 08/20/01 - add distinct keyword to subselect where multi val Rem lgloyd 08/09/01 - fix chronos exists check Rem ancheng 07/31/01 - use mgmt_ table name Rem rpatti 07/20/01 - update for repository changes Rem skini 07/21/01 - Merge in rpatti transaction Rem lgloyd 07/20/01 - latency check Rem lgloyd 07/18/01 - new exchange metrics Rem rpatti 07/10/01 - update fro schema revamp Rem lgloyd 07/09/01 - move utility function to EmdSchema Rem lgloyd 07/05/01 - add chronos package Rem lgloyd 07/02/01 - new metric format Rem lgloyd 06/28/01 - exchange sitemap work Rem ancheng 06/27/01 - modify getHostSiteMapInfo Rem ancheng 06/21/01 - modify getHostSiteMapInfo Rem ancheng 06/15/01 - modify getHostSiteMapInfo Rem ancheng 06/12/01 - modify getExchangeSiteMapInfo Rem rpatti 06/07/01 - check NULL values Rem ancheng 05/24/01 - check availability Rem ancheng 05/14/01 - fix getMonitoredDatabaseInfo Rem ancheng 05/08/01 - modify getExchangeSiteMapInfo Rem ancheng 05/04/01 - remove functions Rem rpatti 05/03/01 - rearrange display for host Rem ancheng 05/02/01 - add getSystemComponents Rem ancheng 05/01/01 - capitalize trace level, etc Rem ancheng 04/30/01 - add getBigIPSiteMapInfo, getExchangeSiteMapInfo Rem rpatti 04/24/01 - cleanup sql Rem ancheng 04/18/01 - add getMonitoredDatabases Rem rpatti 04/19/01 - get host config rel values Rem rpatti 04/12/01 - updates to the host overview page Rem ancheng 04/13/01 - add procedures/functions for listener Rem tjaiswal 04/11/01 - Modify getHostTargetsInfo procedure Rem tjaiswal 04/06/01 - Changes to getHostSiteMapInfo Rem tjaiswal 04/05/01 - Created Rem CREATE OR REPLACE PACKAGE emd_mntr AS -- Package level Type Definition TYPE cursorType IS REF CURSOR; TYPE TARGETS_CURSOR IS REF CURSOR; --Module Name to be used for log4plSql MODULE_NAME CONSTANT VARCHAR2(15) := 'EM.MNTR.UI'; POLICY_CONS CONSTANT VARCHAR2(6) := 'POLICY'; CATEGORY_CONS CONSTANT VARCHAR2(8) := 'CATEGORY'; LANGUAGE_CONS CONSTANT VARCHAR2(2) := 'en'; COUNTRY_CONS CONSTANT VARCHAR2(1) := ' '; DEPLOYMENT_PROP CONSTANT VARCHAR2(25) := 'orcl_gtp_deployment_type'; LOCATION_PROP CONSTANT VARCHAR2(18) := 'orcl_gtp_location'; CONTACT_PROP CONSTANT VARCHAR2(17) := 'orcl_gtp_contact'; LOB_PROP CONSTANT VARCHAR2(21) := 'orcl_gtp_line_of_bus'; COMMENT_PROP CONSTANT VARCHAR2(17) := 'orcl_gtp_comment'; INSTANCE_PPROP CONSTANT VARCHAR2(8) := 'INSTANCE'; -- pluy-in database types MICROSOFT_DATABASE_TYPE CONSTANT VARCHAR2(28) := 'microsoft_sqlserver_database'; IBM_DATABASE_TYPE CONSTANT VARCHAR2(16) := 'ibm_db2_database'; SYBASE_DATABASE_TYPE CONSTANT VARCHAR2(10) := 'sybase_ase'; TIMESTEN_DATABASE_TYPE CONSTANT VARCHAR2(13) := 'timesten_imdb'; /* Get the status code for a target. For a given target name and target type, this function returns the status code. Return of "1" indicates the status of the host is up, "0" indicates the status of the host is down and any other number indicates the status of this host is unknown */ function getStatusCode( targetName IN VARCHAR2, targetType IN VARCHAR2) return INTEGER; /* Get the metric value. For a given target name, target type, metric name and metric column, this function returns the value of the metric */ function getMetricValue( targetName IN VARCHAR2, targetType IN VARCHAR2, metricName IN VARCHAR2, metricColumn IN VARCHAR2, doRound IN NUMBER DEFAULT 0) return NUMBER; /* Get all the summary data for the given target of type oracle_database OUT Parameters: summary_out: cursorType A cursor which returns rows containing: target_name: VARCHAR2 The name of the target target_type: VARCHAR2 The type of the target display_name: VARCHAR2 The display name of the target type_display_name: VARCHAR2 The display name of the target type avail_pct: NUMBER The availability percent over the timeperiod current_status: NUMBER The current availability status - check mgmt current availability table for definition of values. warning_count: NUMBER The number of currently open warning alerts critical_count: NUMBER The number of currently open critical alerts */ procedure get_lsnr_member_summary( target_name_in IN VARCHAR2, summary_out OUT cursorType ); -- -- PROCEDURE: get_comp_tgt_children_data -- -- PURPOSE: -- Returns a summary of monitoring details for the members of -- a particular target. -- -- IN Parameters: -- target_name_in: VARCHAR2 The name of the target -- target_type_in: VARCHAR2 The type of the target -- member_target_type_in: VARCHAR2 The type of the member target -- timeperiod_in: NUMBER The timeperiod or which we want details -- DEFAULTS to MGMT_GLOBAL.G_DAY -- -- OUT Parameters: -- traffic_summary_out: CURSORTYPE A cursor which single row containing the -- following. Note that this data is for the composite -- target itself -- avail_pct: NUMBER The availability percent over the timeperiod -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- summary_out: CURSORTYPE A cursor which returns rows containing: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- display_name: VARCHAR2 The display name of the target -- type_display_name: VARCHAR2 The display name of the target type -- avail_pct: NUMBER The availability percent over the timeperiod -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- -- ERROR CODES: -- TARGET_DOES_NOT_EXIST_ERR: No such target was found. This could also mean -- that the user does not have access to view this target. -- PROCEDURE get_comp_tgt_children_data (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, timeperiod_in IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY, traffic_summary_out OUT cursorType, summary_out OUT cursorType); -- -- PROCEDURE: get_current_metric_details -- -- PURPOSE: -- This procedure returns all current metric information displayed in the current -- metric detail page -- -- IN Parameters: -- target_name_in: target name -- target_type_in: target type -- metric_name_in: metric name -- metric_column_in: metric column -- key_value_in: key value -- -- OUT Parameters: -- has_priv_out: if the user has at least OPERATOR_TARGET privilege -- metric_warning_threshold_out: warning threshold -- metric_critical_threshold_out: critical threshold -- num_occurences_out: number of severity occurs -- metric_info_out: cursor containing labels, key column, key order, and -- metric type information -- number_metric_info_out: cursor containing number metric values -- for the past 1.5 hours -- string_metric_info_out: cursor containing string metric values -- for the past 1.5 hours -- metric_sev_out: cursor containing severity information -- current_metric_sev_out: cursor containing current severity information -- ca_severity_out: on which severity Corrective Action is Associated -- target_info_out: cursor containing target load and last load timestamp along with timezone -- PROCEDURE get_current_metric_details(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, metric_warning_threshold_out OUT VARCHAR2, metric_critical_threshold_out OUT VARCHAR2, num_occurences_out OUT VARCHAR2, has_priv_out OUT NUMBER, metric_info_out OUT cursorType, composite_key_info_out OUT cursorType, number_metric_info_out OUT cursorType, string_metric_info_out OUT cursorType, metric_sev_out OUT cursorType, current_metric_sev_out OUT cursorType, ca_severity_out OUT NUMBER, target_info_out OUT cursorType ); /* * Get the metric details info. This procedure returns all the relevant * information for a given target name, target type, metric name, metric column * and key value. The values returned as OUT parameters are used for display in * the Metric Details pages. */ PROCEDURE get_metric_details(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, page_type_in IN VARCHAR2, metric_latest_value_out OUT VARCHAR2, collection_timestamp_out OUT DATE, metric_warning_threshold_out OUT VARCHAR2, metric_critical_threshold_out OUT VARCHAR2, num_occurences_out OUT VARCHAR2, has_priv_out OUT NUMBER, metric_info_out OUT cursorType, composite_key_info_out OUT cursorType, number_metric_info_out OUT cursorType, string_metric_info_out OUT cursorType, metric_sev_out OUT cursorType, current_metric_sev_out OUT cursorType, start_Date_in IN VARCHAR2 DEFAULT NULL, end_Date_in IN VARCHAR2 DEFAULT NULL, ca_severity_out OUT NUMBER, target_info_out OUT cursorType); -- PROCEDURE: get_target_info -- -- PURPOSE : -- Get the metadata associated with the target -- IN Parameters: -- target_name_in : target name of the target -- target_type_in : target type of the target -- -- OUT Parameters: -- target_info_out : Get target load timestamp, lastload timestamp and timezone PROCEDURE get_target_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_info_out OUT cursorType ); -- PROCEDURE: get_target_timestamp_info -- -- PURPOSE : -- Get the load timestamps associated with the target -- IN Parameters: -- target_name_in : target name of the target -- target_type_in : target type of the target -- metric_name_in : metric name -- metric_column_in : metric column -- key_value_in :key value -- -- OUT Parameters: -- load_timestamp_out target load timestamp -- last_load_time_out target lastload timestamp -- timezone_region_out target timezone -- collection_timestamp_out target last metric data upload time by agent too repository PROCEDURE get_target_timestamp_info(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, load_timestamp_out OUT DATE, last_load_time_out OUT DATE, timezone_region_out OUT VARCHAR2, collection_timestamp_out OUT DATE); -- PROCEDURE: get_metric_associated_ca -- -- PURPOSE : -- Get the Corrective Action associated with the metric -- IN Parameters: -- target_name_in : target name of the target -- target_type_in : target type of the target -- metric_name_in : name of the metric -- metric_column_in : name of the metric column -- -- OUT Parameters: -- ca_severity_out : corrective action associted on which severity -- for eg: Warning PROCEDURE get_metric_associated_ca( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, ca_severity_out OUT NUMBER ); -- PROCEDURE: get_addtnl_folder_details -- -- PURPOSE : -- Get the unconfigurred targets and broken CA counts -- IN Parameters: -- target_type_in : target type of the target -- folder_tgt_type_list_in : target types in a folder -- -- OUT Parameters: -- unconfig_targets_cur_out : cursor with unconfigurred targets count -- broken_ca_cur_out : cursor with Broken Corrective Action count PROCEDURE get_addtnl_folder_details( target_type_in IN VARCHAR2, folder_tgt_type_list_in IN SMP_EMD_STRING_ARRAY DEFAULT NULL, unconfig_targets_cur_out OUT cursorType, broken_ca_cur_out OUT cursorType ); -- PURPOSE: -- Get the charts for the metric detail page. -- IN Parameters: -- target_name_in : target name of the target -- target_type_in : target type of the target -- metric_name_in : name of the metric -- metric_column_in : name of the metric column -- key_value_in : specific key value we are looking at -- page_type_in : time period of the chart -- cmp_targets_in : list of targets to compare against -- cmp_key_values_in : list of key values to compare against -- start_Date_in :start date time for custom view -- end_Date_in :end date time for custom view -- -- -- OUT Parameters: -- Cursor with the chart data PROCEDURE get_metric_details_ts_mk( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, page_type_in IN VARCHAR2, cmp_targets_in IN SMP_EMD_STRING_ARRAY, cmp_key_values_in IN SMP_EMD_STRING_ARRAY, timeseries_out OUT cursorType, start_Date_in IN VARCHAR2 DEFAULT NULL, end_Date_in IN VARCHAR2 DEFAULT NULL, multi_key_value_in IN NUMBER DEFAULT 0); PROCEDURE get_metric_details_ts( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, page_type_in IN VARCHAR2, cmp_targets_in IN SMP_EMD_STRING_ARRAY, cmp_key_values_in IN SMP_EMD_STRING_ARRAY, timeseries_out OUT cursorType, start_Date_in IN VARCHAR2 DEFAULT NULL, end_Date_in IN VARCHAR2 DEFAULT NULL); -- -- Name: get_alert_details -- -- Package: emd_mntr -- -- PURPOSE: -- This procedure returns all alert details information displayed in the alert -- details page -- -- IN Parameters: -- severity_guid_in: RAW severity guid -- -- OUT Parameters: -- key_value_out: VARCHAR2 key value -- metric_info_out: CURSOR cursor containing labels, key order, and -- number of keys, etc. information -- composite_key_info_out: CURSOR cursor containing composite key information -- alert_details_out: CURSOR cursor containing alert details information -- PROCEDURE get_alert_details(severity_guid_in IN RAW, key_value_out OUT VARCHAR2, metric_info_out OUT cursorType, composite_key_info_out OUT cursorType, alert_details_out OUT cursorType, execution_id_out OUT RAW); -- -- Name: clear_metric_severity -- -- Package: emd_mntr -- -- Purpose: -- clear the metric severity -- -- IN parameters: -- sev_guids_in: MGMT_USER_GUID_ARRAY severity guids -- annotation_in: VARCHAR2 the user annotation for clearing the severity -- message_in: VARCHAR2 the message for the clear severity record -- PROCEDURE clear_metric_severity(sev_guids_in IN MGMT_USER_GUID_ARRAY, annotation_in IN VARCHAR2, message_in IN VARCHAR2); -- -- Name: clear_metric_severity -- -- Package: emd_mntr -- -- Purpose: -- clear the metric severity -- -- IN parameters: -- target_name_in: VARCHAR2 target name -- target_type_in: VARCHAR2 target type -- metric_name_in: VARCHAR2 metric name -- metric_column_in: VARCHAR2 metric column -- key_value_in: VARCHAR2 key value -- annotation_in: VARCHAR2 the user annotation for clearing the severity -- message_in: VARCHAR2 the message for the clear severity record -- PROCEDURE clear_metric_severity(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, annotation_in IN VARCHAR2, message_in IN VARCHAR2); -- -- Name: add_metric_annotation -- -- Package: emd_mntr -- -- Purpose: -- add user-entered annotation -- -- IN parameters: -- target_name_in: VARCHAR2 target name -- target_type_in: VARCHAR2 target type -- metric_name_in: VARCHAR2 metric name -- metric_column_in: VARCHAR2 metric column -- key_value_in: VARCHAR2 key value -- annotation_in: VARCHAR2 the annotation that the user entered -- OUT parameters: -- -- Exceptions: MGMT_GLOBAL.target_does_not_exist -- MGMT_GLOBAL.no_such_metric_found PROCEDURE add_metric_annotation(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, annotation_in IN VARCHAR2); /* * Get the thresholds/current severities for all the key values monitored * for a key-based metric. */ PROCEDURE get_key_value_current_details(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, has_priv_out OUT NUMBER, statefull_out OUT NUMBER, thresholds_out OUT cursorType, current_metric_sev_out OUT cursorType); /* * Get the average values/thresholds/any current severities for * all the key values monitored for a key-based metric * within a time period. */ PROCEDURE get_key_value_avg_details( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, time_period_in IN VARCHAR2, has_priv_out OUT NUMBER, statefull_out OUT NUMBER, key_values_out OUT cursorType, avg_details_out OUT cursorType, thresholds_out OUT cursorType, target_info_out OUT cursorType); /* * Get the concurrent manager site map related info. * This procedure returns all the relevant information for a given * concurrent manager target name and oracle_cmanager target type. * The values returned as OUT parameters are used for display in the * cmOverview page */ PROCEDURE get_cmanager_sitemap_info (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, last_load_time_out OUT VARCHAR2, current_status_out OUT INTEGER, avail_pct_out OUT NUMBER, severity_code_out OUT INTEGER, num_severity_out OUT INTEGER, request_successful_out OUT INTEGER, request_warning_out OUT INTEGER, request_error_out OUT INTEGER, current_forms_out OUT INTEGER, standby_request_out OUT INTEGER, pending_request_out OUT INTEGER, running_request_out OUT INTEGER); /* * Get the forms6 listener site map related info. * This procedure returns all the relevant information for a given * forms6 listener target name and oracle_forms6 target type. * The values returned as OUT parameters are used for display in the * forms6Overview page */ PROCEDURE get_forms6_sitemap_info (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, last_load_time_out OUT VARCHAR2, current_status_out OUT INTEGER, avail_pct_out OUT NUMBER, severity_code_out OUT INTEGER, num_severity_out OUT INTEGER, num_forms_sessions_out OUT INTEGER, mem_util_out OUT NUMBER, cpu_util_out OUT NUMBER, apps_context_out OUT VARCHAR2); /* * Get the reports6 site map related info. * This procedure returns all the relevant information for a given * reports6 target name and oracle_reports6 target type. * The values returned as OUT parameters are used for display in the * reports6Overview page */ PROCEDURE get_reports6_sitemap_info (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, last_load_time_out OUT VARCHAR2, current_status_out OUT INTEGER, avail_pct_out OUT NUMBER, severity_code_out OUT INTEGER, num_severity_out OUT INTEGER, num_users_out OUT INTEGER, mem_util_out OUT NUMBER, cpu_util_out OUT NUMBER); /* * Get the E-Business Suite site map related info. * This procedure returns all the relevant information for a given * E-Business Suite target name and oracle_EBS target type. * The values returned as OUT parameters are used for display in the * ebsOverview page */ PROCEDURE get_ebs_sitemap_info (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, last_load_time_out OUT VARCHAR2, current_status_out OUT INTEGER, avail_pct_out OUT NUMBER, severity_code_out OUT INTEGER, num_severity_out OUT INTEGER, current_forms_out OUT INTEGER, standby_request_out OUT INTEGER, pending_request_out OUT INTEGER, running_request_out OUT INTEGER, version_out OUT VARCHAR2, products_out OUT INTEGER, invalid_objects_out OUT INTEGER, patches_out OUT INTEGER, has_chronos_data_out OUT VARCHAR2); -- -- PROCEDURE: get_console_homepage_info -- -- PURPOSE: -- This procedure returns information displayed in the console homepage. -- -- IN Parameters: -- target_type_in: The target type. It's NULL for all targets. If not NULL, -- the cursors return information for only that specific -- target type -- -- OUT Parameters: -- num_targets_out: The number of targets -- target_types_cur_out: The cursor containing target types information -- avail_cur_out: The cursor containing target availabilities information -- severity_cur_out: The cursor containing target severities information -- metric_error_cur_out: The cursor containing metric errors information -- num_targets_cur_out: The cursor containing numbers of monitored and -- unconfigured targets -- policy_violation_cur_out: The cursor containing policy violations information -- mt_broken_ca_cur_out: The cursor contains metric thresholds broken -- corrective action information -- policy_broken_ca_cur_out: policy broken corrective action -- -- metric thresholds broken corrective action -- note: if there is no record, the cursor still contains one row -- with count = 0 -- PROCEDURE get_console_homepage_info(target_type_in IN VARCHAR2, num_targets_out OUT NUMBER, target_types_cur_out OUT cursorType, avail_cur_out OUT cursorType, severity_cur_out OUT cursorType, metric_error_cur_out OUT cursorType, policy_violation_cur_out OUT cursorType, num_targets_cur_out OUT cursorType , mt_broken_ca_cur_out OUT cursorType , policy_broken_ca_cur_out OUT cursorType ); -- -- PROCEDURE: get_console_homepage_info_simp -- -- PURPOSE: -- This procedure returns information displayed in the console homepage when -- simplified mode is used to increase the performance. -- This procedure follows same logic as get_console_homepage_info, but with -- fewer cursor and query. -- -- IN Parameters: -- target_type_in: The target type. It's NULL for all targets. If not NULL, -- the cursors return information for only that specific -- target type -- -- OUT Parameters: -- num_targets_out: The number of targets -- target_types_cur_out: The cursor containing target types information -- avail_cur_out: The cursor containing target availabilities information -- PROCEDURE get_console_homepage_info_simp(target_type_in IN VARCHAR2, num_targets_out OUT NUMBER, target_types_cur_out OUT cursorType, avail_cur_out OUT cursorType ); -- -- PROCEDURE: get_group_homepage_info -- -- PURPOSE: -- This procedure returns information displayed in the console homepage. -- -- IN Parameters: -- target_name_in: The group target name -- target_type_in: The target type. It's NULL for all targets. If not NULL, -- the cursors return information for only that specific -- target type -- config_type_in: The view selection of the inventory table -- -- OUT Parameters: -- num_group_targets_out: The number of group targets -- avail_cur_out: The cursor containing target availabilities information -- severity_cur_out: The cursor containing target severities information -- metric_error_cur_out: The cursor containing metric errors information -- num_targets_cur_out: The cursor containing numbers of monitored and -- unconfigured targets -- PROCEDURE get_group_homepage_info( target_name_in VARCHAR2, target_type_in IN VARCHAR2, num_group_targets_out OUT NUMBER, avail_cur_out OUT cursorType, severity_cur_out OUT cursorType, metric_error_cur_out OUT cursorType, num_targets_cur_out OUT cursorType); -- -- PROCEDURE: get_targets_summary -- -- PURPOSE: -- Returns a summary of targets/metrics detail given the internal system -- folder name -- -- IN Parameters: -- folder_types_in: SMP_EMD_STRING_ARRAY list of target types -- supported by this folder - empty array -- is treated as all targets -- search_target_name_in: VARCHAR2 The target name to be searched. -- DEFAULTS TO NULL -- Also note that the calling code should pass in -- a processed value for search_target_name_in -- For example, if the search target string is -- "dlsun1285" then the actual passed in string for -- search_target_name_in should be %DLSUN1285% -- target_type_in: VARCHAR2 The target type -- -- OUT Parameters: -- case_number_out: NUMBER 1 - 5 for 5 different cases, based on the number of -- target types of the folder and the count of -- memberDerivedAlert properties of those types in -- mgmt_type_properties table -- user_prefs_cur_out: cursorType A cursor containing all the columns -- (ordered) set in the user prefs (or the defaults prefs). -- metric_sevs_cur_out: cursorType A cursor containing target guid, metric guid, -- and metric severity code -- metric_vals_cur_out: cursorType A cursor containing target guid, metric guid, -- and current metric value -- targets_cur_out: cursorType A cursor which returns rows that may contain: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- display_name: VARCHAR2 The display name of the target -- type_display_name: VARCHAR2 The display name of the target type -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- target_guid: The GUID of the associated target -- has_member_status: NUMBER 1 if the severity is for a target with member -- targets, 0 otherwise -- prop_vals_cur_out: cursorType A cursor containing the property values -- for all properties the user wishes to visualize (prefs). -- PROCEDURE get_targets_summary(folder_types_in IN SMP_EMD_STRING_ARRAY, search_query_in IN VARCHAR2 DEFAULT NULL, case_number_out OUT NUMBER, user_prefs_cur_out OUT cursorType, metric_sevs_cur_out OUT cursorType, metric_vals_cur_out OUT cursorType, targets_cur_out OUT cursorType, prop_vals_cur_out OUT cursorType, member_type_cur_out OUT cursorType); -- -- PROCEDURE: get_targets_summary -- -- PURPOSE: -- Returns a summary of targets/metrics detail given the internal system -- folder name -- -- IN Parameters: -- folder_types_in: SMP_EMD_STRING_ARRAY list of target types -- supported by this folder - empty array -- is treated as all targets -- search_target_name_in: VARCHAR2 The target name to be searched. -- DEFAULTS TO NULL -- Also note that the calling code should pass in -- a processed value for search_target_name_in -- For example, if the search target string is -- "dlsun1285" then the actual passed in string for -- search_target_name_in should be %DLSUN1285% -- target_type_in: VARCHAR2 The target type -- -- OUT Parameters: -- case_number_out: NUMBER 1 - 5 for 5 different cases, based on the number of -- target types of the folder and the count of -- memberDerivedAlert properties of those types in -- mgmt_type_properties table -- user_prefs_cur_out: cursorType A cursor containing all the columns -- (ordered) set in the user prefs (or the defaults prefs). -- metric_sevs_cur_out: cursorType A cursor containing target guid, metric guid, -- and metric severity code -- metric_vals_cur_out: cursorType A cursor containing target guid, metric guid, -- and current metric value -- targets_cur_out: cursorType A cursor which returns rows that may contain: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- display_name: VARCHAR2 The display name of the target -- type_display_name: VARCHAR2 The display name of the target type -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- target_guid: The GUID of the associated target -- has_member_status: NUMBER 1 if the severity is for a target with member -- targets, 0 otherwise -- prop_vals_cur_out: cursorType A cursor containing the property values -- for all properties the user wishes to visualize (prefs). -- PROCEDURE get_targets_summary(folder_types_in IN SMP_EMD_STRING_ARRAY, search_query_in IN VARCHAR2 DEFAULT NULL, search_mode_in IN VARCHAR2 DEFAULT 0, tgt_types_srch_lst_in IN SMP_EMD_STRING_ARRAY DEFAULT NULL, target_name_in IN VARCHAR2 DEFAULT NULL, location_in IN VARCHAR2 DEFAULT NULL, lob_in IN VARCHAR2 DEFAULT NULL, deployment_in IN VARCHAR2 DEFAULT NULL, comment_in IN VARCHAR2 DEFAULT NULL, contact_in IN VARCHAR2 DEFAULT NULL, case_number_out OUT NUMBER, user_prefs_cur_out OUT cursorType, metric_sevs_cur_out OUT cursorType, metric_vals_cur_out OUT cursorType, targets_cur_out OUT cursorType, prop_vals_cur_out OUT cursorType, member_type_cur_out OUT cursorType); -- -- PROCEDURE: get_types_in_target_selection -- -- PURPOSE: -- Returns 3 lists of target types for target selection page : -- available target types, parent_types and child_types -- parent_types and child_types are result from mgmt_target_memberships table -- IN Parameters: NONE -- OUT Parameters: target_types_cursor_out list of target types -- parent_types_cur_out list of parent types -- child_types_cur_out list of child types PROCEDURE get_types_in_target_selection(priv_name_in IN VARCHAR2, target_types_cur_out OUT cursorType, parent_types_cur_out OUT cursorType, child_types_cur_out OUT cursorType); -- -- PROCEDURE: get_targets_info -- -- PURPOSE: -- Given list of target guids, return columns from mgmt_targets table -- IN Parameters: target_guids_in array of target guids -- OUT Parameters: targets_cursor_out cursor containing most columns from -- mgmt_targets table PROCEDURE get_targets_info(target_guids_in IN MGMT_USER_GUID_ARRAY, targets_cursor_out OUT cursorType); -- -- A util function to get the list of targets on which a user has a -- certain privilege -- FUNCTION GET_TARGETS_BY_HOST_WITH_PRIV(priv_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_name_search_in IN VARCHAR2, host_name_search_in IN VARCHAR2) RETURN TARGETS_CURSOR; FUNCTION GET_TARGETS_BY_MEMBERSHIP(priv_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_name_search_in IN VARCHAR2, parent_type_in IN VARCHAR2, parent_name_search_in IN VARCHAR) RETURN TARGETS_CURSOR; FUNCTION GET_TARGETS_BY_MEMBERS(priv_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_name_search_in IN VARCHAR2, child_type_in IN VARCHAR2, child_name_search_in IN VARCHAR) RETURN TARGETS_CURSOR; -- -- gets resource bundle path for target types -- PROCEDURE GET_TYPES_RESOURCE_BUNDLES(bundles_out OUT cursorType); -- -- Deletes multiple targets at a time -- This will call sdk_admin.delete_target() -- PROCEDURE DELETE_TARGETS(target_guids_in IN MGMT_USER_GUID_ARRAY); -- -- Updates/Insert License table -- If the EM user reads the License agreement page the first time we will -- insert a new record to MGMT_LICENSES table -- Otherwise update the check_dont_show column if necessary PROCEDURE UPDATE_USER_LICENSE(username_in IN VARCHAR2, i_agree_in IN VARCHAR2, setup_details_in IN SMP_EMD_STRING_ARRAY DEFAULT NULL); -- -- Name: get_target_mntr_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the monitoring settings which include metric thresholds, policies, and -- collections for the given target. -- -- IN parameters: -- target_name_in: VARCHAR2 the name of the target -- target_type_in: VARCHAR2 the type of the target -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- -- ERROR codes: -- TARGET_DOES_NOT_EXIST_ERR: target does not exist error -- PROCEDURE get_target_mntr_settings(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType); -- -- Name: get_template_mntr_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the monitoring settings which include metric thresholds, policies, and -- collections for the given template. -- -- IN parameters: -- template_name_in: VARCHAR2 the name of the template -- target_type_in: VARCHAR2 the type of the template -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- -- ERROR codes: -- TEMPLATE_DOES_NOT_EXIST_ERR: template does not exist error -- PROCEDURE get_template_mntr_settings(template_name_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType); -- -- Name: get_template_crt_mntr_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the monitoring settings which include metric thresholds, policies, and -- collections for creating a template with the given target. -- -- IN parameters: -- target_name_in: VARCHAR2 the name of the target -- target_type_in: VARCHAR2 the type of the target -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- -- ERROR codes: -- TARGET_DOES_NOT_EXIST_ERR: target does not exist error -- PROCEDURE get_template_crt_mntr_settings(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType); -- -- Name: get_template_copy_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the monitoring settings which include metric thresholds, policies, and -- collections for the given template copy. -- -- IN parameters: -- template_copy_guid_in: VARCHAR2 the guid of the template copy -- target_type_in: VARCHAR2 the type of the template copy -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- -- PROCEDURE get_template_copy_settings(template_copy_guid_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType); -- -- Name: get_template_copy_tgt_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the monitoring settings which include metric thresholds, policies, and -- collections for the given template copy and the specified target. -- -- IN parameters: -- template_copy_guid_in: VARCHAR2 the guid of the template copy -- target_type_in: VARCHAR2 the type of the template copy -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- target_guid_in: VARCHAR2 the destination target guid -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- -- PROCEDURE get_template_copy_tgt_settings(template_copy_guid_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType, target_guid_in IN VARCHAR2); -- -- Name: get_mntr_settings_copy -- -- Package: emd_mntr -- -- Purpose: -- Get the metric thresholds, policies, and collections for the given -- monitoring settings copy. -- -- IN parameters: -- mntr_set_copy_guid_in: VARCHAR2 the guid of the template copy -- target_type_in: VARCHAR2 the type of the template copy -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- -- PROCEDURE get_mntr_settings_copy(mntr_settings_copy_guid_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType); -- -- Name: get_target_settings -- -- Package: emd_mntr -- -- Purpose: -- Get target settings for the given target. -- -- IN parameters: -- target_name_in: VARCHAR2 the name of the target -- target_type_in: VARCHAR2 the type of the target -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_priv_out: NUMBER 1 if the user has at least OPERATOR_TARGET privilege -- 0 otherwise -- has_remote_out: NUMBER if we have remote metrics for this target type -- has_update_execution_out: NUMBER if target has pending update executions -- agent_current_status_out: NUMBER the current staus of the agent if applicable -- agent_version_out: VARCHAR2 the agent version if applicable -- is_baselineable_out: VARCHAR2 whether or not the target instance or target type is baselineable -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- -- ERROR codes: -- TARGET_DOES_NOT_EXIST_ERR: target does not exist error -- PROCEDURE get_target_settings(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_priv_out OUT NUMBER, has_remote_out OUT NUMBER, has_update_execution_out OUT NUMBER, agent_current_status_out OUT NUMBER, agent_version_out OUT VARCHAR2, is_baselineable_out OUT VARCHAR2, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType); -- Name: get_template_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the general data, access data and monitoring settings (which include metric -- thresholds, policies, and collections) for the given template. -- -- IN parameters: -- template_name_in: VARCHAR2 the name of the template -- target_type_in: VARCHAR2 the type of the template -- -- OUT parameters: -- template_owner_out: VARCHAR2 template owner -- template_description_out: VARCHAR2 template description -- is_public_out: NUMBER whether or not this is a public template (as opposed -- to an out-of-box template) -- has_priv_out: NUMBER 0 if user does not have FULL_TEMPLATE Privilege, 1 otherwise -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE metric threshold and policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- -- ERROR codes: -- TEMPLATE_DOES_NOT_EXIST_ERR: template does not exist error -- PROCEDURE get_template_settings(template_name_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, template_owner_out OUT VARCHAR2, template_description_out OUT VARCHAR2, is_public_out OUT NUMBER, has_priv_out OUT NUMBER, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType); -- -- Name: get_target_lib_mntr_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the monitoring settings which include policies and collections for -- policy library with the given target type. This is used in the case when -- we add policies from the library to a target. The given target will be -- used to retrieve the default collection information. -- -- IN parameters: -- target_name_in: VARCHAR2 the name of the target -- target_type_in: VARCHAR2 the type of the target -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- default_udp_repository_cur_out: CURSORTYPE collection information for udp repository metrics -- -- ERROR codes: -- TARGET_DOES_NOT_EXIST_ERR: target does not exist error -- PROCEDURE get_target_lib_mntr_settings(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType, default_udp_repository_cur_out OUT cursorType); -- -- Name: get_template_lib_mntr_settings -- -- Package: emd_mntr -- -- Purpose: -- Get the monitoring settings which include policies and collections for -- policy library with the given target type. This is used in the case when -- we add policies from the library to a template. -- -- IN parameters: -- template_name_in: VARCHAR2 the name of the template. It's for the -- consistency and won't be used. -- target_type_in: VARCHAR2 the type of the template -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- has_remote_out: NUMBER if we have remote metrics for this target type -- policies_cur_out: CURSORTYPE policy information -- collections_cur_out: CURSORTYPE collection information -- key_render_cur_out: CURSORTYPE renderable information of key columns -- policy_ctxt_cur_out CURSORTYPE violation context definions for policies -- ca_multitask_cur_out: CURSORTYPE multitask corrective actions -- ca_param_cur_out: CURSORTYPE corrective actions and their parameters -- ca_cred_cur_out: CURSORTYPE override credentials for corrective actions -- job_types_cur_out: CURSORTYPE job-types usable for CAs on this target-type -- default_udp_repository_cur_out: CURSORTYPE collection information for udp repository metrics -- PROCEDURE get_template_lib_mntr_settings(template_name_in IN VARCHAR2, target_type_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, has_remote_out OUT NUMBER, policy_cur_out OUT cursorType, collection_cur_out OUT cursorType, key_render_cur_out OUT cursorType, policy_ctxt_cur_out OUT cursorType, ca_multitask_cur_out OUT cursorType, ca_param_cur_out OUT cursorType, ca_cred_cur_out OUT cursorType, job_types_cur_out OUT cursorType, default_udp_repository_cur_out OUT cursorType); -- -- Name: save_target_settings -- -- Package: emd_mntr -- -- Purpose: -- Save target settings for the given target. -- -- IN parameters: -- target_name_in: VARCHAR2 the name of the target -- target_type_in: VARCHAR2 the type of the target -- metric_list_in: MGMT_MNTR_METRIC_ARRAY the metric threshold data -- policy_list_in: MGMT_MNTR_POLICY_ARRAY the policy data -- collection_list_in: MGMT_MNTR_COLLECTION_ARRAY the collection data -- -- OUT parameters: -- emd_url_out: VARCHAR2 the emd URL -- -- ERROR codes: -- TARGET_DOES_NOT_EXIST_ERR: target does not exist error -- PROCEDURE save_target_settings(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_list_in IN MGMT_MNTR_METRIC_ARRAY, policy_list_in IN MGMT_MNTR_POLICY_ARRAY, collection_list_in IN MGMT_MNTR_COLLECTION_ARRAY, emd_url_out OUT VARCHAR2); -- -- Name: get_target_collection_info -- -- Package: emd_mntr -- -- Purpose: -- Get the information of all metrics (and metric columns) collected -- in the given collection for the given target, and all policies -- defined on these metrics for this target. -- -- IN parameters: -- target_name_in: VARCHAR2 the name of the target -- target_type_in: VARCHAR2 the type of the target -- coll_name_in: VARCHAR2 the collection item name -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- metric_cur_out: CURSORTYPE the metrics collected in the given collection -- policy_cur_out: CURSORTYPE the policies defined on these metrics -- -- ERROR codes: -- TARGET_DOES_NOT_EXIST_ERR: target does not exist error -- PROCEDURE get_target_collection_info(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, coll_name_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, metric_cur_out OUT cursorType, policy_cur_out OUT cursorType); -- -- Name: get_template_collection_info -- -- Package: emd_mntr -- -- Purpose: -- Get the information of all metrics (and metric columns) collected -- together with the given metric accross all target versions, and all policies -- defined on these metrics for this template. -- -- IN parameters: -- template_name_in: VARCHAR2 the name of the template -- target_type_in: VARCHAR2 the type of the target -- metric_guid_in: VARCHAR2 the metric guid -- language_code_in: VARCHAR2 client locale's language code abbreviation -- country_code_in: VARCHAR2 client locale's country code abbreviation -- -- OUT parameters: -- metric_cur_out: CURSORTYPE the metrics collected with the given metric -- policy_cur_out: CURSORTYPE the policies defined on these metrics -- -- ERROR codes: -- TEMPLATE_DOES_NOT_EXIST_ERR: template does not exist error -- PROCEDURE get_template_collection_info(template_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_guid_in IN VARCHAR2, language_code_in IN VARCHAR2, country_code_in IN VARCHAR2, metric_cur_out OUT cursorType, policy_cur_out OUT cursorType); -- -- Name: get_metric_collection_info -- -- Package: emd_mntr -- -- Purpose: -- For each metric of the given metric list, if it is collected together with -- some other metrics accross all target versions, then get the information of -- all metrics (and metric columns) collected together with this metric. -- -- IN parameters: -- target_type_in: VARCHAR2 the type of the target -- metric_guid_list_in: VARCHAR2 the list of metric guids -- -- OUT parameters: -- metric_cur_out: CURSORTYPE the metrics collected with each metric of the -- given metric list if it is collected together with some -- other metrics accross all target versions -- PROCEDURE get_metric_collection_info(target_type_in IN VARCHAR2, metric_guid_list_in IN MGMT_GUID_ARRAY, metric_cur_out OUT cursorType); -- -- search_query_in -> Obsolete (maintaining it for backward compatibility -- apply_operation1_cur_out -> apply_operation1_cur_out contains template names, agent -- names and execution ids -- apply_operation2_cur_out -> apply_operation1_cur_out contains the rest of the -- detais for each apply operation (destination -- target, job owner, submission timestamp, etc.) -- p_temp_name_op -> template_name :: 1 = IS_EXACTLY , 0 = CONTAINS -- p_temp_name -> template_name -- p_tgt_name_op -> target_name :: 1 = IS_EXACTLY , 0 = CONTAINS -- p_tgt_type -> target_type :: % = ALL -- p_mode -> Past or Pending Apply Operations :: past / pending PROCEDURE get_template_apply_operations(search_query_in IN VARCHAR2 DEFAULT NULL, apply_operation1_cur_out OUT SYS_REFCURSOR, apply_operation2_cur_out OUT SYS_REFCURSOR, p_temp_name_op IN NUMBER DEFAULT NULL, p_temp_name IN VARCHAR2 DEFAULT NULL, p_tgt_name_op IN NUMBER DEFAULT NULL, p_tgt_name IN VARCHAR2 DEFAULT NULL, p_tgt_type IN VARCHAR2 DEFAULT NULL, p_mode IN VARCHAR2 DEFAULT NULL); PROCEDURE get_template_apply_ttypes( p_mode IN VARCHAR2 DEFAULT NULL, p_c_tgt_types OUT cursortype); -- -- Name: get_has_template_priv -- -- Package: emd_mntr -- -- Purpose: -- Finds out whether or not the current user has the FULL_TEMPLATE privilege on -- a particular template and if there currently are pending changes against -- the selected template -- -- IN parameters: -- template_name_in: VARCHAR2 the name of the template -- target_type_in: VARCHAR2 the template's target type -- event_name_in: VARCHAR2 the event that called the API. It can be editing -- or deleting a template but we only check for pending changes in the case of -- a delete event (this is to allow the current user to stop the pending -- changes) -- -- OUT parameters: -- has_priv_out: NUMBER 1 if the user has at least FULL_TEMPLATE privilege -- 0 otherwise -- pending_changes_priv_out: CURSOR the number of pending changes agaisnt -- targets on which the current user has at least the VIEW privilege -- all_pending_changes_out: NUMBER the number of all the current pending changes -- for the selected template, regardless of the user privileges on the -- destination targets -- -- ERROR codes: -- TEMPLATE_DOES_NOT_EXIST_ERR: template does not exist error -- PROCEDURE get_has_template_priv(template_name_in IN VARCHAR2, target_type_in IN VARCHAR2, event_name_in IN VARCHAR2, has_priv_out OUT NUMBER, pending_changes_priv_out OUT cursorType, all_pending_changes_out OUT NUMBER); -- -- Name: get_has_template_privilege -- -- Package: emd_mntr -- -- Purpose: -- Finds out whether or not the current user has the FULL_TEMPLATE privilege on -- a particular template -- -- IN parameters: -- template_name_in: VARCHAR2 the name of the template -- target_type_in: VARCHAR2 the template's target type -- -- OUT parameters: -- has_priv_out: NUMBER 1 if the user has at least FULL_TEMPLATE privilege -- 0 otherwise -- -- ERROR codes: -- TEMPLATE_DOES_NOT_EXIST_ERR: template does not exist error -- PROCEDURE get_has_template_privilege(template_name_in IN VARCHAR2, target_type_in IN VARCHAR2, has_priv_out OUT NUMBER); -- -- Name: get_pending_changes -- -- Package: emd_mntr -- -- Purpose: -- Get the number of pending changes for all templates in the system, including -- the templates that have been deleted. If the target information is passed in, -- it's the number of pending changes for that target. -- -- Return: -- NUMBER the number of pending changes -- FUNCTION get_pending_changes(target_name_in IN VARCHAR2 DEFAULT NULL, target_type_in IN VARCHAR2 DEFAULT NULL) RETURN NUMBER; -- -- Name: get_bcn_test_cur_summary -- -- Package: emd_mntr -- -- Purpose: -- Gets the key test summary (#up #down and #alerts) and bcn count -- for a given target type -- -- IN parameters: -- target_type_in: VARCHAR2 the targets' target type -- -- OUT parameters: -- test_summary: CURSOR -- bcn_summary: CURSOR -- -- ERROR codes: -- TARGET_DOES_NOT_EXIST_ERR -- PROCEDURE get_bcn_test_cur_summary(target_type_in IN VARCHAR2, test_summary OUT cursorType, bcn_summary OUT cursorType); -- repository support for modify_threshold verb PROCEDURE modify_threshold(target_name VARCHAR2, target_type VARCHAR2, metric VARCHAR2, column VARCHAR2, coll_name VARCHAR2, key_info VARCHAR2 DEFAULT '', operator NUMBER, warn VARCHAR2, crit VARCHAR2, schedule_ex VARCHAR2, agent_fixit VARCHAR2, num_occurrences NUMBER ); -- Remove all traces of a given UDM from repository PROCEDURE create_udm(target_name VARCHAR2, target_type VARCHAR2, udm_name VARCHAR2, udm_type VARCHAR2, warn VARCHAR2, crit VARCHAR2, schedule_ex VARCHAR2, agent_fixit VARCHAR2, num_occurrences NUMBER, id VARCHAR2, command VARCHAR2, valuetype VARCHAR2, operator NUMBER, message VARCHAR2, already_exists NUMBER, is_multi NUMBER DEFAULT 0, threshold_string VARCHAR2 DEFAULT '', collection_enabled NUMBER DEFAULT 1 ) ; -- Remove all traces of a given UDM from repository PROCEDURE delete_udm(p_target_name VARCHAR2, p_target_type VARCHAR2, p_udm_name VARCHAR2, p_udm_type VARCHAR2); --This Procedure Is used to retreive the list of Metric Errors --Public Procedure:get_metric_errors --IN parameters: p_emd_url --OUT parameters:p_met_cur PROCEDURE get_metric_errors (p_emd_url IN VARCHAR2, p_met_cur OUT CursorType); -- Remove all traces of a given UDM from repository PROCEDURE delete_udm(p_target_guid IN RAW, p_metric_guid IN RAW, p_object_type IN NUMBER, p_coll_name IN VARCHAR2 ); -- PROCEDURE: is_server_gnrtd_metric -- -- PURPOSE: -- This procedure checks if the db version is 10g or greater and the metric -- is server generated metric -- -- IN Parameters: -- target_name_in: The target name -- target_type_in: The target type. -- metric_guid_in : metric guid -- OUT Parameters: -- is_srvr_gnrtd : 0 (if metric is not server generated) or 1 (if metric is server generated) PROCEDURE is_server_gnrtd_metric( target_name_in IN MGMT_TARGETS.target_name%TYPE, target_type_in IN MGMT_TARGETS.target_type%TYPE, metric_guid_in IN MGMT_METRICS.metric_guid%TYPE, is_srvr_gnrtd OUT MGMT_METRICS.has_push%TYPE); -- FUNCTION: check_metric_parent -- -- IN Parameters: -- l_target_name_in : The target name -- l_target_type_in : The target type. -- l_metric_name_in : The metric name -- l_metric_column_in : The metric column -- OUT Parameter: -- l_is_parent : 0 (if metric parent doesnt contain '%Tablespace%') -- or any integer (if metric parent contains '%Tablespace%') /* Added for Bug No 6017721 This checks if the metric_label for the specified metric_name, metric_column combination contains '%Tablespace%'. Returns 0 if no match is found and the original count in case the occurrences happen. */ FUNCTION check_metric_parent (l_target_name_in IN MGMT_TARGETS.target_name%TYPE DEFAULT NULL, l_target_type_in IN MGMT_TARGETS.target_type%TYPE, l_metric_name_in IN MGMT_METRICS.metric_name%TYPE, l_metric_column_in IN MGMT_METRICS.metric_column%TYPE) RETURN NUMBER; -- FUNCTION: check_for_warn_crit_tip -- -- IN Parameters: -- l_target_name_in : The target name -- l_target_type_in : The target type. -- l_metric_name_in : The metric name -- l_metric_column_in : The metric column -- OUT Parameter: -- l_is_parent : 0 (if column label doesnt contain 'Tablespace Space Used (%)') -- or any integer (if column label contains 'Tablespace Space Used (%)') /* Added for Bug No 6017688 This checks if the column_label for the specified metric_name, metric_column combination contains 'Tablespace Space Used (%)'. Returns 0 if no match is found and the original count in case the occurrences happen. */ FUNCTION check_for_warn_crit_tip (l_target_name_in IN MGMT_TARGETS.target_name%TYPE DEFAULT NULL, l_target_type_in IN MGMT_TARGETS.target_type%TYPE, l_metric_name_in IN MGMT_METRICS.metric_name%TYPE, l_metric_column_in IN MGMT_METRICS.metric_column%TYPE) RETURN NUMBER; end emd_mntr; / show errors;