Rem Rem $Header: storage_rep_metrics_pkgbody.sql 25-jul-2005.22:02:04 sreddy Exp $ Rem Rem storage_rep_metrics_pkgbody.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem storage_rep_metrics_pkgdef.sql - Rem host/group storage repository metrics for storage history Rem Rem DESCRIPTION Rem host/group storage repository metrics for storage history Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ajdsouza 08/31/05 - add interface test_rep_metrics Rem sreddy 07/23/05 - fixed bug#4253179 Rem sreddy 07/23/05 - Cleanup: removed some local variables Rem that has no functional use Rem sreddy 07/23/05 - Cleanup: remove call to Rem storage_ui_common_pkg.get_missing_storage_data_info Rem sreddy 07/23/05 - Cleanup: Remove p_total_hosts, p_summarized_hosts Rem parameters from calc_metric() internal Rem procedure Rem rmenon 05/02/05 - Rem rmenon 03/24/05 - Rem rmenon 03/02/05 - code cleanup Rem rmenon 11/22/04 - fixed volume allocated bug (allocated and Rem unallocated were swapped in history data; Rem added logic to compute new history columns Rem related to errors and total/summarized target Rem counts. Rem rmenon 11/02/04 - Rem rmenon 10/13/04 - Rem rmenon 10/01/04 - consolidated host and group history logic. Rem rmenon 09/08/04 - Rem rmenon 08/27/04 - Rem rmenon 08/24/04 - fix for bug 3839907 Rem rmenon 08/17/04 - rmenon_storage_refresh_errors_history_checkin Rem rmenon 08/16/04 - Created Rem CREATE OR REPLACE PACKAGE BODY storage_rep_metrics_pkg AS G_MISSING_COLL_DEFN_PFX constant VARCHAR2(100) := 'Host(s) with missing definition of host_storage collection: '; ------------------------------------------------------------- --- forward declaration of private procedures/functions ----- ------------------------------------------------------------- -- calculates repository metrics for host or a group containing -- hosts. PROCEDURE calc_metrics ( p_target_guid IN RAW, p_total_overhead IN OUT NUMBER, p_total_used IN OUT NUMBER, p_total_free IN OUT NUMBER, p_total_unallocated IN OUT NUMBER, p_total_allocated IN OUT NUMBER, p_db_used IN OUT NUMBER, p_db_free IN OUT NUMBER, p_local_fs_used IN OUT NUMBER, p_local_fs_free IN OUT NUMBER, p_writeable_nfs_used IN OUT NUMBER, p_writeable_nfs_free IN OUT NUMBER, p_vol_allocated IN OUT NUMBER, p_vol_unallocated IN OUT NUMBER, p_vol_overhead IN OUT NUMBER, p_asm_allocated IN OUT NUMBER, p_asm_unallocated IN OUT NUMBER, p_asm_overhead IN OUT NUMBER, p_disk_allocated IN OUT NUMBER, p_disk_unallocated IN OUT NUMBER, p_total_dbs IN OUT NUMBER, p_summarized_dbs IN OUT NUMBER, p_total_asm_targets IN OUT NUMBER, p_summarized_asm_targets IN OUT NUMBER, p_unmonitored_nfs_count IN OUT NUMBER, p_other_mapping_issues IN OUT NUMBER, p_host_metric_errors IN OUT NUMBER, p_db_metric_errors IN OUT NUMBER, p_asm_metric_errors IN OUT NUMBER ); -- populates the mgmt_metric_result data structure given -- the storage data for host/group. FUNCTION get_metric_results ( p_target_type IN VARCHAR2, p_overhead IN OUT NUMBER, p_used IN OUT NUMBER, p_free IN OUT NUMBER, p_unallocated IN OUT NUMBER, p_allocated IN OUT NUMBER, p_db_used IN OUT NUMBER, p_db_free IN OUT NUMBER, p_local_fs_used IN OUT NUMBER, p_local_fs_free IN OUT NUMBER, p_writeable_nfs_used IN OUT NUMBER, p_writeable_nfs_free IN OUT NUMBER, p_vol_allocated IN OUT NUMBER, p_vol_unallocated IN OUT NUMBER, p_vol_overhead IN OUT NUMBER, p_asm_allocated IN OUT NUMBER, p_asm_unallocated IN OUT NUMBER, p_asm_overhead IN OUT NUMBER, p_disk_allocated IN OUT NUMBER, p_disk_unallocated IN OUT NUMBER, p_total_hosts IN OUT NUMBER, p_summarized_hosts IN OUT NUMBER, p_total_dbs IN OUT NUMBER, p_summarized_dbs IN OUT NUMBER, p_total_asm_targets IN OUT NUMBER, p_summarized_asm_targets IN OUT NUMBER, p_unmonitored_nfs_count IN OUT NUMBER, p_other_mapping_issues IN OUT NUMBER, p_host_metric_errors IN OUT NUMBER, p_db_metric_errors IN OUT NUMBER, p_asm_metric_errors IN OUT NUMBER, p_overhead_col IN VARCHAR2, p_used_col IN VARCHAR2, p_free_col IN VARCHAR2, p_unallocated_col IN VARCHAR2, p_allocated_col IN VARCHAR2, p_db_used_col IN VARCHAR2, p_db_free_col IN VARCHAR2, p_local_fs_used_col IN VARCHAR2, p_local_fs_free_col IN VARCHAR2, p_writeable_nfs_used_col IN VARCHAR2, p_writeable_nfs_free_col IN VARCHAR2, p_vol_allocated_col IN VARCHAR2, p_vol_unallocated_col IN VARCHAR2, p_vol_overhead_col IN VARCHAR2, p_asm_allocated_col IN VARCHAR2, p_asm_unallocated_col IN VARCHAR2, p_asm_overhead_col IN VARCHAR2, p_disk_allocated_col IN VARCHAR2, p_disk_unallocated_col IN VARCHAR2, p_total_hosts_col IN VARCHAR2, p_summarized_hosts_col IN VARCHAR2, p_total_dbs_col IN VARCHAR2, p_summarized_dbs_col IN VARCHAR2, p_total_asm_targets_col IN VARCHAR2, p_summarized_asm_targets_col IN VARCHAR2, p_unmonitored_nfs_count_col IN VARCHAR2, p_other_mapping_issues_col IN VARCHAR2, p_host_metric_errors_col IN VARCHAR2, p_db_metric_errors_col IN VARCHAR2, p_asm_metric_errors_col IN VARCHAR2 ) RETURN mgmt_metric_results; --------------------------------------------------------------- ---------- definition of public procedures/functions ---------- --------------------------------------------------------------- -------------------------------------------------------- -- -- Name: calc_storage_history -- -- Package: storage_rep_metrics -- -- Purpose: -- Calculates storage host history related repository metrics. -- Returns the result in the form of mgmt_metric_results. -- The calculated data includes: -- 1. overhead -- 2. used -- 3. free -- 4. unallocated -- 5. allocated -- 6. db_used -- 7. db_free -- 8. local_fs_used -- 9. local_fs_free -- 10. writeable_nfs_used -- 11. writeable_nfs_free -- 12. vol_allocated -- 13. vol_unallocated -- 14. vol_overhead -- 15. asm_allocated -- 16. asm_unallocated -- 17. asm_overhead -- 18. disk_allocated -- 19. disk_unallocated -- -- IN Parameters: -- p_target_guid - the target guid if the host -- p_metric_guid - the metric guid of the repository metric -- -- OUT Parameters: -- p_metric_result - host storage repository metric -- calculation results as required by -- the repository metrics API. -- -- NOTE: -- The signature of this procedure is dictated by the -- the repository collections SDK requirements. ----------------------------------------------------------- PROCEDURE calc_storage_history ( p_target_guid IN RAW, p_metric_guid IN RAW, p_metric_result OUT mgmt_metric_results ) is l_target_name mgmt_targets.target_name%TYPE; l_target_type mgmt_targets.target_type%TYPE; l_index INTEGER; l_overhead NUMBER; l_used NUMBER; l_free NUMBER; l_unallocated NUMBER; l_allocated NUMBER; l_db_used NUMBER; l_db_free NUMBER; l_local_fs_used NUMBER; l_local_fs_free NUMBER; l_writeable_nfs_used NUMBER; l_writeable_nfs_free NUMBER; l_vol_allocated NUMBER; l_vol_unallocated NUMBER; l_vol_overhead NUMBER; l_asm_allocated NUMBER; l_asm_unallocated NUMBER; l_asm_overhead NUMBER; l_disk_allocated NUMBER; l_disk_unallocated NUMBER; l_num_missing_coll_defn NUMBER; l_num_with_disabled_coll NUMBER; l_num_missing_snap NUMBER; l_num_of_dbs_missing_snap NUMBER; l_num_of_asm_missing_metric NUMBER; -- new error message related variables l_total_hosts NUMBER; l_summarized_hosts NUMBER; l_total_dbs NUMBER; l_summarized_dbs NUMBER; l_total_asm_targets NUMBER; l_summarized_asm_targets NUMBER; l_unmonitored_nfs_count NUMBER; l_other_mapping_issues NUMBER; l_host_metric_errors NUMBER; l_db_metric_errors NUMBER; l_asm_metric_errors NUMBER; begin -- insert data into temporary table on which all -- queries depend storage_ui_common_pkg.insert_targets_for_ui_queries( p_target_guid ); storage_ui_common_pkg.get_target_name_type ( p_target_guid => p_target_guid, p_target_name => l_target_name, p_target_type => l_target_type ); -- if it is not a host, it must be a group. If so, -- if the group's collection has been disabled, -- then return without proceeding further. if ( l_target_type != mgmt_global.g_host_target_type and storage_ui_group_pkg.get_storage_hist_enabled( l_target_name, l_target_type ) = storage_ui_util_pkg.NO ) then -- may be stop the collection RETURN; end if; -- select total number of hosts from the -- temporary table mgmt_storage_report_ui_targets -- which should already have been populated. SELECT count(*) INTO l_total_hosts FROM MGMT_STORAGE_REPORT_UI_TARGETS WHERE target_type = mgmt_global.G_HOST_TARGET_TYPE; -- select summarized number of hosts from the -- temporary table mgmt_storage_report_ui_targets SELECT count(*) INTO l_summarized_hosts FROM MGMT_STORAGE_REPORT_UI_TARGETS WHERE ecm_snapshot_id IS NOT NULL; IF (l_summarized_hosts = 0) THEN -- No computations required RETURN; END IF; -- calculate metrics calc_metrics ( p_target_guid => p_target_guid, p_total_overhead => l_overhead, p_total_used =>l_used, p_total_free => l_free, p_total_unallocated => l_unallocated, p_total_allocated => l_allocated, p_db_used => l_db_used, p_db_free => l_db_free, p_local_fs_used => l_local_fs_used, p_local_fs_free => l_local_fs_free, p_writeable_nfs_used => l_writeable_nfs_used, p_writeable_nfs_free => l_writeable_nfs_free, p_vol_allocated => l_vol_allocated, p_vol_unallocated => l_vol_unallocated, p_vol_overhead => l_vol_overhead, p_asm_allocated => l_asm_allocated, p_asm_unallocated => l_asm_unallocated, p_asm_overhead => l_asm_overhead, p_disk_allocated => l_disk_allocated, p_disk_unallocated => l_disk_unallocated, p_total_dbs => l_total_dbs, p_summarized_dbs => l_summarized_dbs, p_total_asm_targets => l_total_asm_targets, p_summarized_asm_targets => l_summarized_asm_targets, p_unmonitored_nfs_count => l_unmonitored_nfs_count, p_other_mapping_issues => l_other_mapping_issues, p_host_metric_errors => l_host_metric_errors, p_db_metric_errors => l_db_metric_errors, p_asm_metric_errors => l_asm_metric_errors ); p_metric_result := get_metric_results ( p_target_type => l_target_type, p_overhead => l_overhead, p_used => l_used, p_free => l_free, p_unallocated => l_unallocated, p_allocated => l_allocated, p_db_used => l_db_used, p_db_free=> l_db_free, p_local_fs_used=> l_local_fs_used, p_local_fs_free=> l_local_fs_free, p_writeable_nfs_used=> l_writeable_nfs_used, p_writeable_nfs_free=> l_writeable_nfs_free, p_vol_allocated=> l_vol_allocated, p_vol_unallocated=> l_vol_unallocated, p_vol_overhead=> l_vol_overhead, p_asm_allocated=> l_asm_allocated, p_asm_unallocated=> l_asm_unallocated, p_asm_overhead=> l_asm_overhead, p_disk_allocated=> l_disk_allocated, p_disk_unallocated=> l_disk_unallocated, p_total_hosts => l_total_hosts, p_summarized_hosts => l_summarized_hosts, p_total_dbs => l_total_dbs, p_summarized_dbs => l_summarized_dbs, p_total_asm_targets => l_total_asm_targets, p_summarized_asm_targets => l_summarized_asm_targets, p_unmonitored_nfs_count => l_unmonitored_nfs_count, p_other_mapping_issues => l_other_mapping_issues, p_host_metric_errors => l_host_metric_errors, p_db_metric_errors => l_db_metric_errors, p_asm_metric_errors => l_asm_metric_errors, p_overhead_col => storage_rep_metrics_pkg.g_total_overhead, p_used_col => storage_rep_metrics_pkg.g_total_used, p_free_col => storage_rep_metrics_pkg.g_total_free, p_unallocated_col => storage_rep_metrics_pkg.g_total_unallocated, p_allocated_col => storage_rep_metrics_pkg.g_total_allocated, p_db_used_col => storage_rep_metrics_pkg.g_db_used, p_db_free_col => storage_rep_metrics_pkg.g_db_free, p_local_fs_used_col => storage_rep_metrics_pkg.g_local_fs_used, p_local_fs_free_col => storage_rep_metrics_pkg.g_local_fs_free, p_writeable_nfs_used_col => storage_rep_metrics_pkg.g_writeable_nfs_used, p_writeable_nfs_free_col => storage_rep_metrics_pkg.g_writeable_nfs_free, p_vol_allocated_col => storage_rep_metrics_pkg.g_vol_allocated, p_vol_unallocated_col => storage_rep_metrics_pkg.g_vol_unallocated, p_vol_overhead_col => storage_rep_metrics_pkg.g_vol_overhead, p_asm_allocated_col => storage_rep_metrics_pkg.g_asm_allocated, p_asm_unallocated_col => storage_rep_metrics_pkg.g_asm_unallocated, p_asm_overhead_col => storage_rep_metrics_pkg.g_asm_overhead, p_disk_allocated_col => storage_rep_metrics_pkg.g_disks_allocated, p_disk_unallocated_col => storage_rep_metrics_pkg.g_disks_unallocated , p_total_hosts_col => storage_rep_metrics_pkg.g_total_hosts, p_summarized_hosts_col => storage_rep_metrics_pkg.g_summarized_hosts, p_total_dbs_col => storage_rep_metrics_pkg.g_total_dbs, p_summarized_dbs_col => storage_rep_metrics_pkg.g_summarized_dbs, p_total_asm_targets_col => storage_rep_metrics_pkg.g_total_asm, p_summarized_asm_targets_col => storage_rep_metrics_pkg.g_summarized_asm, p_unmonitored_nfs_count_col => storage_rep_metrics_pkg.g_unmonitored_nfs, p_other_mapping_issues_col => storage_rep_metrics_pkg.g_other_map_err, p_host_metric_errors_col => storage_rep_metrics_pkg.g_host_metric_err, p_db_metric_errors_col => storage_rep_metrics_pkg.g_db_metric_err, p_asm_metric_errors_col => storage_rep_metrics_pkg.g_asm_metric_err ); END calc_storage_history; -------------------------------------------------------- -- -- Name: test_rep_metrics -- -- Package: storage_rep_metrics -- -- Purpose: -- Execute the storage repository metrics for the -- specified target and print results to stdout -- . The target can be a host or a composite -------------------------------------------------------- PROCEDURE test_rep_metrics ( p_target_name mgmt_targets.target_name%TYPE, p_target_type mgmt_targets.target_type%TYPE DEFAULT MGMT_GLOBAL.G_HOST_TARGET_TYPE ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_results mgmt_metric_results; BEGIN l_target_guid := MGMT_TARGET.GET_TARGET_GUID (p_target_name,p_target_type); STORAGE_REP_METRICS_PKG.CALC_STORAGE_HISTORY ( l_target_guid, NULL, l_metric_results ); END test_rep_metrics; -------------------------------------------------------- -- -- Name: create_storage_rep_hist_metric -- -- Package: storage_rep_metrics -- -- Purpose: -- -- To add group storage history repository metric that -- gets collected daily. The metric contains storage related data -- to be shown in the storage history UI of group (or other -- similar "aggregate" target type. Note that this API should -- be used for a target type that can have more than one -- hosts associated with it. Also the target type home page -- should register a "Storage Details" related link. -- -- IN Parameters: -- p_target_type - type of the target -- p_type_meta_ver - the target type meta version -- ----------------------------------------------------------- PROCEDURE create_storage_rep_hist_metric ( p_target_type IN mgmt_targets.target_type%TYPE, p_type_meta_ver IN mgmt_metrics.type_meta_ver%TYPE ) AS l_grp_metric_column_list mgmt_metric_column_array; l_metric_column mgmt_metric_column_obj; BEGIN -- all the numeric columns can be added in a loop. l_grp_metric_column_list := mgmt_metric_column_array(); for i in 1..storage_rep_metrics_pkg.G_GRP_COLUMN_NAME_LIST.count loop l_metric_column := mgmt_metric_column_obj.new ( p_column_name => storage_rep_metrics_pkg.G_GRP_COLUMN_NAME_LIST(i), p_column_type => mgmt_global.G_METRIC_TYPE_NUMBER, p_column_label => storage_rep_metrics_pkg.G_GRP_COLUMN_LABEL_LIST(i), p_column_label_nlsid => storage_rep_metrics_pkg.G_GRP_COLUMN_NLSID_LIST(i), p_is_key => 0 ); l_grp_metric_column_list.extend(1); l_grp_metric_column_list( i ) := l_metric_column; END LOOP; mgmt_metric.create_metric ( p_target_type => p_target_type, p_metric_name => storage_rep_metrics_pkg.G_GROUP_STORAGE_REP_METRIC, p_type_meta_ver => p_type_meta_ver, p_valid_if_list => null, p_metric_type => mgmt_global.G_METRIC_TYPE_REPOS_TABLE, p_metric_label => storage_rep_metrics_pkg.G_GRP_STORAGE_REP_METRIC_LBL, p_metric_label_nlsid => storage_rep_metrics_pkg.G_GRP_STORAGE_HIST_MET_NLSID, p_source=>storage_rep_metrics_pkg.G_GRP_STORAGE_REP_METRIC_PROC, p_source_type => mgmt_global.G_METRIC_SOURCE_OLD_PLSQL, p_category_list => null, p_metric_column_list => l_grp_metric_column_list ); END create_storage_rep_hist_metric; /*-------------------------------------------------------------*/ /*--------- definition of private procedures/functions --------*/ /*-------------------------------------------------------------*/ PROCEDURE calc_metrics ( p_target_guid IN RAW, p_total_overhead IN OUT NUMBER, p_total_used IN OUT NUMBER, p_total_free IN OUT NUMBER, p_total_unallocated IN OUT NUMBER, p_total_allocated IN OUT NUMBER, p_db_used IN OUT NUMBER, p_db_free IN OUT NUMBER, p_local_fs_used IN OUT NUMBER, p_local_fs_free IN OUT NUMBER, p_writeable_nfs_used IN OUT NUMBER, p_writeable_nfs_free IN OUT NUMBER, p_vol_allocated IN OUT NUMBER, p_vol_unallocated IN OUT NUMBER, p_vol_overhead IN OUT NUMBER, p_asm_allocated IN OUT NUMBER, p_asm_unallocated IN OUT NUMBER, p_asm_overhead IN OUT NUMBER, p_disk_allocated IN OUT NUMBER, p_disk_unallocated IN OUT NUMBER, p_total_dbs IN OUT NUMBER, p_summarized_dbs IN OUT NUMBER, p_total_asm_targets IN OUT NUMBER, p_summarized_asm_targets IN OUT NUMBER, p_unmonitored_nfs_count IN OUT NUMBER, p_other_mapping_issues IN OUT NUMBER, p_host_metric_errors IN OUT NUMBER, p_db_metric_errors IN OUT NUMBER, p_asm_metric_errors IN OUT NUMBER ) IS l_target_name mgmt_targets.target_name%TYPE; l_target_type mgmt_targets.target_name%TYPE; l_db_on_local_disks NUMBER; l_db_on_volumes NUMBER; l_db_on_asm NUMBER; l_db_on_local_fs NUMBER; l_db_on_writeable_nfs NUMBER; l_db_allocated NUMBER; l_db_tablespace_size NUMBER; l_db_tablespace_used NUMBER; l_local_fs_on_local_disks NUMBER; l_local_fs_on_volumes NUMBER; l_disk_size NUMBER; l_asm_size NUMBER; l_vol_size NUMBER; l_writeable_nfs_allocated NUMBER; BEGIN -- get target name and type storage_ui_common_pkg.get_target_name_type ( p_target_guid => p_target_guid, p_target_name => l_target_name, p_target_type => l_target_type ); storage_ui_common_pkg.get_strg_data_cfg_and_errors ( p_target_name => l_target_name, p_target_type => l_target_type, p_total_dbs => p_total_dbs, p_summarized_dbs => p_summarized_dbs, p_total_asm_count => p_total_asm_targets, p_summarized_asm_count => p_summarized_asm_targets, p_host_metric_errors => p_host_metric_errors, p_db_metric_errors => p_db_metric_errors, p_asm_metric_errors => p_asm_metric_errors, p_unmonitored_nfs_count => p_unmonitored_nfs_count, p_other_issues_count => p_other_mapping_issues ); storage_ui_common_pkg.get_storage_summary_values ( p_target_name => l_target_name, p_target_type => l_target_type, p_calc_storage_source_flag => mgmt_global.G_FALSE, p_vol_allocated => p_vol_allocated, p_vol_overhead => p_vol_overhead, p_vol_unallocated => p_vol_unallocated, p_vol_size => l_vol_size, p_asm_allocated => p_asm_allocated, p_asm_unallocated => p_asm_unallocated, p_asm_overhead => p_asm_overhead, p_asm_size => l_asm_size, p_disk_allocated => p_disk_allocated, p_disk_unallocated => p_disk_unallocated, p_disk_size => l_disk_size, p_writeable_nfs_allocated => l_writeable_nfs_allocated, p_db_on_local_disks => l_db_on_local_disks, p_db_on_volumes => l_db_on_volumes, p_db_on_asm => l_db_on_asm, p_db_on_local_fs => l_db_on_local_fs, p_db_on_writeable_nfs => l_db_on_writeable_nfs, p_db_allocated => l_db_allocated, p_db_tablespace_size => l_db_tablespace_size, p_db_tablespace_used => l_db_tablespace_used, p_db_used => p_db_used, p_db_free => p_db_free, p_local_fs_on_local_disks => l_local_fs_on_local_disks, p_local_fs_on_volumes => l_local_fs_on_volumes, p_local_fs_used => p_local_fs_used, p_local_fs_free => p_local_fs_free, p_writeable_nfs_used => p_writeable_nfs_used, p_writeable_nfs_free => p_writeable_nfs_free, p_total_allocated => p_total_allocated, p_total_unallocated => p_total_unallocated, p_total_overhead => p_total_overhead, p_total_used => p_total_used, p_total_free => p_total_free ); END calc_metrics; FUNCTION get_metric_results ( p_target_type IN VARCHAR2, p_overhead IN OUT NUMBER, p_used IN OUT NUMBER, p_free IN OUT NUMBER, p_unallocated IN OUT NUMBER, p_allocated IN OUT NUMBER, p_db_used IN OUT NUMBER, p_db_free IN OUT NUMBER, p_local_fs_used IN OUT NUMBER, p_local_fs_free IN OUT NUMBER, p_writeable_nfs_used IN OUT NUMBER, p_writeable_nfs_free IN OUT NUMBER, p_vol_allocated IN OUT NUMBER, p_vol_unallocated IN OUT NUMBER, p_vol_overhead IN OUT NUMBER, p_asm_allocated IN OUT NUMBER, p_asm_unallocated IN OUT NUMBER, p_asm_overhead IN OUT NUMBER, p_disk_allocated IN OUT NUMBER, p_disk_unallocated IN OUT NUMBER, p_total_hosts IN OUT NUMBER, p_summarized_hosts IN OUT NUMBER, p_total_dbs IN OUT NUMBER, p_summarized_dbs IN OUT NUMBER, p_total_asm_targets IN OUT NUMBER, p_summarized_asm_targets IN OUT NUMBER, p_unmonitored_nfs_count IN OUT NUMBER, p_other_mapping_issues IN OUT NUMBER, p_host_metric_errors IN OUT NUMBER, p_db_metric_errors IN OUT NUMBER, p_asm_metric_errors IN OUT NUMBER, p_overhead_col IN VARCHAR2, p_used_col IN VARCHAR2, p_free_col IN VARCHAR2, p_unallocated_col IN VARCHAR2, p_allocated_col IN VARCHAR2, p_db_used_col IN VARCHAR2, p_db_free_col IN VARCHAR2, p_local_fs_used_col IN VARCHAR2, p_local_fs_free_col IN VARCHAR2, p_writeable_nfs_used_col IN VARCHAR2, p_writeable_nfs_free_col IN VARCHAR2, p_vol_allocated_col IN VARCHAR2, p_vol_unallocated_col IN VARCHAR2, p_vol_overhead_col IN VARCHAR2, p_asm_allocated_col IN VARCHAR2, p_asm_unallocated_col IN VARCHAR2, p_asm_overhead_col IN VARCHAR2, p_disk_allocated_col IN VARCHAR2, p_disk_unallocated_col IN VARCHAR2, p_total_hosts_col IN VARCHAR2, p_summarized_hosts_col IN VARCHAR2, p_total_dbs_col IN VARCHAR2, p_summarized_dbs_col IN VARCHAR2, p_total_asm_targets_col IN VARCHAR2, p_summarized_asm_targets_col IN VARCHAR2, p_unmonitored_nfs_count_col IN VARCHAR2, p_other_mapping_issues_col IN VARCHAR2, p_host_metric_errors_col IN VARCHAR2, p_db_metric_errors_col IN VARCHAR2, p_asm_metric_errors_col IN VARCHAR2 ) RETURN mgmt_metric_results IS l_db_exists_flag VARCHAR2(1); l_asm_exists_flag VARCHAR2(1); l_local_fs_exists_flag VARCHAR2(1); l_writeable_nfs_exists_flag VARCHAR2(1); l_vol_exists_flag VARCHAR2(1); l_disk_exists_flag VARCHAR2(1); l_index NUMBER; l_mgmt_metric_results mgmt_metric_results; BEGIN -- convert all storage data into 'GB' units. p_overhead := storage_ui_util_pkg.convert_into_storage_unit( p_overhead, storage_ui_util_pkg.GB ); p_used := storage_ui_util_pkg.convert_into_storage_unit( p_used, storage_ui_util_pkg.GB ); p_free := storage_ui_util_pkg.convert_into_storage_unit( p_free, storage_ui_util_pkg.GB ); p_unallocated := storage_ui_util_pkg.convert_into_storage_unit( p_unallocated, storage_ui_util_pkg.GB ); p_allocated := storage_ui_util_pkg.convert_into_storage_unit( p_allocated, storage_ui_util_pkg.GB ); p_db_used := storage_ui_util_pkg.convert_into_storage_unit( p_db_used, storage_ui_util_pkg.GB); p_db_free := storage_ui_util_pkg.convert_into_storage_unit( p_db_free, storage_ui_util_pkg.GB ); p_local_fs_used := storage_ui_util_pkg.convert_into_storage_unit( p_local_fs_used, storage_ui_util_pkg.GB ); p_local_fs_free := storage_ui_util_pkg.convert_into_storage_unit( p_local_fs_free, storage_ui_util_pkg.GB ); p_writeable_nfs_used := storage_ui_util_pkg.convert_into_storage_unit( p_writeable_nfs_used, storage_ui_util_pkg.GB ); p_writeable_nfs_free := storage_ui_util_pkg.convert_into_storage_unit( p_writeable_nfs_free, storage_ui_util_pkg.GB ); p_vol_allocated := storage_ui_util_pkg.convert_into_storage_unit( p_vol_allocated, storage_ui_util_pkg.GB ); p_vol_unallocated := storage_ui_util_pkg.convert_into_storage_unit( p_vol_unallocated, storage_ui_util_pkg.GB ); p_vol_overhead := storage_ui_util_pkg.convert_into_storage_unit( p_vol_overhead, storage_ui_util_pkg.GB ); p_asm_allocated := storage_ui_util_pkg.convert_into_storage_unit( p_asm_allocated, storage_ui_util_pkg.GB ); p_asm_unallocated := storage_ui_util_pkg.convert_into_storage_unit( p_asm_unallocated, storage_ui_util_pkg.GB ); p_asm_overhead := storage_ui_util_pkg.convert_into_storage_unit( p_asm_overhead, storage_ui_util_pkg.GB ); p_disk_allocated := storage_ui_util_pkg.convert_into_storage_unit( p_disk_allocated , storage_ui_util_pkg.GB ); p_disk_unallocated := storage_ui_util_pkg.convert_into_storage_unit( p_disk_unallocated , storage_ui_util_pkg.GB ); -- get counts indicating existence of -- each storage "layer" select db_exists_flag, asm_exists_flag, local_fs_exists_flag, writeable_nfs_exists_flag, vol_exists_flag, disk_exists_flag into l_db_exists_flag, l_asm_exists_flag, l_local_fs_exists_flag, l_writeable_nfs_exists_flag, l_vol_exists_flag, l_disk_exists_flag from mgmt_v_storage_lyr_existence; l_mgmt_metric_results := mgmt_metric_results(); l_index := 1; -- first summary metrics applicable to all storage layers l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_overhead_col, null, p_overhead ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_used_col, null, p_used ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_free_col, null, p_free ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_allocated_col, null, p_allocated ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_unallocated_col, null, p_unallocated ); -- populate repository metric collection data structure. if( l_db_exists_flag = storage_ui_util_pkg.YES ) then l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_db_used_col, null, p_db_used ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_db_free_col, null, p_db_free ); end if; if( l_asm_exists_flag = storage_ui_util_pkg.YES ) then l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_asm_allocated_col, null, p_asm_allocated ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_asm_unallocated_col, null, p_asm_unallocated ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_asm_overhead_col, null, p_asm_overhead ); end if; if( l_local_fs_exists_flag = storage_ui_util_pkg.YES ) then l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_local_fs_used_col, null, p_local_fs_used ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_local_fs_free_col, null, p_local_fs_free ); end if; if( l_writeable_nfs_exists_flag = storage_ui_util_pkg.YES ) then l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_writeable_nfs_used_col, null, p_writeable_nfs_used ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_writeable_nfs_free_col, null, p_writeable_nfs_free ); end if; if( l_vol_exists_flag = storage_ui_util_pkg.YES ) then l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_vol_allocated_col, null, p_vol_allocated ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_vol_unallocated_col, null, p_vol_unallocated ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_vol_overhead_col, null, p_vol_overhead ); end if; if( l_disk_exists_flag = storage_ui_util_pkg.YES ) then l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_disk_allocated_col, null, p_disk_allocated ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_disk_unallocated_col, null, p_disk_unallocated ); end if; -- put error conditions/ configuration related columns IF ( p_target_type != mgmt_global.G_HOST_TARGET_TYPE ) THEN l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_total_hosts_col, null, p_total_hosts ); END IF; l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_summarized_hosts_col, null, p_summarized_hosts ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_total_dbs_col, null, p_total_dbs ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_summarized_dbs_col, null, p_summarized_dbs ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_total_asm_targets_col, null, p_total_asm_targets ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_summarized_asm_targets_col, null, p_summarized_asm_targets ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_unmonitored_nfs_count_col, null, p_unmonitored_nfs_count ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_other_mapping_issues_col, null, p_other_mapping_issues ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_host_metric_errors_col, null, p_host_metric_errors ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_db_metric_errors_col, null, p_db_metric_errors ); l_index := l_index + 1; l_mgmt_metric_results.extend(1); l_mgmt_metric_results(l_index) := mgmt_metric_result( p_asm_metric_errors_col, null, p_asm_metric_errors ); /* -- printing all metric data dbms_output.put_line('------------------'); dbms_output.put_line('printing all metric data'); FOR I IN 1..l_mgmt_metric_results.count LOOP dbms_output.put_line( l_mgmt_metric_results(i).metric_column || ': ' || l_mgmt_metric_results(i).metric_value ); END LOOP; dbms_output.put_line('------------------'); */ RETURN l_mgmt_metric_results; END; END; / SHOW ERRORS;