Rem Rem $Header: basic_loader_pkgdef.sql 03-jun-2008.12:16:38 jashukla Exp $ Rem Rem basic_loader_pkgdef.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem basic_loader_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem jashukla 06/03/08 - Bug 7144619 remove internal identifiers Rem jsadras 05/03/07 - Backport jsadras_bug-5357916 from main Rem smodh 03/29/07 - Add Host Metadata Caching Rem smodh 04/11/07 - Backport smodh_bug-4727918 from main Rem neearora 09/13/05 - Bug 4607971. added procedure Rem submit_discard_state_job Rem neearora 04/02/07 - Backport neearora_bug-4607971 from main Rem rmaggarw 03/05/07 - bug5856156 - use seconds internally Rem rmaggarw 03/12/07 - Backport rmaggarw_bug-5856156 from main Rem neearora 07/24/06 - project 21470. added API to register callback Rem for CRS event Rem neearora 03/27/06 - Bug 5108394. Adding capability in loader to Rem delete rows through uploads Rem neearora 07/16/06 - Backport neearora_bug-5108394 from main Rem scgrover 07/12/06 - Backport scgrover_bug-5043773 from main Rem pmodi 05/02/06 - Bug:3900473 - Move p_part% and p_*_keep_parm to Rem body Rem pmodi 07/07/06 - Backport pmodi_bug-3900473 from main Rem rkpandey 12/12/05 - Backport rkpandey_bug-4753716 from main Rem rkpandey 12/07/05 - Bug 4753716:Set last load type while releasing Rem lock Rem rpinnama 09/21/05 - Fix 4595139 : Obtain settings lock before Rem updating the settings Rem pmodi 08/24/05 - Bug:4561906 - Handle exception during error Rem logging for broken target Rem rkpandey 08/18/05 - Remove redundant get_target_details Rem pmodi 08/16/05 - Bug:4538663 - Include order by clause and chg Rem batch size Rem rmaggarw 07/28/05 - cleanup admin messages Rem rmaggarw 07/26/05 - 4436017: cutover nio -> dbms_lock Rem pmodi 07/21/05 - Bug:4260179 - Handle broken target Rem neearora 07/20/05 - Bug 4500972. upload callback added Rem scgrover 07/06/05 - add extended sql trace Rem neearora 07/05/05 - Canged obtain/release lock_for_emd_url to accept Rem file type Rem neearora 06/28/05 - added variable p_current_upload_time to capture Rem file upload time in start_upload Rem rmaggarw 06/28/05 - add logPerformance Rem rmaggarw 06/20/05 - add get_sharedfs_backlog Rem jsadras 06/20/05 - Fetch Batchsize Rem neearora 06/16/05 - Bug 4343519. Added to procedure Rem obtain_lock_for_emd_url and Rem release_lock_for_emd_url Rem neearora 06/10/05 - add_name_port_from_emd_url added Rem rmaggarw 05/31/05 - add msgId in dequeue Rem rpinnama 05/06/05 - Fix bug 4302515 : Insert the change_agent_url Rem rec to track changes across files Rem jsadras 03/28/05 - convert to constant Rem jsadras 03/10/05 - Bug:4186866, metaver callback support Rem rpinnama 03/15/05 - Fix 4116241: Support only restricted updates on Rem mgmt_target.timezone_region Rem rkpandey 03/05/05 - Added get_target_details Rem rmaggarw 03/10/05 - 4175804: install api for sharedfs Rem ramalhot 03/03/05 - fixed bug 4218008 Rem asawant 02/09/05 - Add protocol version constants. Rem rpinnama 12/03/04 - Fix 4044204 : Add procedure to add host Rem Respnse/Status metric Rem rmaggarw 10/08/04 - add procedures for shared_fs Rem ramalhot 09/11/04 - get_table_object added Rem rpinnama 09/29/04 - Add register_rowset_handler API Rem vkgarg 08/24/04 - updating retention APIs Rem vkgarg 08/24/04 - adding retention period APIs Rem gan 07/01/04 - target direct load Rem mbhoopat 11/16/03 - Added standby target list to END_MGMT_TARGETS Rem rpinnama 10/31/03 - Add APIs to get/set metadata status Rem streddy 09/26/03 - Added category_prop check Rem streddy 09/25/03 - Added duptarget cursor end_targets_rowset Rem streddy 09/23/03 - Added END_MGMT_TARGET_MEMBERSHIPS Rem rpinnama 09/07/03 - Add purge/rollup procedures used for purge Rem rzazueta 08/18/03 - Fix bug 2960829: Return resolved targets' list Rem in END_MGMT_TARGETS_ROWSET Rem rpinnama 08/06/03 - Add init_loader_params and update_loader_params Rem edemembe 07/01/03 - Adding partition support Rem streddy 04/21/03 - Target added callback support Rem sgrover 03/19/03 - remove variance Rem streddy 02/20/03 - Added multi-agent support Rem sgrover 01/08/03 - change purge windows to match ui defaults Rem rpinnama 11/12/02 - modify the signature Rem edemembe 11/06/02 - Setting retention times to exact day/month Rem rpinnama 11/08/02 - Support add_target procedure Rem rpinnama 11/05/02 - Add START_MGMT_TARGETS_ROWSET, END_MGMT_TARGETS_ROWSET procedures Rem snakai 01/02/03 - Add new raw table Rem rpinnama 09/18/02 - Remove authid current_user clause Rem edemembe 08/08/02 - Adding performance logging Rem rpinnama 07/15/02 - Remove LOAD_FILE_COUNT and OLDEST_LOAD_FILE_AGE_MINUTES Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE EMD_LOADER AUTHID CURRENT_USER AS -- -- ****** WARNING!! NON-JAVA DECLARATIONS MUST BE BEFORE JAVA !!! ****** -- -- Constants for ROWSET handlers G_ROWSET_HANDLER_DEFAULT CONSTANT NUMBER(1) := 0; G_ROWSET_HANDLER_PLSQL CONSTANT NUMBER(1) := 1; G_ROWSET_HANDLER_PLSQL_DELETE CONSTANT NUMBER(1) := 2; TYPE DUPTARGET_CURSOR IS REF CURSOR; TYPE DELETETARGET_CURSOR IS REF CURSOR; TYPE RESOLVEDTARGET_CURSOR IS REF CURSOR; TYPE METADATASET_CURSOR IS REF CURSOR; TYPE STANDBYTARGET_CURSOR IS REF CURSOR; TYPE CURSORTYPE IS REF CURSOR; PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN); PROCEDURE ROLLUP; PROCEDURE EMD_RAW_PURGE; PROCEDURE EMD_1HOUR_ROLLUP; PROCEDURE EMD_1HOUR_PURGE; FUNCTION EMD_1HOUR_CLEANUP RETURN BOOLEAN; PROCEDURE EMD_1DAY_ROLLUP; PROCEDURE EMD_1DAY_PURGE; FUNCTION EMD_1DAY_CLEANUP RETURN BOOLEAN; PROCEDURE EMD_PURGE_ERROR_LOG; PROCEDURE EMD_PURGE_PERFORMANCE_LOG; PROCEDURE EMD_CURRENT_METRIC_PURGE; PROCEDURE EMD_INITIALIZE; PROCEDURE INIT_RAW_PARTITIONS; PROCEDURE INIT_1HOUR_PARTITIONS; PROCEDURE INIT_1DAY_PARTITIONS; FUNCTION EMD_GET_TARGET_INFO(cur_target_guid IN RAW) RETURN BOOLEAN; FUNCTION IS_MULTI_AGENT_TARGET(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2) RETURN NUMBER; FUNCTION IS_MULTI_AGENT_TARGET(p_monitoring_mode IN NUMBER) RETURN NUMBER; --- --- Gets retention period for fetching raw data from repository --- FUNCTION GET_RAW_RETENTION_WINDOW RETURN NUMBER; --- --- Gets retention period for fetching 1 hour data from repository --- FUNCTION GET_1HOUR_RETENTION_WINDOW RETURN NUMBER; --- --- Gets retention period for fetching 1 day data from repository --- FUNCTION GET_1DAY_RETENTION_WINDOW RETURN NUMBER; --- --- Attempts to add an agent to the list of agents of a Multi-Agent --- Target (MAT). If the specified agent is already present, it ignores --- the entry. -- --- ERROR CODES: --- TARGET_DOES_NOT_EXIST_ERR: If there is corresponding agent for the --- specified emd_url. --- PROCEDURE UPDATE_MAT_AGENT_LIST(p_target_guid IN RAW, p_emd_url IN VARCHAR2); -- -- Adds a target addition callback -- PROCEDURE ADD_TARGET_ADDITION_CALLBACK(p_callback_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT ' '); -- -- Execute target addition callbacks -- PROCEDURE EXEC_TARGET_ADDITION_CALLBACKS(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_guid IN RAW); -- -- Deletes a target addition callback -- PROCEDURE DEL_TARGET_ADDITION_CALLBACK(p_callback_name IN VARCHAR2, p_target_type IN VARCHAR2 DEFAULT ' '); -- BEGIN PROCEDURES used by the Loader PROCEDURE INIT_LOADER_PARAMS(p_host_url VARCHAR2, p_loader_params SMP_EMD_NVPAIR_ARRAY); PROCEDURE UPDATE_LOADER_PARAMS(p_host_url VARCHAR2, p_loader_params SMP_EMD_NVPAIR_ARRAY); PROCEDURE START_UPLOAD(p_emd_url_in IN VARCHAR2, p_upload_time_in IN VARCHAR2, p_pre_load_proc IN VARCHAR2 DEFAULT NULL, p_post_load_proc IN VARCHAR2 DEFAULT NULL, p_duptarget_details_out OUT DUPTARGET_CURSOR, p_deletetarget_details_out OUT DELETETARGET_CURSOR); PROCEDURE START_MGMT_TARGETS_ROWSET(p_start_time_out OUT DATE); PROCEDURE END_MGMT_TARGETS_ROWSET(p_emd_url_in IN VARCHAR2, p_start_time_in IN VARCHAR2, p_last_updated_time_in IN VARCHAR2, p_resolved_dup_targets_out OUT RESOLVEDTARGET_CURSOR, p_duptarget_details_out OUT DUPTARGET_CURSOR, p_standby_targets_out OUT STANDBYTARGET_CURSOR); PROCEDURE END_CHANGE_AGENT_URL(p_old_agent_url IN VARCHAR2, p_new_agent_url IN VARCHAR2); PROCEDURE END_UPLOAD(p_emd_url_in IN VARCHAR2, p_upload_time_in IN VARCHAR2, p_raw_list IN EM_LOADER_UPLOAD_TIME_ARRAY, p_rt_raw_list IN EM_LOADER_UPLOAD_TIME_ARRAY, p_e2e_raw_list IN EM_LOADER_UPLOAD_TIME_ARRAY); PROCEDURE UPSERT_DUPLICATE_TARGET(p_target_guid IN RAW, p_emd_url IN VARCHAR2); PROCEDURE UPSERT_DUPLICATE_TARGET(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_guid IN RAW, p_emd_url IN VARCHAR2, p_timezone_rgn IN VARCHAR2); PROCEDURE START_MEMBERSHIPS_ROWSET; PROCEDURE END_MEMBERSHIPS_ROWSET; PROCEDURE END_METADATA_SET(p_target_type_in IN VARCHAR2, p_type_meta_ver_in IN VARCHAR2, p_table_name_in IN VARCHAR2, p_platform_in IN VARCHAR2 DEFAULT NULL); PROCEDURE GET_LOADED_METADATA_SETS( p_loaded_metadata_sets_out OUT METADATASET_CURSOR); -- Procedure to get table object PROCEDURE GET_TABLE_OBJECT(v_table_name IN VARCHAR2, v_pk_cur OUT CURSORTYPE, v_lob_cur OUT CURSORTYPE, v_handler_cur OUT CURSORTYPE); -- END PROCEDURES used by the Loader -- BEGIN PROCEDURES used by the target direct loader -- for repository only targets PROCEDURE ADD_TARGET(p_upsert IN NUMBER, p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_properties IN MGMT_TARGET_PROPERTY_LIST, p_target_display_name IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_target_credentials IN MGMT_TARGET_CRED_ARRAY DEFAULT NULL, p_member_targets IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL); -- This procedure to work around the restriction placed in mgmt_target -- package for modify membership. That package requires member targets has -- been added. The procedure should be obsoleted once a generic solution is -- available in mgmt_target package. Parent target should exist. PROCEDURE MODIFY_MEMBERSHIP(p_parent_target_name IN VARCHAR2, p_parent_target_type IN VARCHAR2, p_targets_to_add SMP_EMD_NVPAIR_ARRAY); -- END PROCEDURES used by the target direct loader -- BEGIN Purge policies callbacks PROCEDURE STRING_HISTORY_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS); PROCEDURE METRIC_ERROR_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS); -- END Purge policies callbacks -- Procedure called by before update trigger on targets table PROCEDURE ADD_HOST_AVAIL_METRIC(p_target_guid IN RAW, 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 ' '); -- Procedure called by target before update trigger PROCEDURE CHECK_CAT_PROP_CHANGES(v_cat_prop_name IN VARCHAR2, v_target_name IN VARCHAR2, v_old_value IN VARCHAR2, v_new_value IN OUT VARCHAR2); -- Procedure called by target insert trigger PROCEDURE ADD_TARGET(v_target_guid IN RAW); -- Returns the URL of the currently loading Agent. One can -- use this to do some special processing based on which -- agent is loading the data. For example, for multi-agent -- targets, one can use this to filter out non-master data. FUNCTION GET_CURRENT_EMD_URL RETURN VARCHAR2; -- Procedure to change the emd_url for a target. PROCEDURE CHANGE_TARGET_EMD_URL(v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_old_emd_url IN VARCHAR2, v_new_emd_url IN VARCHAR2); -- Procedure to associate a handler to a rowset -- NOTE : Does not throw any exception, if the association already exists PROCEDURE add_handler_for_rowset( p_rowset_name IN VARCHAR2, p_protocol_version IN VARCHAR2 DEFAULT ' ', p_handler_type IN NUMBER, p_handler_info IN VARCHAR2); -- Procedure to add a PL/SQL handler to a rowset PROCEDURE add_plsql_handler_for_rowset( p_rowset_name IN VARCHAR2, p_protocol_version IN VARCHAR2 DEFAULT ' ', p_proc_name IN VARCHAR2, p_handler_type IN NUMBER DEFAULT G_ROWSET_HANDLER_PLSQL); -- Procedure to remove the handler associated with a rowset. PROCEDURE remove_handler_for_rowset( p_rowset_name IN VARCHAR2, p_protocol_version IN VARCHAR2 DEFAULT ' ', p_handler_type IN NUMBER DEFAULT G_ROWSET_HANDLER_PLSQL); --Nobody should call this except for testing purposes PROCEDURE SET_CURRENT_EMD_URL(v_emd_url IN VARCHAR2); -- BEGIN Procedures used for Shared FS PROCEDURE INITIALIZE_SHAREDFS; -- Enqueue message PROCEDURE ENQUEUE(p_designator IN VARCHAR2, p_mesg IN VARCHAR2, p_delay IN INTEGER DEFAULT DBMS_AQ.NO_DELAY); -- Generate unique filename and enqueue message PROCEDURE RECEIVE(p_designator IN VARCHAR2, p_filename OUT VARCHAR2); -- Check for messages for the list of designators PROCEDURE LISTEN(p_designators IN SMP_EMD_STRING_ARRAY, p_qtimeout IN NUMBER, p_qname OUT VARCHAR2); -- Dequeue first available message for designator PROCEDURE DEQUEUE(p_designator IN VARCHAR2, p_qtimeout IN NUMBER, p_remove IN NUMBER, p_mesg OUT VARCHAR2, p_mid IN OUT RAW); -- Dequeue admin message PROCEDURE DEQUEUE_ADMIN(p_designator IN VARCHAR2, p_qtimeout IN NUMBER, p_mesg OUT VARCHAR2); -- Dequeue w/o consuming messages in AQ and lock the designator PROCEDURE DEQUEUE_BROWSE(p_designator IN VARCHAR2, p_qtimeout IN NUMBER, p_filename OUT VARCHAR2, p_mid OUT RAW); -- Dequeue consume and release lock held PROCEDURE DEQUEUE_CONSUME(p_designator IN VARCHAR2, p_qtimeout IN NUMBER, p_mid IN RAW); --Obtain lock and store handle internally PROCEDURE OBTAIN_LOCK_FOR_DESIGNATOR(p_designator IN VARCHAR2); --Release the currently held lock PROCEDURE RELEASE_LOCK_FOR_DESIGNATOR; -- Get agent_host_name and port from emd_url -- http://host.example.com:4889/emd/main/ PROCEDURE get_name_port_from_emd_url ( p_emd_url IN mgmt_targets.emd_url%TYPE, p_agent_name OUT VARCHAR2, p_agent_port OUT VARCHAR2 ); -- Register OMS for partipation in loading metric -- data files PROCEDURE REGISTER_SHAREDFS(p_oms_id IN NUMBER, p_admin_qname OUT VARCHAR2); -- Callback for MGMT_FAILOVER package PROCEDURE OMS_FAILOVER(p_oms_id IN NUMBER, p_last_timestamp IN DATE); -- Cleanup leftover admin messages PROCEDURE CLEANUP_ADMIN_MESSAGES; -- Withdraw OMS from participating in loading -- data files PROCEDURE UNREGISTER_SHAREDFS(p_oms_id IN NUMBER); -- Update loader metrics PROCEDURE UPDATE_LOADER_METRICS(p_oms_url VARCHAR2); -- Log loader performance metrics into system performance table PROCEDURE LOG_PERFORMANCE(p_host_url IN VARCHAR2, p_module IN SMP_EMD_STRING_ARRAY, p_name IN SMP_EMD_STRING_ARRAY, p_value IN SMP_EMD_INTEGER_ARRAY, p_duration IN SMP_EMD_INTEGER_ARRAY); -- Procedure to add numeric metric data to mgmt_metrics_raw PROCEDURE ADD_METRIC_DATA (v_target_guid IN RAW, v_metric_guid IN RAW, v_key_value IN VARCHAR2, v_value IN NUMBER, v_timestamp IN DATE DEFAULT SYSDATE); -- Procedure to add string metric data to mgmt_metrics_raw PROCEDURE ADD_METRIC_DATA (v_target_guid IN RAW, v_metric_guid IN RAW, v_key_value IN VARCHAR2, v_string_value IN VARCHAR2, v_timestamp IN DATE DEFAULT SYSDATE); -- update current metric table with cached metric values PROCEDURE UPDATE_CURRENT_METRICS(p_targets IN SMP_EMD_STRING_ARRAY, p_metrics IN SMP_EMD_STRING_ARRAY, p_keys IN SMP_EMD_STRING_ARRAY, p_times IN SMP_EMD_STRING_ARRAY, p_values IN SMP_EMD_STRING_ARRAY, p_svalues IN SMP_EMD_LARGE_STRING_ARRAY); -- update string metric history table with cached metric values PROCEDURE UPDATE_STRING_METRICS_HISTORY(p_targets IN SMP_EMD_STRING_ARRAY, p_metrics IN SMP_EMD_STRING_ARRAY, p_keys IN SMP_EMD_STRING_ARRAY, p_times IN SMP_EMD_STRING_ARRAY, p_svalues IN SMP_EMD_LARGE_STRING_ARRAY); FUNCTION UPDATE_STRING_METRICS_ROW(p_target IN RAW, p_metric IN RAW, p_key IN VARCHAR2, p_time IN DATE, p_svalue IN VARCHAR2) RETURN NUMBER; FUNCTION UPDATE_CURRENT_METRICS_ROW(p_target IN RAW, p_metric IN RAW, p_key IN VARCHAR2, p_time IN DATE, p_value IN NUMBER, p_svalue IN VARCHAR2) RETURN NUMBER; -- Get backlog metrics for SharedFS loader PROCEDURE GET_SHAREDFS_BACKLOG(p_oms_url IN VARCHAR2, p_backlog OUT NUMBER, p_agesecs OUT NUMBER); -- Get load coordinator parameters PROCEDURE GET_LOAD_DESIGNATORS(p_ld_label OUT VARCHAR2, p_ld_count OUT NUMBER); -- Install APIs to support Shared filesystem mode PROCEDURE GET_HA_MODE(p_ha_mode OUT NUMBER, p_recv_dir OUT VARCHAR2); PROCEDURE SET_HA_MODE(p_ha_mode IN NUMBER, p_recv_dir IN VARCHAR2 DEFAULT ' '); -- API to start/check/end emd_url change PROCEDURE set_emd_url_change ( p_last_emd_url IN mgmt_targets.emd_url%TYPE, p_emd_url IN mgmt_targets.emd_url%TYPE); FUNCTION is_emd_url_change_allowed( p_last_emd_url IN mgmt_targets.emd_url%TYPE, p_emd_url IN mgmt_targets.emd_url%TYPE) RETURN BOOLEAN; PROCEDURE clear_emd_url_change( p_last_emd_url IN mgmt_targets.emd_url%TYPE, p_emd_url IN mgmt_targets.emd_url%TYPE); -- Obtain the lock for the emd_url PROCEDURE obtain_lock_for_emd_url( p_emd_url IN mgmt_targets.emd_url%TYPE, p_timeout_secs IN NUMBER := 30, p_load_type IN VARCHAR2, p_lock_handle OUT VARCHAR2); -- Release the lock for the emd_url PROCEDURE release_lock_for_emd_url( p_lock_handle IN VARCHAR2, p_emd_url IN VARCHAR2, p_load_type IN VARCHAR2, p_clean_load IN NUMBER, p_lock_released OUT NUMBER); -- Procedure to check metadata is cached in database or not. PROCEDURE check_metadata_sets( p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_table_name IN VARCHAR2, p_metadata_cnt OUT NUMBER); -- APIs to obtain and release metric setting locks -- Procedure to obtain metric settings lock PROCEDURE obtain_metric_settings_lock( p_lock_name IN VARCHAR2, p_timeout_secs IN NUMBER := 30, p_release_on_commit IN NUMBER := 0, p_lock_handle OUT VARCHAR2); -- Procedure to release the metric settings lock PROCEDURE release_metric_settings_lock( p_lock_handle IN VARCHAR2, p_lock_released OUT NUMBER); -- APIs to start and end the tzrgn change. FUNCTION allow_tzrgn_change RETURN BOOLEAN; PROCEDURE set_tzrgn_change; PROCEDURE clear_tzrgn_change; -- END Procedures for Shared FS -- -- PROCEDURE: register_pre_load_callback -- -- PURPOSE: -- Registers the given load_proc to the repository. PROCEDURE register_load_callback ( p_callback_name IN VARCHAR2, p_callback_type IN NUMBER ); -- PROCEDURE: deregister_load_callback -- -- PURPOSE: -- Deregisters the given load_proc from the repository. PROCEDURE deregister_load_callback ( p_callback_name IN VARCHAR2, p_callback_type IN NUMBER ); -- -- PROCEDURE: execute_pre_load_callback -- -- PURPOSE: -- This procedure executes the given pre_load_proc PROCEDURE execute_pre_load_callback ( p_pre_load_proc IN VARCHAR2, p_input IN SMP_EMD_NVPAIR_ARRAY, p_pre_load_output OUT SMP_EMD_NVPAIR_ARRAY ); -- -- PROCEDURE: execute_post_load_callback -- -- PURPOSE: -- This procedure executes the given post_load_proc. PROCEDURE execute_post_load_callback ( p_post_load_proc IN VARCHAR2, p_input IN SMP_EMD_NVPAIR_ARRAY, p_pre_load_output IN SMP_EMD_NVPAIR_ARRAY ); -- -- PROCEDURE: set_purge_batchsize -- -- PURPOSE: -- This procedure to set purge batch size in mgmt_parameters. -- -- IN parameters : -- p_purge_batchsize : Value for purge_batchsize PROCEDURE set_purge_batchsize ( p_purge_batchsize IN NUMBER ) ; -- -- PROCEDURE: get_purge_batchsize -- -- PURPOSE: -- This procedure to get purge batch size from mgmt_parameters. -- If no parameter is set in mgmt_parameters then it would return default value -- set at package level in p_purge_batchsize. FUNCTION get_purge_batchsize RETURN NUMBER ; -- -- FUNCTION: is_fatally_broken -- -- PURPOSE: -- Function to chk whether new target broken state is fatal or non-fatal. FUNCTION is_fatally_broken ( p_new_broken_state IN mgmt_targets.broken_reason%TYPE ) RETURN BOOLEAN ; PROCEDURE check_deletion_allowed ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_emd_url IN mgmt_targets.emd_url%TYPE, p_table_name IN VARCHAR2 ); -- -- PROCEDURE: register_cluster_crs_event_cbk -- -- PURPOSE: -- Registers the given cluster_crs_event_cbk with the repository. -- This callback will be invoked when ever any severity for -- cluster target type , crs_event metric is received. -- During registration some basic validations like -- NOT NULL and valid signature are also done. -- -- PARAMETERS: -- p_cluster_crs_event_cbk : Name of the callback procedure -- -- EXCEPTIONS -- InvaildParams: If callback name is null or signature of the callback procedure is not valid -- InvaildParams: If any callback is already registered -- PROCEDURE register_cluster_crs_event_cbk ( p_cluster_crs_event_cbk IN VARCHAR2 ); -- -- PROCEDURE: dereg_cluster_crs_event_cbk -- -- PURPOSE: -- Deregisters the given cluster_crs_event_cbk from the repository. -- -- PARAMETERS: -- p_cluster_crs_event_cbk : Name of the callback procedure -- -- EXCEPTIONS -- InvaildParams: If callback name is null -- InvaildParams: If no such callback is registered -- PROCEDURE dereg_cluster_crs_event_cbk ( p_cluster_crs_event_cbk IN VARCHAR2 ); -- -- PROCEDURE: submit_discard_state_job -- -- PURPOSE: -- Submit the discard state job for the given tergets PROCEDURE submit_discard_state_job ( p_emd_url IN mgmt_targets.emd_url%TYPE, p_job_target_names IN MGMT_JOB_VECTOR_PARAMS, p_job_target_types IN MGMT_JOB_VECTOR_PARAMS ); TYPE p_guid_list_type IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER; TYPE p_date_list_type IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE p_number_list_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE p_rowid_list_type IS TABLE OF UROWID INDEX BY BINARY_INTEGER; TYPE p_varchar2_256_list_type IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; TYPE p_hour_rollup_record_type IS RECORD ( metric_guid p_guid_list_type, rollup_time p_date_list_type, key_val p_varchar2_256_list_type, sample_count p_number_list_type, avg_val p_number_list_type, min_val p_number_list_type, max_val p_number_list_type, stddev_val p_number_list_type ); TYPE p_day_rollup_record_type IS RECORD ( metric_guid p_guid_list_type, rollup_time p_date_list_type, key_val p_varchar2_256_list_type, sample_count p_number_list_type, min_val p_number_list_type, max_val p_number_list_type, sumx_val p_number_list_type, sumx2_val p_number_list_type ); TYPE p_TargetGUIDList IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER; TYPE p_RowIdList IS TABLE OF UROWID INDEX BY BINARY_INTEGER; TYPE p_MetricGUIDList IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER; TYPE p_KeyValueList IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; TYPE p_MaxTimestampList IS TABLE OF DATE INDEX BY BINARY_INTEGER; -- p_PurgeBatch is removed as purge batch size can be controlled through mgmt_parameters --p_PurgeBatch NUMBER := 2000; -- This is the number of rows that will be -- purged in a single database call. p_LogPurgeWindow NUMBER := 168; -- Window (in hours) of data in log tables. p_TotalRowsProcessed NUMBER := 0; -- Counter for rows processed. p_metrics_raw_table CONSTANT VARCHAR(30) := 'MGMT_METRICS_RAW'; -- Intermediate table for partition conversion. p_metrics_raw_int_table CONSTANT VARCHAR(30) := 'MGMT_METRICS_RAW_INT'; p_metrics_1hour_table CONSTANT VARCHAR(30) := 'MGMT_METRICS_1HOUR'; -- Intermediate table for partition conversion. p_metrics_1hour_int_tab CONSTANT VARCHAR(30) := 'MGMT_METRICS_1HOUR_INT'; p_metrics_1day_table CONSTANT VARCHAR(30) := 'MGMT_METRICS_1DAY'; -- Intermediate table for partition conversion. p_metrics_1day_int_table CONSTANT VARCHAR(30) := 'MGMT_METRICS_1DAY_INT'; p_emd_schema VARCHAR(6) := 'SYSMAN'; p_metrics_1day_idx PLS_INTEGER := 1; p_metrics_1hour_idx PLS_INTEGER := 2; --Bug 3904399 :Following variable has been made private and moved to body -- p_part_retention_raw / p_prepopulate_days_1hour / p_prepopulate_days_1day -- p_raw_keep_param / p_hour_keep_param / p_day_keep_param p_prepopulate_days_raw PLS_INTEGER := 5; -- Days of partitions to prepopulate. p_prepopulate_days_1hour PLS_INTEGER := 5; -- Days of partitions to prepopulate. p_prepopulate_days_1day PLS_INTEGER := 5; -- Days of partitions to prepopulate. -- Constant for parameter name -- Used to set purge_batchsize in mgmt_parameters EM_PURGE_BATCHSIZE VARCHAR(32) := 'purge_batchsize'; -- Parameters of current and stringmetrics updating EM_CURRENT_METRICS_BATCHSIZE VARCHAR(32) := 'current_metrics_batchsize'; EM_STRING_METRICS_BATCHSIZE VARCHAR(32) := 'string_metrics_batchsize'; p_target_batchsize PLS_INTEGER := 2000; p_rollup_batchsize PLS_INTEGER := 2000; -- rows/bulk insert p_purge_batchsize PLS_INTEGER := 2000; -- rows/bulk delete p_fetch_batchsize PLS_INTEGER := 10000; -- rows/bulk delete -- -- p_default_raw_keep/p_default_hour_keep/p_default_day_keep -- duplicated in emd_maint_util as well. Please change there as -- well if below 3 are changed -- p_default_raw_keep NUMBER := 24; -- in hours p_default_hour_keep NUMBER := 168; -- in hours (7 days) p_default_day_keep NUMBER := 31; -- in days p_curdef_raw_keep NUMBER; p_curdef_hour_keep NUMBER; p_curdef_day_keep NUMBER; p_cur_target_guid RAW(16); p_cur_rollup_timestamps p_date_list_type; p_cur_rollup_rowids p_rowid_list_type; p_cur_raw_keep NUMBER; p_cur_hour_keep NUMBER; p_cur_day_keep NUMBER; -- Performance logging variables p_1hour_rollup_time NUMBER := 0; p_1hour_rollup_records NUMBER := 0; p_raw_purge_time NUMBER := 0; p_raw_purge_records NUMBER := 0; p_1day_rollup_time NUMBER := 0; p_1day_rollup_records NUMBER := 0; p_1hour_purge_time NUMBER := 0; p_1hour_purge_records NUMBER := 0; p_1day_purge_time NUMBER := 0; p_1day_purge_records NUMBER := 0; -- Current target's rollup/purge time boundaries p_cur_hour_rollup DATE; p_cur_day_rollup DATE; p_cur_hour_purge DATE; p_cur_day_purge DATE; p_ErrMsg VARCHAR(2048); p_Module VARCHAR(128); p_Action VARCHAR(128); p_current_emd_url MGMT_TARGETS.emd_url%TYPE; p_current_upload_time VARCHAR2(20); -- list of targets added for each ROWSET p_targets_added SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); -- Bug 4260169/436302 -- List of target Added/Updated for each ROWSET -- List get appended in AFTER INSERT and BEFORE UPDATE trigger p_targets_loaded SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); -- List of target whose broken state have changed. -- This array would store TARGET_GUID and Message needed for logging metric error for broken target. -- State changed from > Un-Broken to Broken -- > Broken to Un-broken -- > Broken with Reason 1 to Broken with Reason 2 -- List get appended in AFTER INSERT and BEFORE UPDATE trigger if there is change in broken state/reason p_targets_broken MGMT_BROKEN_TARGET_LIST := MGMT_BROKEN_TARGET_LIST(); -- List of targets having meta version changes p_metaver_changes mgmt_target_meta_ver_cbk_array := mgmt_target_meta_ver_cbk_array() ; -- Admin message strings for SharedFilesystem ADMIN_START_MONITORING CONSTANT VARCHAR2(6) := 'START-'; ADMIN_STOP_MONITORING CONSTANT VARCHAR2(6) := 'STOP-'; -- List of protocol versions PROT_10_1_0_2 CONSTANT VARCHAR2(10) := '10.1.0.2'; -- HA_MODE values HA_MODE_SHAREDFS CONSTANT NUMBER := 0; HA_MODE_NONSHAREDFS CONSTANT NUMBER := 1; HA_MODE_UNDEFINED CONSTANT NUMBER := -1; HA_MODE_PARAM CONSTANT VARCHAR2(32) := 'loaderCoordinationMethod'; HA_RECV_DIR_PARAM CONSTANT VARCHAR2(32) := 'loader_recv_dir'; EST_ROLLUP_NAME CONSTANT VARCHAR2(32) := 'EST_ROLLUP'; END EMD_LOADER; / show errors;