Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/storage/storage_ui_common_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/02/18 16:39:48 yozhang Exp $
Rem
Rem storage_ui_common_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem storage_ui_common_pkgbody.sql
Rem
Rem DESCRIPTION
Rem package containing methods to retrieve host or group
Rem storage data.
Rem
Rem NOTES
Rem Only methods that work for both host and group should exist
Rem in this package.
Rem
Rem MODIFIED (MM/DD/YY)
Rem yozhang 02/11/09 - Fix bug 6848759
Rem yozhang 05/04/07 - Fix backport bug: 6029650
Rem mnihalan 08/18/06 - fix backport 5475298
Rem ajere 09/07/05 - Fix bug# 4573302
Rem sreddy 07/23/05 - Modified all queries to MGMT_V_STORAGE_STATS
Rem to include target_type in join conditions
Rem sreddy 07/23/05 - Improve performance by cutting over queries
Rem to use ecm_snapshotId column in
Rem MGMT_STORAGE_REPORT_UI_TARGETS table. Fixes
Rem the performance bug of using
Rem MGMT_STORAGE_REPORT_DATA as driver table
Rem which has large number of rows.
Rem sreddy 07/23/05 - Fixed GET_COLLECTION_TIMESTAMP to return
Rem correct timestamp. It fixes the bug of not taking
Rem ASM collection timestamps into computing it.
Rem sreddy 07/23/05 - Fixed p_total_dbs computation in
Rem GET_STRG_DATA_CFG_AND_ERRORS(). It fixes the
Rem bug of counting errors due to inclusion of
Rem oracle_database instances having valid
Rem rac_database association
Rem sreddy 07/23/05 - Fixed computation of collection_timestamp in
Rem GET_COLLECTION_INFO. Fixes the bug of not taking
Rem ASM collection timestamps into computing it.
Rem sreddy 07/23/05 - perform DELETE_MISSING_SNAPSHOT_HOSTS before
Rem computing summary metrics [fixes the bug of
Rem summarizing usage values for db, asm and rac
Rem targets without having associated host storage
Rem snapshot]
Rem sreddy 07/23/05 - define/use constants, cleanup comments,
Rem cleanup queries, make the code more readable
Rem ajdsouza 07/19/05 - Modified function get_refresh_job_info to query on
Rem job desc
Rem ajere 07/19/05 - Fix bug# 4409361
Rem ajere 07/11/05 - Fix bug# 4222308
Rem ajere 06/15/05 - Fix bug# 4300002
Rem ajere 06/13/05 - Fix bug# 4253174
Rem rmenon 05/02/05 - partial fix for performance bug (4289514).
Rem Some extra code is being executed in the
Rem common method. Also fixed the bug in
Rem calculating local fs and writeable NFS
Rem used storage.
Rem rmenon 04/07/05 - fixed bug 4290570
Rem rmenon 03/18/05 - In the select in the function
Rem get_refresh_job_info, we now use rownum
Rem to restrict the number of rows to 1.
Rem If jobs are normally submitted from the UI,
Rem this is not needed. But due to some reason,
Rem if the query does return more than one row,
Rem it is not an error really, the behavior should
Rem be to just return the first row.
Rem rmenon 03/10/05 - changed db size calculation. it now depends
Rem only on tablespace size (not on control files
Rem size and redo log sizes.
Rem rmenon 03/01/05 - added info conveying if any refresh
Rem job is running against a group or not.
Rem rmenon 02/21/05 -
Rem rmenon 02/16/05 - fix 4186450 (show timezone in UI pages)
Rem rmenon 02/07/05 - fix for 399806 - fixed by looking at tkprof
Rem there were two problems:
Rem a. There was a full scan on MGMT_TARGETS
Rem that I now avoid by adding the target_type
Rem column in my views and using them
Rem in the queries to give mor info to the CBO
Rem for it to be able to use index.
Rem b. There were sorts using physical IO which
Rem can be a bottleneck. If I alter the
Rem sort_area_size and hash_area_size then
Rem these disappear. If this problem occurs
Rem again, we can at the minimum change these
Rem settings at a session level perhaps.
Rem Currently, I am ignoring these issues.
Rem Formatted code to conform to EM coding
Rem standards.
Rem rmenon 01/31/05 -
Rem rmenon 01/05/05 - fixed query that gets hosts in a group
Rem that don't have collection defined or
Rem jhave missing storage data.
Rem rmenon 12/09/04 - history page UI changes based on UI
Rem exit review - mainly to show the congiuration
Rem and errors encountered while history
Rem was calculated.
Rem
Rem rmenon 11/15/04 - split collection error count into
Rem mapping error count and collection error
Rem count; formatted procedure definitions
Rem aligning the beginning and ending brackets.
Rem rmenon 11/11/04 - correcting queries from MGMT_METRICS
Rem to remove "met.metric_column = ' '"
Rem condition.added API for calculating
Rem error and config change history for
Rem host and group storage history data.
Rem rmenon 10/27/04 - corrected queries from MGMT_METRICS
Rem to include type_meta_ver and
Rem category properties conditions.
Rem rmenon 10/23/04 - vendor distribution; code refactor
Rem rmenon 10/09/04 - rmenon_storage_group_ui_and_sql_code_refactor
Rem rmenon 09/28/04 - Created
Rem
CREATE OR REPLACE PACKAGE BODY STORAGE_UI_COMMON_PKG AS
G_GROUP_JOB_DESC_STRG CONSTANT VARCHAR2(50) := 'Refreshing group storage for group: ';
G_NO_REFRESH_JOB_IS_RUNNING CONSTANT NUMBER := -1;
G_MODULE_NAME CONSTANT VARCHAR2(30) := 'STORAGE_UI_COMMON_PKG';
-- Note that following is not a constant
gv_storage_rep_metric VARCHAR2(50) := STORAGE_REP_METRICS_PKG.G_HOST_STORAGE_REP_METRIC;
-- Forward declarations of private procedures/functions
FUNCTION IS_HISTORY_METRIC_DEFINED(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE PRINT_DASHED_LINE;
PROCEDURE PRINT_STORAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_vol_size IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_disk_size IN NUMBER,
p_asm_size IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_db_on_local_disks IN NUMBER,
p_db_on_volumes IN NUMBER,
p_db_on_asm IN NUMBER,
p_db_on_local_fs IN NUMBER,
p_db_on_writeable_nfs IN NUMBER,
p_db_allocated IN NUMBER,
p_db_tablespace_size IN NUMBER,
p_db_tablespace_used IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_on_local_disks IN NUMBER,
p_local_fs_on_volumes IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_total_allocated IN NUMBER,
p_total_unallocated IN NUMBER,
p_total_overhead IN NUMBER,
p_total_used IN NUMBER,
p_total_free IN NUMBER);
PROCEDURE LOG_STORAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_vol_size IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_disk_size IN NUMBER,
p_asm_size IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_db_on_local_disks IN NUMBER,
p_db_on_volumes IN NUMBER,
p_db_on_asm IN NUMBER,
p_db_on_local_fs IN NUMBER,
p_db_on_writeable_nfs IN NUMBER,
p_db_allocated IN NUMBER,
p_db_tablespace_size IN NUMBER,
p_db_tablespace_used IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_on_local_disks IN NUMBER,
p_local_fs_on_volumes IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_total_allocated IN NUMBER,
p_total_unallocated IN NUMBER,
p_total_overhead IN NUMBER,
p_total_used IN NUMBER,
p_total_free IN NUMBER);
PROCEDURE LOG_DASHED_LINE;
PROCEDURE SET_HIST_DATA_EXISTS_FLAG(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_hist_data_exists_flag OUT VARCHAR2);
PROCEDURE DO_INS_TARGETS_FOR_UI_QUERIES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2);
PROCEDURE GET_LOCAL_FS_NFS_USED_FREE(p_local_fs_used OUT NUMBER,
p_local_fs_free OUT NUMBER,
p_writeable_nfs_used OUT NUMBER,
p_writeable_nfs_free OUT NUMBER);
PROCEDURE GET_VOL_DISK_USED_BY_LAYER(p_storage_layer IN VARCHAR2,
p_vol_allocated_by_layer OUT NUMBER,
p_disk_allocated_by_layer OUT NUMBER);
PROCEDURE GET_DB_DATA_FILES_SOURCE(p_db_df_on_local_fs OUT NUMBER,
p_db_df_on_nfs OUT NUMBER,
p_db_df_on_volumes OUT NUMBER,
p_db_df_on_disks OUT NUMBER);
PROCEDURE GET_DB_TABLESPACE_SIZE_USED(p_tablespace_size OUT NUMBER,
p_tablespace_used OUT NUMBER);
PROCEDURE GET_DB_ALLOC_USED_FREE(p_db_allocated OUT NUMBER,
p_db_used OUT NUMBER,
p_db_free OUT NUMBER);
PROCEDURE GET_DB_STORAGE_SOURCE(p_db_on_local_disks OUT NUMBER,
p_db_on_volumes OUT NUMBER,
p_db_on_asm OUT NUMBER,
p_db_on_local_fs OUT NUMBER,
p_db_on_writeable_nfs OUT NUMBER);
PROCEDURE GET_ALLOCATION_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_vol_allocated OUT NUMBER,
p_vol_overhead OUT NUMBER,
p_vol_unallocated OUT NUMBER,
p_vol_size OUT NUMBER,
p_asm_allocated OUT NUMBER,
p_asm_unallocated OUT NUMBER,
p_asm_overhead OUT NUMBER,
p_disk_allocated OUT NUMBER,
p_disk_unallocated OUT NUMBER,
p_disk_size OUT NUMBER,
p_asm_size OUT NUMBER,
p_writeable_nfs_allocated OUT NUMBER);
PROCEDURE GET_USAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_calc_storage_source_flag IN NUMBER,
p_db_on_local_disks OUT NUMBER,
p_db_on_volumes OUT NUMBER,
p_db_on_asm OUT NUMBER,
p_db_on_local_fs OUT NUMBER,
p_db_on_writeable_nfs OUT NUMBER,
p_db_allocated OUT NUMBER,
p_db_tablespace_size OUT NUMBER,
p_db_tablespace_used OUT NUMBER,
p_db_used OUT NUMBER,
p_db_free OUT NUMBER,
p_local_fs_on_local_disks OUT NUMBER,
p_local_fs_on_volumes OUT NUMBER,
p_local_fs_used OUT NUMBER,
p_local_fs_free OUT NUMBER,
p_writeable_nfs_used OUT NUMBER,
p_writeable_nfs_free OUT NUMBER);
FUNCTION GET_USAGE_SUMMARY_AS_CURSOR(p_db_on_local_disks IN NUMBER,
p_db_on_volumes IN NUMBER,
p_db_on_asm IN NUMBER,
p_db_on_local_fs IN NUMBER,
p_db_on_writeable_nfs IN NUMBER,
p_db_allocated IN NUMBER,
p_db_tablespace_size IN NUMBER,
p_db_tablespace_used IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_on_local_disks IN NUMBER,
p_local_fs_on_volumes IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_UTIL_SUMMARY_VALUES(p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_total_allocated OUT NUMBER,
p_total_unallocated OUT NUMBER,
p_total_overhead OUT NUMBER,
p_total_used OUT NUMBER,
p_total_free OUT NUMBER);
FUNCTION GET_UTIL_SUMMARY_AS_CURSOR(p_total_unallocated IN NUMBER,
p_total_overhead IN NUMBER,
p_total_used IN NUMBER,
p_total_free IN NUMBER,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_ASM_ALLOCATED_TO_DBS(p_asm_allocated OUT NUMBER);
PROCEDURE GET_ASM_ALLOC_UNALLOC_OVERHEAD(p_asm_allocated OUT NUMBER,
p_asm_unallocated OUT NUMBER,
p_asm_overhead OUT NUMBER);
PROCEDURE GET_VOL_DISK_NFS_SIZE(p_disk_size OUT NUMBER,
p_vol_size OUT NUMBER,
p_writeable_nfs_allocated OUT NUMBER);
PROCEDURE GET_VOL_DISK_UNALLOCATED(p_disk_unallocated OUT NUMBER,
p_vol_unallocated OUT NUMBER);
FUNCTION GET_ALLOC_SUMMARY_AS_CURSOR(p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_vol_size IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_asm_size IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_disk_size IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
FUNCTION GET_COLLECTION_INFO(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN SYS_REFCURSOR;
FUNCTION GET_STORAGE_UNIT(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN VARCHAR2;
--
-- History related methods
--
PROCEDURE SET_GLOBAL_METRIC_NAME(p_target_type IN VARCHAR2);
PROCEDURE SET_HIST_QUERY_ENV(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN INTEGER);
PROCEDURE GET_MIN_MAX_DATES(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
PROCEDURE GET_MGMT_LYR_TIMESRS_AS_CURSOR(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR);
PROCEDURE GET_DISK_TIMESERIES_AS_CURSOR(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR);
PROCEDURE GET_APP_TIMESERIES_AS_CURSOR(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR);
PROCEDURE GET_SUMM_TIMESERIES_AS_CURSOR(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR);
FUNCTION GET_PERIOD_IN_DAYS(p_period IN VARCHAR2) RETURN NUMBER;
FUNCTION GET_HIST_COLLECTION_TIMESTAMP(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN DATE;
FUNCTION GET_HISTORY_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_HISTORY_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
FUNCTION GET_HISTORY_DB_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_HISTORY_DB_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
FUNCTION GET_HISTORY_LOCALFS_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_HISTORY_LOCALFS_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
FUNCTION GET_HISTORY_DISK_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_HISTORY_DISK_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
FUNCTION GET_HISTORY_ASM_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_HISTORY_ASM_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
FUNCTION GET_HISTORY_VOL_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_HISTORY_VOL_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
FUNCTION GET_HISTORY_NFS_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR;
PROCEDURE GET_HISTORY_NFS_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE);
FUNCTION GET_STORAGE_COLLECTION_ERRORS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN SYS_REFCURSOR;
FUNCTION GET_STORAGE_MAPPING_ISSUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN SYS_REFCURSOR;
FUNCTION GET_SUMMARIZED_ASM_COUNT(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN NUMBER;
PROCEDURE GET_STORAGE_ISSUES_COUNT(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_unmonitored_nfs_count OUT NUMBER,
p_other_issues_count OUT NUMBER);
PROCEDURE GET_HOST_DB_ASM_MET_ERRORS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_host_metric_errors OUT NUMBER,
p_db_metric_errors OUT NUMBER,
p_asm_metric_errors OUT NUMBER);
----------------------------------------------------------------------
--
-- Gets data to render the summary section of UI above the page
-- properties in host storage UI pages.
--
PROCEDURE GET_STORAGE_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_info OUT SYS_REFCURSOR,
p_allocation_summary OUT SYS_REFCURSOR,
p_usage_summary OUT SYS_REFCURSOR,
p_utilization_summary OUT SYS_REFCURSOR,
p_num_of_targets OUT NUMBER,
p_targets_having_snapshot OUT NUMBER,
p_targets_with_coll_defined OUT NUMBER,
p_targets_with_coll_enabled OUT NUMBER,
p_hist_data_exists_flag OUT VARCHAR2,
p_storage_unit OUT VARCHAR2,
p_history_metric_defined_flag OUT VARCHAR2) IS
-- Allocation summary variables
l_vol_allocated NUMBER;
l_vol_overhead NUMBER;
l_asm_allocated NUMBER;
l_disk_allocated NUMBER;
l_vol_unallocated NUMBER;
l_asm_unallocated NUMBER;
l_asm_overhead NUMBER;
l_disk_unallocated NUMBER;
l_disk_size NUMBER;
l_asm_size NUMBER;
l_writeable_nfs_allocated NUMBER;
l_vol_size NUMBER;
-- Usage summary variables
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_db_used NUMBER;
l_db_free NUMBER;
l_local_fs_on_local_disks NUMBER;
l_local_fs_on_volumes NUMBER;
l_local_fs_used NUMBER;
l_local_fs_free NUMBER;
l_writeable_nfs_used NUMBER;
l_writeable_nfs_free NUMBER;
-- Utilization summary variables
l_total_allocated NUMBER;
l_total_unallocated NUMBER;
l_total_overhead NUMBER;
l_total_used NUMBER;
l_total_free NUMBER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
INSERT_TARGETS_FOR_UI_QUERIES(p_target_name, p_target_type);
GET_STORAGE_DATA_AVAIL_INFO(p_target_name => p_target_name,
p_target_type => p_target_type,
p_num_of_targets => p_num_of_targets,
p_targets_having_snapshot => p_targets_having_snapshot,
p_targets_with_coll_defined => p_targets_with_coll_defined,
p_targets_with_coll_enabled => p_targets_with_coll_enabled);
p_collection_info := GET_COLLECTION_INFO(p_target_name => p_target_name,
p_target_type => p_target_type);
SET_HIST_DATA_EXISTS_FLAG(p_target_name => p_target_name,
p_target_type => p_target_type,
p_hist_data_exists_flag => p_hist_data_exists_flag);
p_storage_unit := GET_STORAGE_UNIT(p_target_name => p_target_name,
p_target_type => p_target_type);
DELETE_MISSING_SNAPSHOT_HOSTS;
GET_STORAGE_SUMMARY_VALUES(p_target_name => p_target_name,
p_target_type => p_target_type,
p_calc_storage_source_flag => MGMT_GLOBAL.G_TRUE,
p_vol_allocated => l_vol_allocated,
p_vol_overhead => l_vol_overhead,
p_vol_unallocated => l_vol_unallocated,
p_vol_size => l_vol_size,
p_asm_allocated => l_asm_allocated,
p_asm_unallocated => l_asm_unallocated,
p_asm_overhead => l_asm_overhead,
p_asm_size => l_asm_size,
p_disk_allocated => l_disk_allocated,
p_disk_unallocated => l_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 => l_db_used,
p_db_free => l_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 => 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_total_allocated => l_total_allocated,
p_total_unallocated => l_total_unallocated,
p_total_overhead => l_total_overhead,
p_total_used => l_total_used,
p_total_free => l_total_free);
p_allocation_summary := GET_ALLOC_SUMMARY_AS_CURSOR(
p_vol_allocated => l_vol_allocated,
p_vol_overhead => l_vol_overhead,
p_vol_unallocated => l_vol_unallocated,
p_vol_size => l_vol_size,
p_asm_allocated => l_asm_allocated,
p_asm_unallocated => l_asm_unallocated,
p_asm_overhead => l_asm_overhead,
p_asm_size => l_asm_size,
p_disk_allocated => l_disk_allocated,
p_disk_unallocated => l_disk_unallocated,
p_disk_size => l_disk_size,
p_writeable_nfs_allocated => l_writeable_nfs_allocated,
p_storage_unit => p_storage_unit);
p_usage_summary := GET_USAGE_SUMMARY_AS_CURSOR(
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 => l_db_used,
p_db_free => l_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 => 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_storage_unit => p_storage_unit);
p_utilization_summary := GET_UTIL_SUMMARY_AS_CURSOR(
p_total_unallocated => l_total_unallocated,
p_total_overhead => l_total_overhead,
p_total_used => l_total_used,
p_total_free => l_total_free,
p_storage_unit => p_storage_unit);
p_history_metric_defined_flag := IS_HISTORY_METRIC_DEFINED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_SUMMARY;
----------------------------------------------------------------------
--
-- Inserts target names into a temporary table MGMT_STORAGE_REPORT_UI_TARGETS.
-- In case of host, inserts the host itself.
-- In case of group, inserts associated host list for the group.
-- This approach allows common code for both host and group targets.
--
PROCEDURE INSERT_TARGETS_FOR_UI_QUERIES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) IS
BEGIN
-- first delete data from temporary tables.
-- this table could have stale data from previous
-- session due to session pooling.
DELETE MGMT_STORAGE_REPORT_UI_TARGETS;
DO_INS_TARGETS_FOR_UI_QUERIES(p_target_name => p_target_name,
p_target_type => p_target_type);
END INSERT_TARGETS_FOR_UI_QUERIES;
----------------------------------------------------------------------
PROCEDURE INSERT_TARGETS_FOR_UI_QUERIES(p_target_guid IN RAW) IS
l_target_type MGMT_TARGETS.target_type%TYPE;
l_target_name MGMT_TARGETS.target_name%TYPE;
BEGIN
GET_TARGET_NAME_TYPE(p_target_guid => p_target_guid,
p_target_name => l_target_name,
p_target_type => l_target_type);
INSERT_TARGETS_FOR_UI_QUERIES(p_target_name => l_target_name,
p_target_type => l_target_type);
END INSERT_TARGETS_FOR_UI_QUERIES;
----------------------------------------------------------------------
--
-- Deletes hosts from MGMT_STORAGE_REPORT_UI_TARGETS table
-- which do not have valid host_storage snapshot.
--
PROCEDURE DELETE_MISSING_SNAPSHOT_HOSTS IS
BEGIN
-- delete hosts with missing storage snapshots from the temporary table
DELETE FROM MGMT_STORAGE_REPORT_UI_TARGETS
WHERE target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND ecm_snapshot_id IS NULL;
END DELETE_MISSING_SNAPSHOT_HOSTS;
----------------------------------------------------------------------
--
-- Gets target name and type, given target guid of a target.
--
PROCEDURE GET_TARGET_NAME_TYPE(p_target_guid IN RAW,
p_target_name OUT VARCHAR2,
p_target_type OUT VARCHAR2) IS
BEGIN
SELECT target_name, target_type
INTO p_target_name, p_target_type
FROM MGMT_TARGETS
WHERE target_guid = p_target_guid;
END GET_TARGET_NAME_TYPE;
----------------------------------------------------------------------
--
-- Gets storage history data to render the history summary page.
--
PROCEDURE GET_STORAGE_HIST_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_history_summary OUT SYS_REFCURSOR,
p_history_stats OUT SYS_REFCURSOR,
p_storage_unit OUT VARCHAR2,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE,
p_storage_hist_enabled_flag OUT VARCHAR2) IS
l_period_in_days INTEGER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
SET_GLOBAL_METRIC_NAME(p_target_type);
l_period_in_days := GET_PERIOD_IN_DAYS(p_period);
SET_HIST_QUERY_ENV(p_target_name, p_target_type, l_period_in_days);
p_collection_timestamp := GET_HIST_COLLECTION_TIMESTAMP(
p_target_name, p_target_type);
p_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
p_history_stats := GET_HISTORY_STATS(p_target_name, p_target_type, p_storage_unit);
GET_HISTORY_SUMMARY(p_target_name => p_target_name,
p_target_type => p_target_type,
p_period_in_days => l_period_in_days,
p_storage_unit => p_storage_unit,
p_summary_cursor => p_history_summary,
p_min_timeseries_date => p_min_timeseries_date,
p_max_timeseries_date => p_max_timeseries_date);
p_storage_hist_enabled_flag := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_HIST_SUMMARY;
----------------------------------------------------------------------
--
-- Gets storage history data to render the storage history of
-- databases on a host.
--
PROCEDURE GET_STORAGE_HIST_DB(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_history_summary OUT SYS_REFCURSOR,
p_history_stats OUT SYS_REFCURSOR,
p_storage_unit OUT VARCHAR2,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE,
p_storage_hist_enabled_flag OUT VARCHAR2) IS
l_period_in_days INTEGER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
SET_GLOBAL_METRIC_NAME(p_target_type);
l_period_in_days := GET_PERIOD_IN_DAYS(p_period);
SET_HIST_QUERY_ENV(p_target_name, p_target_type, l_period_in_days);
p_collection_timestamp := GET_HIST_COLLECTION_TIMESTAMP(
p_target_name, p_target_type);
p_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
p_history_stats := GET_HISTORY_DB_STATS(p_target_name,
p_target_type,
p_storage_unit);
GET_HISTORY_DB_SUMMARY(p_target_name, p_target_type, l_period_in_days,
p_storage_unit, p_history_summary,
p_min_timeseries_date, p_max_timeseries_date);
p_storage_hist_enabled_flag := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_HIST_DB;
----------------------------------------------------------------------
--
-- Gets storage history data to render the storage history of
-- local file systems on a host.
--
PROCEDURE GET_STORAGE_HIST_LOCALFS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_history_summary OUT SYS_REFCURSOR,
p_history_stats OUT SYS_REFCURSOR,
p_storage_unit OUT VARCHAR2,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE,
p_storage_hist_enabled_flag OUT VARCHAR2) IS
l_period_in_days INTEGER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
SET_GLOBAL_METRIC_NAME(p_target_type);
l_period_in_days := GET_PERIOD_IN_DAYS(p_period);
SET_HIST_QUERY_ENV(p_target_name, p_target_type, l_period_in_days);
p_collection_timestamp := GET_HIST_COLLECTION_TIMESTAMP(
p_target_name, p_target_type);
p_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
p_history_stats := GET_HISTORY_LOCALFS_STATS(p_target_name,
p_target_type,
p_storage_unit);
GET_HISTORY_LOCALFS_SUMMARY(p_target_name, p_target_type, l_period_in_days,
p_storage_unit, p_history_summary,
p_min_timeseries_date, p_max_timeseries_date);
p_storage_hist_enabled_flag := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_HIST_LOCALFS;
----------------------------------------------------------------------
--
-- Gets storage history data to render the storage history of NFS on a host.
--
PROCEDURE GET_STORAGE_HIST_NFS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_history_summary OUT SYS_REFCURSOR,
p_history_stats OUT SYS_REFCURSOR,
p_storage_unit OUT VARCHAR2,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE,
p_storage_hist_enabled_flag OUT VARCHAR2) IS
l_period_in_days INTEGER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
SET_GLOBAL_METRIC_NAME(p_target_type);
l_period_in_days := GET_PERIOD_IN_DAYS(p_period);
SET_HIST_QUERY_ENV(p_target_name, p_target_type, l_period_in_days);
p_collection_timestamp := GET_HIST_COLLECTION_TIMESTAMP(
p_target_name, p_target_type);
p_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
p_history_stats := GET_HISTORY_NFS_STATS(p_target_name,
p_target_type,
p_storage_unit);
GET_HISTORY_NFS_SUMMARY(p_target_name, p_target_type, l_period_in_days,
p_storage_unit, p_history_summary,
p_min_timeseries_date, p_max_timeseries_date);
p_storage_hist_enabled_flag := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_HIST_NFS;
----------------------------------------------------------------------
--
-- Gets storage history data to render the storage history of
-- local disks on a host.
--
PROCEDURE GET_STORAGE_HIST_DISKS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_history_summary OUT SYS_REFCURSOR,
p_history_stats OUT SYS_REFCURSOR,
p_storage_unit OUT VARCHAR2,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE,
p_storage_hist_enabled_flag OUT VARCHAR2) IS
l_period_in_days INTEGER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
SET_GLOBAL_METRIC_NAME(p_target_type);
l_period_in_days := GET_PERIOD_IN_DAYS(p_period);
SET_HIST_QUERY_ENV(p_target_name, p_target_type, l_period_in_days);
p_collection_timestamp := GET_HIST_COLLECTION_TIMESTAMP(
p_target_name, p_target_type);
p_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
p_history_stats := GET_HISTORY_DISK_STATS(p_target_name,
p_target_type,
p_storage_unit);
GET_HISTORY_DISK_SUMMARY(p_target_name, p_target_type, l_period_in_days,
p_storage_unit, p_history_summary,
p_min_timeseries_date, p_max_timeseries_date);
p_storage_hist_enabled_flag := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_HIST_DISKS;
----------------------------------------------------------------------
--
-- Gets storage history data to render the storage history of asm on a host.
--
PROCEDURE GET_STORAGE_HIST_ASM(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_history_summary OUT SYS_REFCURSOR,
p_history_stats OUT SYS_REFCURSOR,
p_storage_unit OUT VARCHAR2,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE,
p_storage_hist_enabled_flag OUT VARCHAR2) IS
l_period_in_days INTEGER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
SET_GLOBAL_METRIC_NAME(p_target_type);
l_period_in_days := GET_PERIOD_IN_DAYS(p_period);
SET_HIST_QUERY_ENV(p_target_name, p_target_type, l_period_in_days);
p_collection_timestamp := GET_HIST_COLLECTION_TIMESTAMP(
p_target_name, p_target_type);
p_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
p_history_stats := GET_HISTORY_ASM_STATS(p_target_name,
p_target_type,
p_storage_unit);
GET_HISTORY_ASM_SUMMARY(p_target_name, p_target_type, l_period_in_days,
p_storage_unit, p_history_summary,
p_min_timeseries_date, p_max_timeseries_date);
p_storage_hist_enabled_flag := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_HIST_ASM;
----------------------------------------------------------------------
--
-- Gets storage history data to render the storage history of volumes on a host.
--
PROCEDURE GET_STORAGE_HIST_VOL(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_history_summary OUT SYS_REFCURSOR,
p_history_stats OUT SYS_REFCURSOR,
p_storage_unit OUT VARCHAR2,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE,
p_storage_hist_enabled_flag OUT VARCHAR2) IS
l_period_in_days INTEGER;
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
SET_GLOBAL_METRIC_NAME(p_target_type);
l_period_in_days := GET_PERIOD_IN_DAYS(p_period);
SET_HIST_QUERY_ENV(p_target_name, p_target_type, l_period_in_days);
p_collection_timestamp := GET_HIST_COLLECTION_TIMESTAMP(
p_target_name, p_target_type);
p_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
p_history_stats := GET_HISTORY_VOL_STATS(p_target_name,
p_target_type,
p_storage_unit);
GET_HISTORY_VOL_SUMMARY(p_target_name, p_target_type, l_period_in_days,
p_storage_unit, p_history_summary,
p_min_timeseries_date, p_max_timeseries_date);
p_storage_hist_enabled_flag := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_HIST_VOL;
----------------------------------------------------------------------
--
-- Runs storage history repository collection for host or group.
--
PROCEDURE RUN_HIST_COLLECTION(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) IS
l_metric_name MGMT_METRICS.metric_name%TYPE;
BEGIN
INSERT_TARGETS_FOR_UI_QUERIES(p_target_name, p_target_type);
SET_HIST_QUERY_ENV(p_target_name => p_target_name,
p_target_type => p_target_type,
p_period_in_days => STORAGE_UI_COMMON_PKG.G_DAYS_PER_MONTH);
SET_GLOBAL_METRIC_NAME(p_target_type => p_target_type);
MGMT_COLLECTION.RUN_COLLECTION(p_target_type => p_target_type,
p_target_name => p_target_name,
p_metric_name => gv_storage_rep_metric);
END RUN_HIST_COLLECTION;
----------------------------------------------------------------------
--
-- This PROCEDURE returns 'Y' if storage history for a group
-- is enabled, 'N' otherwise. If the target type is host,
-- it always returns 'Y'.
--
FUNCTION GET_STORAGE_HIST_ENABLED(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN VARCHAR2 IS
l_storage_hist_enabled VARCHAR2(1);
BEGIN
IF (p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
l_storage_hist_enabled :=STORAGE_UI_UTIL_PKG.YES;
ELSE
SELECT DECODE(count(*), 0,STORAGE_UI_UTIL_PKG.NO,STORAGE_UI_UTIL_PKG.YES)
INTO l_storage_hist_enabled
FROM MGMT_TARGET_PROPERTIES tp,
MGMT_TARGETS t
WHERE t.target_guid = tp.target_guid
AND tp.property_name = G_GRP_STORAGE_HIST_ENABLED
AND property_type = MGMT_GLOBAL.G_INSTANCE_PROP_TYPE
AND target_name = p_target_name
AND target_type = p_target_type
AND property_value =STORAGE_UI_UTIL_PKG.YES;
END IF;
RETURN l_storage_hist_enabled;
END GET_STORAGE_HIST_ENABLED;
----------------------------------------------------------------------
--
-- Gets storage vendor disribution for a host or a group
-- based on writeable NFS and disks information.
--
PROCEDURE GET_STORAGE_VENDOR_DISTRIB(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_vendor_distrib OUT SYS_REFCURSOR) IS
l_storage_unit VARCHAR2(2);
BEGIN
INSERT_TARGETS_FOR_UI_QUERIES(p_target_name, p_target_type);
l_storage_unit := GET_STORAGE_UNIT(p_target_name, p_target_type);
OPEN p_storage_vendor_distrib FOR
SELECT vendor,
storage_layer,
round(SUM(sizeb)/STORAGE_UI_UTIL_PKG.BYTES_PER_GB,2) host_visible
FROM (
SELECT vendor, storage_layer, sizeb,
ROW_NUMBER() OVER (PARTITION BY storage_layer,
global_unique_id
ORDER BY ecm_snapshot_id) AS
global_unique_id_rownumber
FROM MGMT_V_VENDOR_DISTRIBUTION
)
WHERE global_unique_id_rownumber = 1
GROUP BY vendor, storage_layer;
END GET_STORAGE_VENDOR_DISTRIB;
----------------------------------------------------------------------
--
-- Returns information on storage data availability.
-- For a host, it returns
-- p_num_of_targets => num of targets, in this case 1.
-- p_targets_having_snapshot => 1 if host has storage snapshot, 0 otherwise.
-- p_targets_with_coll_defined => 1 if host has storage collection defined,
-- 0 otherwise.
-- p_targets_with_coll_enabled => 1 if host has storage collection enabled,
-- 0 otherwise.
-- For a group, it returns
-- p_num_of_targets => # of associated host targets for the group.
-- p_targets_having_snapshot => # of associated hosts having storage snapshot
-- p_targets_with_coll_defined => # of associated hosts with storage snapshot
-- collection defined
-- p_targets_with_coll_enabled => # of associated hosts with storage snapshot
-- collection enabled
--
PROCEDURE GET_STORAGE_DATA_AVAIL_INFO(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_num_of_targets OUT NUMBER,
p_targets_having_snapshot OUT NUMBER,
p_targets_with_coll_defined OUT NUMBER,
p_targets_with_coll_enabled OUT NUMBER) IS
BEGIN
SELECT NVL(SUM(is_enabled), 0) targets_with_coll_enabled,
COUNT(*) targets_with_coll_defined
INTO p_targets_with_coll_enabled,
p_targets_with_coll_defined
FROM MGMT_COLLECTIONS c,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.target_guid = c.object_guid
AND c.coll_name = G_HOST_STORAGE_COLL_NAME;
SELECT COUNT(*)
INTO p_num_of_targets
FROM MGMT_STORAGE_REPORT_UI_TARGETS
WHERE target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE;
SELECT COUNT(*)
INTO p_targets_having_snapshot
FROM MGMT_STORAGE_REPORT_UI_TARGETS
WHERE ecm_snapshot_id IS NOT NULL;
END GET_STORAGE_DATA_AVAIL_INFO;
----------------------------------------------------------------------
--
-- Gets latest collection timestamp of all the relevant snapshots of given
-- target.
--
FUNCTION GET_COLLECTION_TIMESTAMP(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN DATE IS
l_collection_timestamp MGMT$ECM_CURRENT_SNAPSHOTS.saved_timestamp%TYPE;
BEGIN
SELECT MAX(timestamp)
INTO l_collection_timestamp
FROM (
SELECT MAX(snap.saved_timestamp) timestamp
FROM MGMT$ECM_CURRENT_SNAPSHOTS snap,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND uit.target_name = snap.host_name
AND snap.snapshot_type IN (G_DB_CONFIG_COLL_NAME,
G_RAC_CONFIG_COLL_NAME,
G_HOST_STORAGE_COLL_NAME)
UNION ALL
SELECT MAX(collection_timestamp) timestamp
FROM MGMT_CURRENT_METRICS cm,
MGMT_TARGETS t,
MGMT_METRICS m,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.target_name = t.host_name
AND t.target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND t.target_guid = cm.target_guid
AND cm.metric_guid = m.metric_guid
AND t.type_meta_ver = m.type_meta_ver
AND (m.category_prop_1 = ' ' OR
m.category_prop_1 = t.category_prop_1)
AND (m.category_prop_2 = ' ' OR
m.category_prop_2 = t.category_prop_2)
AND (m.category_prop_3 = ' ' OR
m.category_prop_3 = t.category_prop_3)
AND (m.category_prop_4 = ' ' OR
m.category_prop_4 = t.category_prop_4)
AND (m.category_prop_5 = ' ' OR
m.category_prop_5 = t.category_prop_5)
AND m.metric_name in (STORAGE_UI_COMMON_PKG.G_ASM_DG_USAGE_METRIC,
STORAGE_UI_COMMON_PKG.G_ASM_DB_DG_METRIC)
);
RETURN l_collection_timestamp;
END GET_COLLECTION_TIMESTAMP;
----------------------------------------------------------------------
--
-- Gets the following types of errors for a host or group
-- 1. any storage metric collection errors
-- 2. any storage metric mapping issues
--
PROCEDURE GET_STORAGE_ERRORS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_target_timezone_region OUT VARCHAR2,
p_collection_timestamp OUT DATE,
p_storage_collection_errors OUT SYS_REFCURSOR,
p_storage_mapping_issues OUT SYS_REFCURSOR,
p_targets_with_missing_snaps OUT SYS_REFCURSOR,
p_targets_with_no_collection OUT SYS_REFCURSOR,
p_targets_with_coll_disabled OUT SYS_REFCURSOR) IS
BEGIN
p_target_timezone_region := GET_TARGET_TIMEZONE_REGION(
p_target_name => p_target_name,
p_target_type => p_target_type);
INSERT_TARGETS_FOR_UI_QUERIES(p_target_name => p_target_name,
p_target_type => p_target_type);
OPEN p_targets_with_no_collection FOR
SELECT target_name
FROM MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE NOT EXISTS
(
SELECT c.object_guid
FROM MGMT_COLLECTIONS c
WHERE c.coll_name = G_HOST_STORAGE_COLL_NAME
AND c.object_guid = uit.target_guid
)
AND target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE;
OPEN p_targets_with_coll_disabled FOR
SELECT target_name
FROM MGMT_STORAGE_REPORT_UI_TARGETS uit,
MGMT_COLLECTIONS c
WHERE uit.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND uit.target_guid = c.object_guid
AND c.coll_name = G_HOST_STORAGE_COLL_NAME
AND c.is_enabled = MGMT_GLOBAL.G_FALSE;
OPEN p_targets_with_missing_snaps FOR
SELECT target_name
FROM MGMT_STORAGE_REPORT_UI_TARGETS
WHERE target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND ecm_snapshot_id IS NULL;
p_collection_timestamp := GET_COLLECTION_TIMESTAMP(
p_target_name => p_target_name,
p_target_type => p_target_type);
p_storage_collection_errors := GET_STORAGE_COLLECTION_ERRORS(
p_target_name => p_target_name,
p_target_type => p_target_type);
p_storage_mapping_issues := GET_STORAGE_MAPPING_ISSUES(
p_target_name => p_target_name,
p_target_type => p_target_type);
END GET_STORAGE_ERRORS;
----------------------------------------------------------------------
--
-- Gets stats related to configuration of the target. Stats are as follows.
-- . total database targets deployed across summarized hosts
-- . number of database targets with storage data across summarized hosts
-- . total ASM targets deployed across summarized hosts
-- . number of ASM targets with storage data across summarized hosts
-- . Total number of mapping issues related to unmonitored NFS targets
-- . Total number of mapping issues other than the unmonitored NFS targets
-- . Total storage metric collection errors for host(s).
-- . Total storage metric collection errors for associated database target(s)
-- having valid associated host storage snapshots.
-- . Total storage metric collection errors for associated ASM target(s).
-- having valid associated host storage snapshots.
--
PROCEDURE GET_STRG_DATA_CFG_AND_ERRORS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_total_dbs OUT NUMBER,
p_summarized_dbs OUT NUMBER,
p_total_asm_count OUT NUMBER,
p_summarized_asm_count OUT NUMBER,
p_host_metric_errors OUT NUMBER,
p_db_metric_errors OUT NUMBER,
p_asm_metric_errors OUT NUMBER,
p_unmonitored_nfs_count OUT NUMBER,
p_other_issues_count OUT NUMBER) IS
BEGIN
--
-- Compute total number of rac_db and non_rac_db (stand alone db) targets
-- having associated host storage snapshot as p_total_dbs. DB Targets
-- not having valid host storage snapshot are excluded from all computations.
--
SELECT non_rac_db_count+rac_db_count
INTO p_total_dbs
FROM (
SELECT COUNT(*) non_rac_db_count
FROM MGMT_TARGETS t,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id IS NOT NULL
AND uit.target_name = t.host_name
AND t.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE
AND NOT EXISTS
(
SELECT member_target_guid
FROM MGMT_TARGET_MEMBERSHIPS
WHERE member_target_guid = t.target_guid
AND composite_target_type = MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE
)
),
(
SELECT COUNT(*) rac_db_count
FROM MGMT_TARGETS t,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id IS NOT NULL
AND uit.target_name = t.host_name
AND t.target_type = MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE
);
--
-- Compute number of ASM targets having associated host storage snapshot
-- as p_total_asm_count. ASM targets not having valid host storage snapshot
-- are excluded from all computations.
--
SELECT COUNT(*)
INTO p_total_asm_count
FROM MGMT_TARGETS t,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id IS NOT NULL
AND t.host_name = uit.target_name
AND t.target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE;
--
-- Compute number of db/rac targets with appropriate snapshot as
-- p_summarized_dbs. It will be less than p_total_dbs when expected
-- oracle_dbconfig or oracle_racconfig snapshot(s) are missing.
--
SELECT count(*)
INTO p_summarized_dbs
FROM MGMT_V_HOST_DBS;
--
-- Compute number of asm targets with appropriate metric collections as
-- p_summarized_asm_count. It will be less than p_total_asm_count when
-- all of the expected metric columns are missing from mgmt_current_metrics.
--
p_summarized_asm_count := GET_SUMMARIZED_ASM_COUNT(
p_target_name => p_target_name,
p_target_type => p_target_type);
GET_STORAGE_ISSUES_COUNT(p_target_name => p_target_name,
p_target_type => p_target_type,
p_unmonitored_nfs_count => p_unmonitored_nfs_count,
p_other_issues_count => p_other_issues_count);
GET_HOST_DB_ASM_MET_ERRORS(p_target_name => p_target_name,
p_target_type => p_target_type,
p_host_metric_errors => p_host_metric_errors,
p_db_metric_errors => p_db_metric_errors,
p_asm_metric_errors => p_asm_metric_errors);
END GET_STRG_DATA_CFG_AND_ERRORS;
----------------------------------------------------------------------
PROCEDURE GET_HOST_DB_ASM_MET_ERRORS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_host_metric_errors OUT NUMBER,
p_db_metric_errors OUT NUMBER,
p_asm_metric_errors OUT NUMBER) IS
BEGIN
SELECT SUM(DECODE(e.target_type,
MGMT_GLOBAL.G_HOST_TARGET_TYPE, 1,
0)) host_metric_errors,
SUM(DECODE(e.target_type,
MGMT_GLOBAL.G_DATABASE_TARGET_TYPE, 1,
MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE, 1,
0)) db_metric_errors,
SUM(DECODE(e.target_type,
MGMT_GLOBAL.G_ASM_TARGET_TYPE, 1,
0)) asm_metric_errors
INTO p_host_metric_errors,
p_db_metric_errors,
p_asm_metric_errors
FROM MGMT_V_STORAGE_COLL_ERRORS e;
END GET_HOST_DB_ASM_MET_ERRORS;
----------------------------------------------------------------------
--
-- Gets timezone region of a target.
--
FUNCTION GET_TARGET_TIMEZONE_REGION(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN VARCHAR2 IS
l_target_timezone_region MGMT_TARGETS.timezone_region%TYPE;
BEGIN
SELECT NVL(timezone_region, sessiontimezone)
INTO l_target_timezone_region
FROM MGMT_TARGETS
WHERE target_name = p_target_name
AND target_type = p_target_type;
RETURN l_target_timezone_region;
END GET_TARGET_TIMEZONE_REGION;
----------------------------------------------------------------------
--
-- Returns 'Y' if storage history for a group is enabled, 'N' otherwise.
-- Returns 'Y' for a host target.
--
PROCEDURE GET_HIST_ENABLED_EXISTS_FLAG(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_hist_enabled OUT VARCHAR2,
p_hist_data_exists_flag OUT VARCHAR2,
p_refresh_job_name OUT MGMT_JOB.job_name%TYPE,
p_refresh_job_exec_id OUT MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE,
p_refresh_job_status OUT MGMT_JOB_EXEC_SUMMARY.status%TYPE) IS
BEGIN
-- since this method can be invoked independently
-- we need to insert the targets from here
INSERT_TARGETS_FOR_UI_QUERIES(p_target_name, p_target_type);
SET_HIST_DATA_EXISTS_FLAG(p_target_name => p_target_name,
p_target_type => p_target_type,
p_hist_data_exists_flag => p_hist_data_exists_flag);
p_storage_hist_enabled := GET_STORAGE_HIST_ENABLED(
p_target_name => p_target_name,
p_target_type => p_target_type);
GET_REFRESH_JOB_INFO(p_target_name => p_target_name,
p_target_type => p_target_type,
p_refresh_job_name => p_refresh_job_name,
p_refresh_job_exec_id => p_refresh_job_exec_id,
p_refresh_job_status => p_refresh_job_status);
END GET_HIST_ENABLED_EXISTS_FLAG;
----------------------------------------------------------------------
PROCEDURE GET_DB_ALLOC_USED_FREE(p_db_allocated OUT NUMBER,
p_db_used OUT NUMBER,
p_db_free OUT NUMBER) IS
BEGIN
SELECT SUM(allocated) as allocated,
SUM(used) as used
INTO p_db_allocated,
p_db_used
FROM mgmt_v_db_alloc_used;
IF (NOT(p_db_allocated IS NULL AND p_db_used IS NULL)) THEN
p_db_free := NVL(p_db_allocated, 0) - NVL(p_db_used, 0);
END IF;
END GET_DB_ALLOC_USED_FREE;
----------------------------------------------------------------------
PROCEDURE GET_LOCAL_FS_NFS_USED_FREE(p_local_fs_used OUT NUMBER,
p_local_fs_free OUT NUMBER,
p_writeable_nfs_used OUT NUMBER,
p_writeable_nfs_free OUT NUMBER) IS
l_writeable_nfs_size NUMBER;
l_local_fs_size NUMBER;
BEGIN
SELECT SUM(writeable_nfs_size) writeable_nfs_size,
SUM(local_fs_size) local_fs_size
INTO l_writeable_nfs_size,
l_local_fs_size
FROM (
SELECT CASE WHEN data.storage_layer = G_NFS AND
nfs_mount_privilege = G_WRITE_PRIVELEGE
THEN sizeb
ELSE NULL
END writeable_nfs_size,
CASE WHEN data.storage_layer = G_LOCAL_FILESYSTEM
THEN sizeb
ELSE NULL
END local_fs_size,
ROW_NUMBER() OVER (PARTITION BY global_unique_id
ORDER BY name)
global_unique_id_rownumber
FROM MGMT_V_STORAGE_REPORT_DATA data,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id = data.ecm_snapshot_id
AND data.storage_layer IN
(STORAGE_UI_COMMON_PKG.G_LOCAL_FILESYSTEM,
STORAGE_UI_COMMON_PKG.G_NFS)
AND data.is_virtual_layer=STORAGE_UI_UTIL_PKG.NO
AND data.is_bottom_layer =STORAGE_UI_UTIL_PKG.YES
)
WHERE global_unique_id_rownumber = 1;
SELECT SUM(writeable_nfs_free) writeable_nfs_free,
SUM(local_fs_free) local_fs_free
INTO p_writeable_nfs_free,
p_local_fs_free
FROM (
SELECT name,
CASE WHEN data.storage_layer = G_LOCAL_FILESYSTEM
THEN freeb
ELSE NULL
END local_fs_free,
CASE WHEN data.storage_layer = G_NFS AND
nfs_mount_privilege = G_WRITE_PRIVELEGE
THEN freeb
ELSE NULL
END writeable_nfs_free,
ROW_NUMBER() OVER (PARTITION BY global_unique_id
ORDER BY name)
global_unique_id_rownumber
FROM MGMT_V_STORAGE_REPORT_DATA data,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id = data.ecm_snapshot_id
AND data.storage_layer IN
(STORAGE_UI_COMMON_PKG.G_LOCAL_FILESYSTEM,
STORAGE_UI_COMMON_PKG.G_NFS)
AND is_virtual_layer =STORAGE_UI_UTIL_PKG.NO
)
WHERE global_unique_id_rownumber = 1;
IF (NOT(l_writeable_nfs_size IS NULL)) THEN
p_writeable_nfs_used := l_writeable_nfs_size - NVL(p_writeable_nfs_free, 0);
END IF;
IF (NOT(l_local_fs_size IS NULL)) THEN
p_local_fs_used := l_local_fs_size - NVL(p_local_fs_free, 0);
END IF;
END GET_LOCAL_FS_NFS_USED_FREE;
----------------------------------------------------------------------
PROCEDURE GET_VOL_DISK_USED_BY_LAYER(p_storage_layer IN VARCHAR2,
p_vol_allocated_by_layer OUT NUMBER,
p_disk_allocated_by_layer OUT NUMBER) IS
BEGIN
SELECT SUM(disk_usedb),
SUM(vol_usedb)
INTO p_disk_allocated_by_layer,
p_vol_allocated_by_layer
FROM (
SELECT data.storage_layer as from_storage_layer,
DECODE(data.storage_layer,
STORAGE_UI_COMMON_PKG.G_OS_DISK, data.usedb,
NULL) AS disk_usedb,
DECODE(data.storage_layer,
STORAGE_UI_COMMON_PKG.G_VOLUME_MANAGER, data.usedb,
NULL) AS vol_usedb,
parent.storage_layer as to_storage_layer,
ROW_NUMBER() OVER (PARTITION BY data.storage_layer,
data.global_unique_id
ORDER BY data.target_name) AS
global_unique_id_rownumber
FROM MGMT_V_STORAGE_REPORT_DATA data,
MGMT_V_STORAGE_REPORT_DATA parent,
MGMT_STORAGE_REPORT_KEYS keys,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id = data.ecm_snapshot_id
AND data.ecm_snapshot_id = parent.ecm_snapshot_id
AND keys.ecm_snapshot_id = data.ecm_snapshot_id
AND data.key_value = keys.key_value
AND keys.parent_key_value = parent.key_value
AND data.storage_layer in(G_OS_DISK, G_VOLUME_MANAGER)
AND data.storage_layer != parent.storage_layer
AND parent.storage_layer like p_storage_layer
)
WHERE global_unique_id_rownumber = 1;
END GET_VOL_DISK_USED_BY_LAYER;
----------------------------------------------------------------------
PROCEDURE GET_DB_DATA_FILES_SOURCE(p_db_df_on_local_fs OUT NUMBER,
p_db_df_on_nfs OUT NUMBER,
p_db_df_on_volumes OUT NUMBER,
p_db_df_on_disks OUT NUMBER) IS
BEGIN
SELECT SUM(DECODE(storage_layer,
G_LOCAL_FILESYSTEM, file_size,
0)) db_df_on_local_fs,
SUM(DECODE(storage_layer,
G_NFS, file_size,
0)) db_df_on_nfs,
SUM(DECODE(storage_layer,
G_VOLUME_MANAGER, file_size,
0)) db_df_on_volumes,
SUM(DECODE(storage_layer,
G_OS_DISK, file_size,
0)) db_df_on_disks
INTO p_db_df_on_local_fs, p_db_df_on_nfs,
p_db_df_on_volumes, p_db_df_on_disks
FROM (
SELECT dbs.target_name, dbs.target_type,
df.file_name, df.file_size,
df.os_storage_entity, data.storage_layer
FROM MGMT_V_DB_DATAFILES_ECM df,
MGMT$ECM_CURRENT_SNAPSHOTS dbs,
MGMT_V_STORAGE_REPORT_DATA data,
MGMT_STORAGE_REPORT_KEYS keys,
MGMT_STORAGE_REPORT_ALIAS alias,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE df.ecm_snapshot_id = dbs.ecm_snapshot_id
AND dbs.target_type in(MGMT_GLOBAL.G_DATABASE_TARGET_TYPE,
MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE)
AND uit.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND dbs.host_name = uit.target_name
AND data.ecm_snapshot_id = uit.ecm_snapshot_id
AND data.storage_layer IN (G_LOCAL_FILESYSTEM, G_NFS,
G_VOLUME_MANAGER, G_OS_DISK)
AND keys.ecm_snapshot_id = data.ecm_snapshot_id
AND keys.key_value = data.key_value
AND keys.parent_key_value = alias.key_value
AND alias.ecm_snapshot_id = data.ecm_snapshot_id
AND alias.value = df.os_storage_entity
);
END GET_DB_DATA_FILES_SOURCE;
----------------------------------------------------------------------
PROCEDURE GET_DB_TABLESPACE_SIZE_USED(p_tablespace_size OUT NUMBER,
p_tablespace_used OUT NUMBER) IS
BEGIN
SELECT SUM(t.tablespace_size) total_size,
SUM(t.tablespace_used_size) total_used
INTO p_tablespace_size,
p_tablespace_used
FROM MGMT_V_HOST_DBS r,
MGMT_V_DB_TABLESPACES_ECM t
WHERE r.ecm_snapshot_id = t.ecm_snapshot_id;
END GET_DB_TABLESPACE_SIZE_USED;
----------------------------------------------------------------------
PROCEDURE GET_DB_STORAGE_SOURCE(p_db_on_local_disks OUT NUMBER,
p_db_on_volumes OUT NUMBER,
p_db_on_asm OUT NUMBER,
p_db_on_local_fs OUT NUMBER,
p_db_on_writeable_nfs OUT NUMBER) IS
BEGIN
GET_DB_DATA_FILES_SOURCE(p_db_df_on_local_fs => p_db_on_local_fs,
p_db_df_on_nfs => p_db_on_writeable_nfs,
p_db_df_on_volumes => p_db_on_volumes,
p_db_df_on_disks => p_db_on_local_disks);
GET_ASM_ALLOCATED_TO_DBS(p_asm_allocated => p_db_on_asm);
END GET_DB_STORAGE_SOURCE;
----------------------------------------------------------------------
FUNCTION GET_USAGE_SUMMARY_AS_CURSOR(p_db_on_local_disks IN NUMBER,
p_db_on_volumes IN NUMBER,
p_db_on_asm IN NUMBER,
p_db_on_local_fs IN NUMBER,
p_db_on_writeable_nfs IN NUMBER,
p_db_allocated IN NUMBER,
p_db_tablespace_size IN NUMBER,
p_db_tablespace_used IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_on_local_disks IN NUMBER,
p_local_fs_on_volumes IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_result_cursor SYS_REFCURSOR;
BEGIN
OPEN l_result_cursor FOR
SELECT round( p_db_on_local_disks/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) db_on_local_disks,
round(p_db_on_volumes/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) db_on_volumes,
round(p_db_on_asm/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) db_on_asm,
round(p_db_on_local_fs/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) db_on_local_fs,
round(p_db_on_writeable_nfs/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) db_on_writeable_nfs,
round(p_local_fs_on_local_disks/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) local_fs_on_local_disks,
round(p_local_fs_on_volumes/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) local_fs_on_volumes,
round(p_db_used/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) db_used,
round(p_db_free/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) db_free,
round(p_local_fs_used/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) local_fs_used,
round(p_local_fs_free/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) local_fs_free,
round(p_writeable_nfs_used/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) writeable_nfs_used,
round(p_writeable_nfs_free/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) writeable_nfs_free
FROM DUAL;
RETURN l_result_cursor;
END GET_USAGE_SUMMARY_AS_CURSOR;
----------------------------------------------------------------------
PROCEDURE GET_UTIL_SUMMARY_VALUES(p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_total_allocated OUT NUMBER,
p_total_unallocated OUT NUMBER,
p_total_overhead OUT NUMBER,
p_total_used OUT NUMBER,
p_total_free OUT NUMBER) IS
BEGIN
IF (NOT(p_writeable_nfs_allocated IS NULL AND
p_asm_allocated IS NULL AND
p_vol_allocated IS NULL AND
p_disk_allocated IS NULL))
THEN
p_total_allocated := NVL(p_writeable_nfs_allocated, 0) +
NVL(p_asm_allocated, 0) +
NVL(p_vol_allocated, 0) +
NVL(p_disk_allocated, 0);
END IF;
IF (NOT(p_asm_unallocated IS NULL AND
p_vol_unallocated IS NULL AND
p_disk_unallocated IS NULL))
THEN
p_total_unallocated := NVL(p_asm_unallocated, 0) +
NVL(p_vol_unallocated, 0) +
NVL(p_disk_unallocated, 0);
END IF;
IF (NOT(p_db_free IS NULL AND
p_local_fs_free IS NULL AND
p_writeable_nfs_free IS NULL))
THEN
p_total_free := NVL(p_db_free, 0) +
NVL(p_local_fs_free, 0) +
NVL(p_writeable_nfs_free, 0);
END IF;
IF (NOT(p_total_allocated IS NULL AND
p_total_free IS NULL))
THEN
p_total_used := NVL(p_total_allocated, 0) -
NVL(p_total_free, 0);
END IF;
IF (NOT(p_vol_overhead IS NULL AND
p_asm_overhead IS NULL))
THEN
p_total_overhead := NVL(p_vol_overhead, 0) +
NVL(p_asm_overhead, 0);
END IF;
END GET_UTIL_SUMMARY_VALUES;
----------------------------------------------------------------------
FUNCTION GET_UTIL_SUMMARY_AS_CURSOR(p_total_unallocated IN NUMBER,
p_total_overhead IN NUMBER,
p_total_used IN NUMBER,
p_total_free IN NUMBER,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_result_cursor SYS_REFCURSOR;
BEGIN
OPEN l_result_cursor FOR
SELECT round(p_total_used/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) total_used,
round(p_total_free/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) total_free,
round(p_total_unallocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) total_unallocated,
round(p_total_overhead/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) total_overhead
FROM DUAL;
RETURN l_result_cursor;
END GET_UTIL_SUMMARY_AS_CURSOR;
----------------------------------------------------------------------
PROCEDURE GET_ASM_ALLOCATED_TO_DBS(p_asm_allocated OUT NUMBER) IS
BEGIN
/*
-- currently we are assuming that the same ASM can not have the
-- one disk group which can serve more than one databases.
-- At least that is what the ROW_NUMBER() clause is there for.
-- We are not dealing with the situation wherein, we go via the
-- diskgroups to the databases but double count the storage
-- allocated because the same dg may be serving multiple
-- databases.
SELECT NVL(sum(total_mb), NULL) asm_allocated
INTO p_asm_allocated
FROM
(
SELECT a.host_name, total_mb,
ROW_NUMBER() OVER
(
PARTITION BY db_unique_name, cluster_db_flag,
disk_group_name
ORDER BY cluster_db_flag, disk_group_name
) rn
FROM mgmt_v_asm_db_dg_usage a
)
WHERE rn = 1;
*/
SELECT NVL((sum(total_mb)), NULL) asm_allocated
INTO p_asm_allocated
FROM MGMT_V_ASM_DB_DG_USAGE;
END GET_ASM_ALLOCATED_TO_DBS;
----------------------------------------------------------------------
PROCEDURE GET_ASM_ALLOC_UNALLOC_OVERHEAD(p_asm_allocated OUT NUMBER,
p_asm_unallocated OUT NUMBER,
p_asm_overhead OUT NUMBER) IS
l_asm_size NUMBER;
BEGIN
GET_ASM_ALLOCATED_TO_DBS(p_asm_allocated);
/*
SELECT NVL(((sum(total_mb))*1024*1024), 0) as asm_size,
NVL(((sum(usable_file_mb))*1024*1024), 0) as asm_unallocated
INTO l_asm_size,
p_asm_unallocated
FROM
(
SELECT disk_group_name, db_unique_name,
cluster_db_flag, total_mb,
usable_file_mb,
ROW_NUMBER() OVER
(
PARTITION BY db_unique_name, cluster_db_flag,
disk_group_name
ORDER BY cluster_db_flag, disk_group_name
) rn
FROM mgmt_v_asm_dg_usage
)
WHERE rn = 1;
*/
SELECT NVL(((SUM(total_mb))*1024*1024), NULL) AS asm_size,
NVL(((SUM(usable_file_mb))*1024*1024), NULL) AS asm_unallocated
INTO l_asm_size,
p_asm_unallocated
FROM MGMT_V_ASM_DG_USAGE;
IF (NOT(l_asm_size IS NULL AND
p_asm_allocated IS NULL AND
p_asm_unallocated IS NULL))
THEN
p_asm_overhead := NVL(l_asm_size,0) -
(NVL(p_asm_allocated,0) + NVL(p_asm_unallocated,0));
END IF;
END GET_ASM_ALLOC_UNALLOC_OVERHEAD;
----------------------------------------------------------------------
PROCEDURE GET_VOL_DISK_NFS_SIZE(p_disk_size OUT NUMBER,
p_vol_size OUT NUMBER,
p_writeable_nfs_allocated OUT NUMBER) IS
BEGIN
-- Note: Writeable nfs total size is the same as storage allocated
-- from writeable nfs.
SELECT SUM(disk_sizeb) disk_size,
SUM(writeable_nfs_sizeb) writeable_nfs_allocated,
SUM(vol_sizeb) vol_size
INTO p_disk_size, p_writeable_nfs_allocated, p_vol_size
FROM (
SELECT DECODE(data.storage_layer,
STORAGE_UI_COMMON_PKG.G_OS_DISK, sizeb,
NULL) disk_sizeb,
DECODE(data.storage_layer||nfs_mount_privilege,
STORAGE_UI_COMMON_PKG.G_NFS_WRITE, sizeb,
NULL) writeable_nfs_sizeb,
DECODE(data.storage_layer,
STORAGE_UI_COMMON_PKG.G_VOLUME_MANAGER, sizeb,
NULL) vol_sizeb,
ROW_NUMBER() OVER (PARTITION BY data.storage_layer,
data.global_unique_id
ORDER BY data.ecm_snapshot_id, data.name)
AS global_unique_id_rownumber
FROM MGMT_V_STORAGE_REPORT_DATA data,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id = data.ecm_snapshot_id
AND data.storage_layer IN
(
STORAGE_UI_COMMON_PKG.G_OS_DISK,
STORAGE_UI_COMMON_PKG.G_NFS,
STORAGE_UI_COMMON_PKG.G_VOLUME_MANAGER
)
AND is_bottom_layer = STORAGE_UI_UTIL_PKG.YES
)
WHERE global_unique_id_rownumber = 1;
END GET_VOL_DISK_NFS_SIZE;
----------------------------------------------------------------------
PROCEDURE GET_VOL_DISK_UNALLOCATED(p_disk_unallocated OUT NUMBER,
p_vol_unallocated OUT NUMBER) IS
BEGIN
SELECT SUM(vol_freeb) vol_unallocated,
SUM(disk_freeb) disk_unallocated
INTO p_vol_unallocated,
p_disk_unallocated
FROM (
SELECT storage_layer,
DECODE(storage_layer,
STORAGE_UI_COMMON_PKG.G_VOLUME_MANAGER, freeb,
NULL) vol_freeb,
DECODE(storage_layer,
STORAGE_UI_COMMON_PKG.G_OS_DISK, freeb,
NULL) disk_freeb,
ROW_NUMBER() OVER (PARTITION BY data.storage_layer,
data.global_unique_id
ORDER BY data.ecm_snapshot_id, data.name)
AS global_unique_id_rownumber
FROM MGMT_V_STORAGE_REPORT_DATA data,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE data.ecm_snapshot_id = uit.ecm_snapshot_id
AND storage_layer IN
(
STORAGE_UI_COMMON_PKG.G_VOLUME_MANAGER,
STORAGE_UI_COMMON_PKG.G_OS_DISK
)
AND is_virtual_layer=STORAGE_UI_UTIL_PKG.NO
)
WHERE global_unique_id_rownumber = 1;
END GET_VOL_DISK_UNALLOCATED;
----------------------------------------------------------------------
PROCEDURE GET_VOL_DISK_ALLOCATED(p_disk_allocated OUT NUMBER,
p_vol_allocated OUT NUMBER) IS
BEGIN
SELECT SUM(vol_usedb) vol_allocated,
SUM(disk_usedb) disk_allocated
INTO p_vol_allocated,
p_disk_allocated
FROM (
SELECT storage_layer,
DECODE(storage_layer,
STORAGE_UI_COMMON_PKG.G_VOLUME_MANAGER, usedb,
NULL) vol_usedb,
DECODE(storage_layer,
STORAGE_UI_COMMON_PKG.G_OS_DISK, usedb,
NULL) disk_usedb,
ROW_NUMBER() OVER (PARTITION BY data.storage_layer,
data.global_unique_id
ORDER BY data.ecm_snapshot_id, data.name)
AS global_unique_id_rownumber
FROM MGMT_V_STORAGE_REPORT_DATA data,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id = data.ecm_snapshot_id
AND storage_layer IN
(
STORAGE_UI_COMMON_PKG.G_VOLUME_MANAGER,
STORAGE_UI_COMMON_PKG.G_OS_DISK
)
AND is_allocated =STORAGE_UI_UTIL_PKG.YES
AND is_top_layer =STORAGE_UI_UTIL_PKG.YES
AND is_virtual_layer=STORAGE_UI_UTIL_PKG.NO
)
WHERE global_unique_id_rownumber = 1;
END;
----------------------------------------------------------------------
FUNCTION GET_ALLOC_SUMMARY_AS_CURSOR(p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_vol_size IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_asm_size IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_disk_size IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_result_cursor SYS_REFCURSOR;
BEGIN
-- currently we dont calculate how much asm is allocated
-- from volumes (it is a corner case).
-- also we need to exclude asm from disks allocated also.
-- this is higher priority.
-- for the time being retain the old column names
-- some of which are meaningless (e.g. volumes_excl_asm_unallocated
-- should be simply vol_unallocated.)
OPEN l_result_cursor FOR
SELECT round(p_vol_allocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) vol_allocated,
round(p_vol_unallocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) vol_unallocated,
round(p_vol_overhead/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) vol_overhead,
round(p_disk_allocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) disks_allocated,
round(p_disk_unallocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) disks_unallocated,
round(p_writeable_nfs_allocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) writeable_nfs_allocated,
round(p_asm_allocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) asm_allocated,
round(p_asm_unallocated/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) asm_unallocated,
round(p_asm_overhead/STORAGE_UI_UTIL_PKG.BYTES_PER_GB, 2) asm_overhead
FROM DUAL;
RETURN l_result_cursor;
END GET_ALLOC_SUMMARY_AS_CURSOR;
----------------------------------------------------------------------
--
-- Gets storage summary values in byte size numbers.
-- If p_calc_storage_source_flag is FALSE, then it does not compute
-- p_db_on_* values. It is set to FALSE for repository metric collections
-- and TRUE for rendeting Storage Details page.
--
PROCEDURE GET_STORAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_calc_storage_source_flag IN NUMBER
DEFAULT MGMT_GLOBAL.G_TRUE,
p_vol_allocated OUT NUMBER,
p_vol_overhead OUT NUMBER,
p_vol_unallocated OUT NUMBER,
p_vol_size OUT NUMBER,
p_asm_allocated OUT NUMBER,
p_asm_unallocated OUT NUMBER,
p_asm_overhead OUT NUMBER,
p_disk_allocated OUT NUMBER,
p_disk_unallocated OUT NUMBER,
p_disk_size OUT NUMBER,
p_asm_size OUT NUMBER,
p_writeable_nfs_allocated OUT NUMBER,
p_db_on_local_disks OUT NUMBER,
p_db_on_volumes OUT NUMBER,
p_db_on_asm OUT NUMBER,
p_db_on_local_fs OUT NUMBER,
p_db_on_writeable_nfs OUT NUMBER,
p_db_allocated OUT NUMBER,
p_db_tablespace_size OUT NUMBER,
p_db_tablespace_used OUT NUMBER,
p_db_used OUT NUMBER,
p_db_free OUT NUMBER,
p_local_fs_on_local_disks OUT NUMBER,
p_local_fs_on_volumes OUT NUMBER,
p_local_fs_used OUT NUMBER,
p_local_fs_free OUT NUMBER,
p_writeable_nfs_used OUT NUMBER,
p_writeable_nfs_free OUT NUMBER,
p_total_allocated OUT NUMBER,
p_total_unallocated OUT NUMBER,
p_total_overhead OUT NUMBER,
p_total_used OUT NUMBER,
p_total_free OUT NUMBER) IS
BEGIN
GET_ALLOCATION_SUMMARY_VALUES(p_target_name => p_target_name,
p_target_type => p_target_type,
p_vol_allocated => p_vol_allocated,
p_vol_overhead => p_vol_overhead,
p_vol_unallocated => p_vol_unallocated,
p_vol_size => p_vol_size,
p_asm_allocated => p_asm_allocated,
p_asm_unallocated => p_asm_unallocated,
p_asm_overhead => p_asm_overhead,
p_asm_size => p_asm_size,
p_disk_allocated => p_disk_allocated,
p_disk_unallocated => p_disk_unallocated,
p_disk_size => p_disk_size,
p_writeable_nfs_allocated => p_writeable_nfs_allocated);
GET_USAGE_SUMMARY_VALUES(p_target_name => p_target_name,
p_target_type => p_target_type,
p_calc_storage_source_flag => p_calc_storage_source_flag,
p_db_on_local_disks => p_db_on_local_disks,
p_db_on_volumes => p_db_on_volumes,
p_db_on_asm => p_db_on_asm,
p_db_on_local_fs => p_db_on_local_fs,
p_db_on_writeable_nfs => p_db_on_writeable_nfs,
p_db_allocated => p_db_allocated,
p_db_tablespace_size => p_db_tablespace_size,
p_db_tablespace_used => p_db_tablespace_used,
p_db_used => p_db_used,
p_db_free => p_db_free,
p_local_fs_on_local_disks => p_local_fs_on_local_disks,
p_local_fs_on_volumes => p_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);
GET_UTIL_SUMMARY_VALUES(p_vol_allocated => p_vol_allocated,
p_vol_overhead => p_vol_overhead,
p_vol_unallocated => p_vol_unallocated,
p_asm_allocated => p_asm_allocated,
p_asm_unallocated => p_asm_unallocated,
p_asm_overhead => p_asm_overhead,
p_disk_allocated => p_disk_allocated,
p_disk_unallocated => p_disk_unallocated,
p_writeable_nfs_allocated => p_writeable_nfs_allocated,
p_db_used => p_db_used,
p_db_free => p_db_free,
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);
/*
--IF(emdw_log.P_IS_DEBUG_SET) then
PRINT_STORAGE_SUMMARY_VALUES
(
p_target_name => p_target_name,
p_target_type => p_target_type,
p_vol_allocated => p_vol_allocated,
p_vol_overhead => p_vol_overhead,
p_vol_unallocated => p_vol_unallocated,
p_vol_size => p_vol_size,
p_asm_allocated => p_asm_allocated,
p_asm_unallocated => p_asm_unallocated,
p_asm_overhead => p_asm_overhead,
p_disk_allocated => p_disk_allocated,
p_disk_unallocated => p_disk_unallocated,
p_disk_size => p_disk_size,
p_asm_size => p_asm_size,
p_writeable_nfs_allocated => p_writeable_nfs_allocated,
p_db_on_local_disks => p_db_on_local_disks,
p_db_on_volumes => p_db_on_volumes,
p_db_on_asm => p_db_on_asm,
p_db_on_local_fs => p_db_on_local_fs,
p_db_on_writeable_nfs => p_db_on_writeable_nfs,
p_db_allocated => p_db_allocated,
p_db_tablespace_size => p_db_tablespace_size,
p_db_tablespace_used => p_db_tablespace_used,
p_db_used => p_db_used,
p_db_free => p_db_free,
p_local_fs_on_local_disks => p_local_fs_on_local_disks,
p_local_fs_on_volumes => p_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 IF;
*/
END GET_STORAGE_SUMMARY_VALUES;
----------------------------------------------------------------------
FUNCTION GET_COLLECTION_INFO(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_result_cursor SYS_REFCURSOR;
BEGIN
OPEN l_result_cursor FOR
SELECT collection_timestamp,
collection_error_count,
mapping_issues_count
FROM (
SELECT MAX(collection_timestamp) collection_timestamp
FROM (
SELECT MAX(snap.saved_timestamp) collection_timestamp
FROM MGMT$ECM_CURRENT_SNAPSHOTS snap,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND uit.target_name = snap.host_name
AND snap.snapshot_type IN (G_DB_CONFIG_COLL_NAME,
G_RAC_CONFIG_COLL_NAME,
G_HOST_STORAGE_COLL_NAME)
UNION
SELECT MAX(cm.collection_timestamp) collection_timestamp
FROM MGMT_CURRENT_METRICS cm,
MGMT_TARGETS t,
MGMT_METRICS m,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.target_name = t.host_name
AND t.target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND t.target_guid = cm.target_guid
AND cm.metric_guid = m.metric_guid
AND t.type_meta_ver = m.type_meta_ver
AND (m.category_prop_1 = ' ' OR
m.category_prop_1 = t.category_prop_1)
AND (m.category_prop_2 = ' ' OR
m.category_prop_2 = t.category_prop_2)
AND (m.category_prop_3 = ' ' OR
m.category_prop_3 = t.category_prop_3)
AND (m.category_prop_4 = ' ' OR
m.category_prop_4 = t.category_prop_4)
AND (m.category_prop_5 = ' ' OR
m.category_prop_5 = t.category_prop_5)
AND m.metric_name IN
(
STORAGE_UI_COMMON_PKG.G_ASM_DG_USAGE_METRIC,
STORAGE_UI_COMMON_PKG.G_ASM_DB_DG_METRIC
)
)
),
(
SELECT count(*) collection_error_count
FROM MGMT_V_STORAGE_COLL_ERRORS e
),
(
SELECT count(*) mapping_issues_count
FROM MGMT_STORAGE_REPORT_ISSUES i,
MGMT_STORAGE_REPORT_UI_TARGETS tgt
WHERE tgt.ecm_snapshot_id = i.ecm_snapshot_id
);
RETURN l_result_cursor;
END GET_COLLECTION_INFO;
----------------------------------------------------------------------
PROCEDURE SET_GLOBAL_METRIC_NAME(p_target_type IN VARCHAR2) IS
BEGIN
IF(p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
gv_storage_rep_metric :=
STORAGE_REP_METRICS_PKG.G_HOST_STORAGE_REP_METRIC;
ELSE
gv_storage_rep_metric :=
STORAGE_REP_METRICS_PKG.G_GROUP_STORAGE_REP_METRIC;
END IF;
END SET_GLOBAL_METRIC_NAME;
----------------------------------------------------------------------
PROCEDURE SET_HIST_QUERY_ENV(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN INTEGER) IS
BEGIN
INSERT_TARGETS_FOR_UI_QUERIES(p_target_name, p_target_type);
STORAGE_UI_UTIL_PKG.SET_STORAGE_CONTEXT('history_period_in_days',
TO_CHAR(p_period_in_days));
END SET_HIST_QUERY_ENV;
----------------------------------------------------------------------
FUNCTION GET_STORAGE_UNIT(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN VARCHAR2 IS
l_storage_unit VARCHAR2(2);
BEGIN
RETURN STORAGE_UI_UTIL_PKG.GB;
END GET_STORAGE_UNIT;
----------------------------------------------------------------------
PROCEDURE GET_HISTORY_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
l_storage_timeseries_rec_list STORAGE_TIMESERIES_REC_LIST;
BEGIN
SET_HIST_QUERY_ENV(p_target_name, p_target_type, p_period_in_days);
SELECT STORAGE_TIMESERIES_REC(rollup_timestamp, NULL, unallocated,
overhead, used, free)
BULK COLLECT
INTO l_storage_timeseries_rec_list
FROM (
SELECT rollup_timestamp,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_TOTAL_UNALLOCATED, value,
NULL)) unallocated,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_TOTAL_OVERHEAD, value,
NULL)) overhead,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_TOTAL_USED, value,
NULL)) used,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_TOTAL_FREE, value,
NULL)) free
FROM MGMT_V_TIMESERIES_DAILY_HIST
WHERE metric_column IN
(
STORAGE_REP_METRICS_PKG.G_TOTAL_OVERHEAD,
STORAGE_REP_METRICS_PKG.G_TOTAL_UNALLOCATED,
STORAGE_REP_METRICS_PKG.G_TOTAL_USED,
STORAGE_REP_METRICS_PKG.G_TOTAL_FREE
)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp
);
GET_SUMM_TIMESERIES_AS_CURSOR(l_storage_timeseries_rec_list,
p_summary_cursor);
GET_MIN_MAX_DATES(l_storage_timeseries_rec_list,
p_min_timeseries_date, p_max_timeseries_date);
END GET_HISTORY_SUMMARY;
----------------------------------------------------------------------
FUNCTION GET_HISTORY_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN metric_column = STORAGE_REP_METRICS_PKG.G_TOTAL_ALLOCATED
THEN 'ALLOCATED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_TOTAL_UNALLOCATED
THEN 'UNALLOCATED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_TOTAL_OVERHEAD
THEN 'OVERHEAD_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_TOTAL_USED
THEN 'USED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_TOTAL_FREE
THEN 'FREE_STORAGE_ATTRIBUTE'
END storage_attribute,
average, maximum, minimum, last_known_value
FROM mgmt_v_storage_stats
WHERE metric_column IN (STORAGE_REP_METRICS_PKG.G_TOTAL_USED,
STORAGE_REP_METRICS_PKG.G_TOTAL_FREE,
STORAGE_REP_METRICS_PKG.G_TOTAL_OVERHEAD,
STORAGE_REP_METRICS_PKG.G_TOTAL_UNALLOCATED,
STORAGE_REP_METRICS_PKG.G_TOTAL_ALLOCATED)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type;
RETURN l_cursor;
END GET_HISTORY_STATS;
----------------------------------------------------------------------
FUNCTION GET_HISTORY_DB_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN metric_column = STORAGE_REP_METRICS_PKG.G_DB_USED
THEN 'USED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_DB_FREE
THEN 'FREE_STORAGE_ATTRIBUTE'
END storage_attribute,
average, maximum, minimum, last_known_value
FROM mgmt_v_storage_stats
WHERE metric_column IN (STORAGE_REP_METRICS_PKG.G_DB_USED,
STORAGE_REP_METRICS_PKG.G_DB_FREE)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type;
RETURN l_cursor;
END GET_HISTORY_DB_STATS;
----------------------------------------------------------------------
PROCEDURE GET_HISTORY_DB_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
l_storage_timeseries_rec_list STORAGE_TIMESERIES_REC_LIST;
BEGIN
SET_HIST_QUERY_ENV(p_target_name, p_target_type, p_period_in_days);
SELECT STORAGE_TIMESERIES_REC(rollup_timestamp, NULL, NULL,
NULL, used, free)
BULK COLLECT
INTO l_storage_timeseries_rec_list
FROM (
SELECT rollup_timestamp,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_DB_USED, value,
NULL)) used,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_DB_FREE, value,
NULL)) free
FROM MGMT_V_TIMESERIES_DAILY_HIST
WHERE metric_column IN
(
STORAGE_REP_METRICS_PKG.G_DB_USED,
STORAGE_REP_METRICS_PKG.G_DB_FREE
)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp
);
GET_APP_TIMESERIES_AS_CURSOR(l_storage_timeseries_rec_list,
p_summary_cursor);
GET_MIN_MAX_DATES(l_storage_timeseries_rec_list,
p_min_timeseries_date, p_max_timeseries_date);
END GET_HISTORY_DB_SUMMARY;
----------------------------------------------------------------------
FUNCTION GET_HISTORY_LOCALFS_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN metric_column = STORAGE_REP_METRICS_PKG.G_LOCAL_FS_USED
THEN 'USED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_LOCAL_FS_FREE
THEN 'FREE_STORAGE_ATTRIBUTE'
END storage_attribute,
average, maximum, minimum, last_known_value
FROM mgmt_v_storage_stats
WHERE metric_column IN (STORAGE_REP_METRICS_PKG.G_LOCAL_FS_USED,
STORAGE_REP_METRICS_PKG.G_LOCAL_FS_FREE)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type;
RETURN l_cursor;
END GET_HISTORY_LOCALFS_STATS;
----------------------------------------------------------------------
PROCEDURE GET_HISTORY_LOCALFS_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
l_storage_timeseries_rec_list STORAGE_TIMESERIES_REC_LIST;
BEGIN
SET_HIST_QUERY_ENV(p_target_name, p_target_type, p_period_in_days);
-- first try from the daily metric rollup table.
SELECT STORAGE_TIMESERIES_REC(rollup_timestamp, NULL, NULL,
NULL, used, free)
BULK COLLECT
INTO l_storage_timeseries_rec_list
FROM (
SELECT rollup_timestamp,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_LOCAL_FS_USED, value,
NULL)) used,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_LOCAL_FS_FREE, value,
NULL)) free
FROM MGMT_V_TIMESERIES_DAILY_HIST
WHERE metric_column IN
(
STORAGE_REP_METRICS_PKG.G_LOCAL_FS_USED,
STORAGE_REP_METRICS_PKG.G_LOCAL_FS_FREE
)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp
);
GET_APP_TIMESERIES_AS_CURSOR(l_storage_timeseries_rec_list,
p_summary_cursor);
GET_MIN_MAX_DATES(l_storage_timeseries_rec_list,
p_min_timeseries_date, p_max_timeseries_date);
END GET_HISTORY_LOCALFS_SUMMARY;
----------------------------------------------------------------------
FUNCTION GET_HISTORY_NFS_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN metric_column = STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_USED
THEN 'USED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_FREE
THEN 'FREE_STORAGE_ATTRIBUTE'
END storage_attribute,
average, maximum, minimum, last_known_value
FROM mgmt_v_storage_stats
WHERE metric_column IN (STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_USED,
STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_FREE)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type;
RETURN l_cursor;
END GET_HISTORY_NFS_STATS;
----------------------------------------------------------------------
PROCEDURE GET_HISTORY_NFS_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
l_storage_timeseries_rec_list STORAGE_TIMESERIES_REC_LIST;
BEGIN
SET_HIST_QUERY_ENV(p_target_name, p_target_type, p_period_in_days);
-- first try from the daily metric rollup table.
SELECT STORAGE_TIMESERIES_REC(rollup_timestamp, NULL, NULL,
NULL, used, free)
BULK COLLECT
INTO l_storage_timeseries_rec_list
FROM (
SELECT rollup_timestamp,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_USED, value,
NULL)) used,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_FREE, value,
NULL)) free
FROM MGMT_V_TIMESERIES_DAILY_HIST
WHERE metric_column IN
(
STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_USED,
STORAGE_REP_METRICS_PKG.G_WRITEABLE_NFS_FREE
)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp
);
GET_APP_TIMESERIES_AS_CURSOR(l_storage_timeseries_rec_list,
p_summary_cursor);
GET_MIN_MAX_DATES(l_storage_timeseries_rec_list,
p_min_timeseries_date, p_max_timeseries_date);
END GET_HISTORY_NFS_SUMMARY;
----------------------------------------------------------------------
FUNCTION GET_HISTORY_DISK_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN metric_column = STORAGE_REP_METRICS_PKG.G_DISKS_ALLOCATED
THEN 'ALLOCATED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_DISKS_UNALLOCATED
THEN 'UNALLOCATED_STORAGE_ATTRIBUTE'
END storage_attribute,
average, maximum, minimum, last_known_value
FROM mgmt_v_storage_stats
WHERE metric_column IN (STORAGE_REP_METRICS_PKG.G_DISKS_ALLOCATED,
STORAGE_REP_METRICS_PKG.G_DISKS_UNALLOCATED)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type;
RETURN l_cursor;
END GET_HISTORY_DISK_STATS;
----------------------------------------------------------------------
PROCEDURE GET_HISTORY_DISK_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
l_storage_timeseries_rec_list STORAGE_TIMESERIES_REC_LIST;
BEGIN
SET_HIST_QUERY_ENV(p_target_name, p_target_type, p_period_in_days);
-- first try from the daily metric rollup table.
SELECT STORAGE_TIMESERIES_REC(rollup_timestamp, allocated, unallocated,
NULL, NULL, NULL)
BULK COLLECT
INTO l_storage_timeseries_rec_list
FROM (
SELECT rollup_timestamp,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_DISKS_ALLOCATED, value,
NULL)) allocated,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_DISKS_UNALLOCATED, value,
NULL)) unallocated
FROM MGMT_V_TIMESERIES_DAILY_HIST
WHERE metric_column IN
(
STORAGE_REP_METRICS_PKG.G_DISKS_ALLOCATED,
STORAGE_REP_METRICS_PKG.G_DISKS_UNALLOCATED
)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp
);
GET_DISK_TIMESERIES_AS_CURSOR(l_storage_timeseries_rec_list,
p_summary_cursor);
GET_MIN_MAX_DATES(l_storage_timeseries_rec_list,
p_min_timeseries_date, p_max_timeseries_date);
END GET_HISTORY_DISK_SUMMARY;
----------------------------------------------------------------------
FUNCTION GET_PERIOD_IN_DAYS(p_period IN VARCHAR2) RETURN NUMBER IS
l_period_in_days NUMBER;
BEGIN
CASE
WHEN p_period = G_THREE_MONTHS THEN
l_period_in_days := G_DAYS_PER_MONTH * 3;
WHEN p_period = G_ONE_MONTH THEN
l_period_in_days := G_DAYS_PER_MONTH * 1;
WHEN p_period = G_ONE_YEAR THEN
l_period_in_days := G_DAYS_PER_MONTH * 12;
END CASE;
RETURN l_period_in_days;
END GET_PERIOD_IN_DAYS;
----------------------------------------------------------------------
FUNCTION GET_HISTORY_ASM_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN metric_column = STORAGE_REP_METRICS_PKG.G_ASM_ALLOCATED
THEN 'ALLOCATED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_ASM_UNALLOCATED
THEN 'UNALLOCATED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_ASM_OVERHEAD
THEN 'OVERHEAD_STORAGE_ATTRIBUTE'
END storage_attribute,
average, maximum, minimum, last_known_value
FROM mgmt_v_storage_stats
WHERE metric_column IN (STORAGE_REP_METRICS_PKG.G_ASM_ALLOCATED,
STORAGE_REP_METRICS_PKG.G_ASM_UNALLOCATED,
STORAGE_REP_METRICS_PKG.G_ASM_OVERHEAD)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type;
RETURN l_cursor;
END GET_HISTORY_ASM_STATS;
----------------------------------------------------------------------
PROCEDURE GET_HISTORY_ASM_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
l_storage_timeseries_rec_list STORAGE_TIMESERIES_REC_LIST;
BEGIN
SET_HIST_QUERY_ENV(p_target_name, p_target_type, p_period_in_days);
-- first try from the daily metric rollup table.
SELECT STORAGE_TIMESERIES_REC(rollup_timestamp, allocated, unallocated,
overhead, NULL, NULL)
BULK COLLECT
INTO l_storage_timeseries_rec_list
FROM (
SELECT rollup_timestamp,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_ASM_ALLOCATED, value,
NULL)) allocated,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_ASM_UNALLOCATED, value,
NULL)) unallocated,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_ASM_OVERHEAD, value,
NULL)) overhead
FROM MGMT_V_TIMESERIES_DAILY_HIST
WHERE metric_column IN
(
STORAGE_REP_METRICS_PKG.G_ASM_ALLOCATED,
STORAGE_REP_METRICS_PKG.G_ASM_UNALLOCATED,
STORAGE_REP_METRICS_PKG.G_ASM_OVERHEAD
)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp
);
GET_MGMT_LYR_TIMESRS_AS_CURSOR(l_storage_timeseries_rec_list,
p_summary_cursor);
GET_MIN_MAX_DATES(l_storage_timeseries_rec_list,
p_min_timeseries_date, p_max_timeseries_date);
END GET_HISTORY_ASM_SUMMARY;
----------------------------------------------------------------------
FUNCTION GET_HISTORY_VOL_STATS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_storage_unit IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT CASE WHEN metric_column = STORAGE_REP_METRICS_PKG.G_VOL_ALLOCATED
THEN 'ALLOCATED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_VOL_UNALLOCATED
THEN 'UNALLOCATED_STORAGE_ATTRIBUTE'
WHEN metric_column = STORAGE_REP_METRICS_PKG.G_VOL_OVERHEAD
THEN 'OVERHEAD_STORAGE_ATTRIBUTE'
END storage_attribute,
average, maximum, minimum, last_known_value
FROM mgmt_v_storage_stats
WHERE metric_column IN (STORAGE_REP_METRICS_PKG.G_VOL_ALLOCATED,
STORAGE_REP_METRICS_PKG.G_VOL_UNALLOCATED,
STORAGE_REP_METRICS_PKG.G_VOL_OVERHEAD)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type;
RETURN l_cursor;
END GET_HISTORY_VOL_STATS;
----------------------------------------------------------------------
PROCEDURE GET_HISTORY_VOL_SUMMARY(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_period_in_days IN NUMBER,
p_storage_unit IN VARCHAR2,
p_summary_cursor OUT SYS_REFCURSOR,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
l_storage_timeseries_rec_list STORAGE_TIMESERIES_REC_LIST;
BEGIN
SET_HIST_QUERY_ENV(p_target_name, p_target_type, p_period_in_days);
SELECT STORAGE_TIMESERIES_REC(rollup_timestamp, allocated,
unallocated, overhead, NULL, NULL)
BULK COLLECT
INTO l_storage_timeseries_rec_list
FROM (
SELECT rollup_timestamp,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_VOL_ALLOCATED, value,
NULL)) allocated,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_VOL_UNALLOCATED, value,
NULL)) unallocated,
MAX(DECODE(metric_column,
STORAGE_REP_METRICS_PKG.G_VOL_OVERHEAD, value,
NULL)) overhead
FROM MGMT_V_TIMESERIES_DAILY_HIST
WHERE metric_column IN
(
STORAGE_REP_METRICS_PKG.G_VOL_ALLOCATED,
STORAGE_REP_METRICS_PKG.G_VOL_UNALLOCATED,
STORAGE_REP_METRICS_PKG.G_VOL_OVERHEAD
)
AND metric_name = gv_storage_rep_metric
AND target_name = p_target_name
AND target_type = p_target_type
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp
);
GET_MGMT_LYR_TIMESRS_AS_CURSOR(l_storage_timeseries_rec_list,
p_summary_cursor);
GET_MIN_MAX_DATES(l_storage_timeseries_rec_list,
p_min_timeseries_date, p_max_timeseries_date);
END GET_HISTORY_VOL_SUMMARY;
----------------------------------------------------------------------
FUNCTION GET_HIST_COLLECTION_TIMESTAMP(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN DATE IS
l_collection_timestamp MGMT_CURRENT_METRICS.collection_timestamp%TYPE;
BEGIN
SELECT MAX(collection_timestamp)
INTO l_collection_timestamp
FROM MGMT_METRICS m,
MGMT_TARGETS t,
MGMT_CURRENT_METRICS c
WHERE m.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND t.target_guid = c.target_guid
AND t.target_name = p_target_name
AND t.target_type = p_target_type
AND m.metric_name = STORAGE_REP_METRICS_PKG.G_HOST_STORAGE_REP_METRIC
AND t.type_meta_ver = m.type_meta_ver
AND (m.category_prop_1 = ' ' OR m.category_prop_1 = t.category_prop_1)
AND (m.category_prop_2 = ' ' OR m.category_prop_2 = t.category_prop_2)
AND (m.category_prop_3 = ' ' OR m.category_prop_3 = t.category_prop_3)
AND (m.category_prop_4 = ' ' OR m.category_prop_4 = t.category_prop_4)
AND (m.category_prop_5 = ' ' OR m.category_prop_5 = t.category_prop_5)
AND m.metric_guid = c.metric_guid;
RETURN l_collection_timestamp;
END GET_HIST_COLLECTION_TIMESTAMP;
----------------------------------------------------------------------
PROCEDURE GET_MGMT_LYR_TIMESRS_AS_CURSOR(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_summary_cursor FOR
SELECT collection_timestamp, allocated, unallocated, overhead
FROM (
SELECT ts.collection_timestamp, ts.allocated,
ts.unallocated, ts.overhead
FROM TABLE(CAST(p_storage_timeseries_rec_list
as storage_timeseries_rec_list)) ts
);
END GET_MGMT_LYR_TIMESRS_AS_CURSOR;
----------------------------------------------------------------------
PROCEDURE GET_SUMM_TIMESERIES_AS_CURSOR(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_summary_cursor FOR
SELECT collection_timestamp, used, free, unallocated, overhead
FROM (
SELECT ts.collection_timestamp, ts.unallocated,
ts.overhead, ts.used, ts.free
FROM TABLE(CAST(p_storage_timeseries_rec_list
as storage_timeseries_rec_list)) ts
);
END GET_SUMM_TIMESERIES_AS_CURSOR;
----------------------------------------------------------------------
PROCEDURE GET_APP_TIMESERIES_AS_CURSOR(
p_storage_timeseries_rec_list in STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_summary_cursor FOR
SELECT collection_timestamp, used, free
FROM (
SELECT ts.collection_timestamp, ts.used, ts.free
FROM TABLE(CAST(p_storage_timeseries_rec_list
as storage_timeseries_rec_list)) ts
);
END GET_APP_TIMESERIES_AS_CURSOR;
----------------------------------------------------------------------
PROCEDURE GET_DISK_TIMESERIES_AS_CURSOR(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_summary_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_summary_cursor FOR
SELECT collection_timestamp, allocated, unallocated
FROM (
SELECT ts.collection_timestamp, ts.allocated, ts.unallocated
FROM TABLE(CAST(p_storage_timeseries_rec_list
as storage_timeseries_rec_list)) ts
);
END GET_DISK_TIMESERIES_AS_CURSOR;
----------------------------------------------------------------------
PROCEDURE GET_MIN_MAX_DATES(
p_storage_timeseries_rec_list IN STORAGE_TIMESERIES_REC_LIST,
p_min_timeseries_date OUT DATE,
p_max_timeseries_date OUT DATE) IS
BEGIN
SELECT min_date, max_date
INTO p_min_timeseries_date, p_max_timeseries_date
FROM (
-- if the minimum date and maxmum dates differ
-- by less than one, then we deliberately
-- reduce min date by one. This is because
-- the charts look ugly (with very thin bars)
-- for cases WHERE it is rendered for period
-- less than one day.
SELECT CASE WHEN max_date-min_date <= 1
THEN max_date-1
ELSE min_date
END min_date,
max_date
FROM (
-- in case there are no dates, we select
-- sysdates - otherwise charts dont get
-- rendered properly.
SELECT NVL(MIN(ts.collection_timestamp), SYSDATE) min_date,
NVL(MAX(ts.collection_timestamp), SYSDATE) max_date
FROM TABLE(CAST(p_storage_timeseries_rec_list
as storage_timeseries_rec_list)) ts
)
);
END GET_MIN_MAX_DATES;
----------------------------------------------------------------------
PROCEDURE DO_INS_TARGETS_FOR_UI_QUERIES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) IS
BEGIN
IF (p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
INSERT INTO MGMT_STORAGE_REPORT_UI_TARGETS(target_type,
target_name,
target_guid,
ecm_snapshot_id)
SELECT a.target_type, a.target_name,
a.target_guid, b.ecm_snapshot_id
FROM (
SELECT t.target_type, t.target_name,
t.target_guid, null ecm_snapshot_id
FROM MGMT_TARGETS t
WHERE t.target_name = p_target_name
AND t.target_type = p_target_type
) a,
(
SELECT t.target_type, t.target_name,
t.target_guid, snap.ecm_snapshot_id
FROM MGMT_TARGETS t,
MGMT$ECM_CURRENT_SNAPSHOTS snap
WHERE t.target_name = p_target_name
AND t.target_type = p_target_type
AND t.target_guid = snap.target_guid(+)
AND snap.snapshot_type = 'host_storage'
AND EXISTS
(
SELECT ecm_snapshot_id
FROM MGMT_STORAGE_REPORT_DATA
WHERE ecm_snapshot_id = snap.ecm_snapshot_id
)
) b
WHERE a.target_guid = b.target_guid(+);
ELSE
INSERT INTO MGMT_STORAGE_REPORT_UI_TARGETS(target_type,
target_name,
target_guid,
ecm_snapshot_id)
SELECT tgt.target_type, tgt.target_name,
tgt.target_guid, NULL ecm_snapshot_id
FROM MGMT_TARGETS tgt
WHERE target_name = p_target_name
AND target_type = p_target_type
UNION ALL
(
SELECT c.target_type, c.target_name,
c.target_guid, d.ecm_snapshot_id
FROM (
SELECT tgt.target_type,
tgt.target_name,
tgt.target_guid,
null ecm_snapshot_id
FROM MGMT_V_STRG_FLAT_MEMBERSHIPS members,
MGMT_TARGET_ASSOCS assoc,
MGMT_TARGET_ASSOC_DEFS assocdef,
MGMT_TARGETS tgt
WHERE composite_target_name = p_target_name
AND composite_target_type = p_target_type
AND assoc.source_target_guid = members.member_target_guid
AND assoc.assoc_guid = assocdef.assoc_guid
AND assocdef.assoc_def_name = 'hosted_by'
AND assoc.assoc_target_guid = tgt.target_guid
) c,
(
SELECT tgt.target_type,
tgt.target_name,
tgt.target_guid,
snap.ecm_snapshot_id
FROM MGMT_V_STRG_FLAT_MEMBERSHIPS members,
MGMT_TARGET_ASSOCS assoc,
MGMT_TARGET_ASSOC_DEFS assocdef,
MGMT_TARGETS tgt,
MGMT$ECM_CURRENT_SNAPSHOTS snap
WHERE composite_target_name = p_target_name
AND composite_target_type = p_target_type
AND assoc.source_target_guid = members.member_target_guid
AND assoc.assoc_guid = assocdef.assoc_guid
AND assocdef.assoc_def_name = 'hosted_by'
AND assoc.assoc_target_guid = tgt.target_guid
AND tgt.target_guid = snap.target_guid(+)
AND snap.snapshot_type = 'host_storage'
AND EXISTS
(
SELECT ecm_snapshot_id
FROM MGMT_STORAGE_REPORT_DATA
WHERE ecm_snapshot_id = snap.ecm_snapshot_id
)
) d
WHERE c.target_guid = d.target_guid(+)
UNION
-- finally insert all direct members that are hosts
SELECT e.target_type, e.target_name,
e.target_guid, f.ecm_snapshot_id
FROM (
SELECT members.member_target_type target_type,
members.member_target_name target_name,
members.member_target_guid target_guid,
null ecm_snapshot_id
FROM MGMT_V_STRG_FLAT_MEMBERSHIPS members,
MGMT$ECM_CURRENT_SNAPSHOTS snap
WHERE composite_target_name = p_target_name
AND composite_target_type = p_target_type
AND member_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
) e,
(
SELECT members.member_target_type target_type,
members.member_target_name target_name,
members.member_target_guid target_guid,
snap.ecm_snapshot_id
FROM MGMT_V_STRG_FLAT_MEMBERSHIPS members,
MGMT$ECM_CURRENT_SNAPSHOTS snap
WHERE composite_target_name = p_target_name
AND composite_target_type = p_target_type
AND member_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND member_target_guid = snap.target_guid(+)
AND snap.snapshot_type = 'host_storage'
AND EXISTS
(
SELECT ecm_snapshot_id
FROM MGMT_STORAGE_REPORT_DATA
WHERE ecm_snapshot_id = snap.ecm_snapshot_id
)
) f
WHERE e.target_guid = f.target_guid(+)
);
END IF;
END DO_INS_TARGETS_FOR_UI_QUERIES;
----------------------------------------------------------------------
PROCEDURE SET_HIST_DATA_EXISTS_FLAG(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_hist_data_exists_flag OUT VARCHAR2) IS
l_hist_metric_name MGMT_METRICS.metric_name%TYPE;
BEGIN
IF(p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
l_hist_metric_name :=
STORAGE_REP_METRICS_PKG.G_HOST_STORAGE_REP_METRIC;
ELSE
l_hist_metric_name :=
STORAGE_REP_METRICS_PKG.G_GROUP_STORAGE_REP_METRIC;
END IF;
SELECT DECODE(COUNT(*),
0, 'N',
'Y') history_data_exists
INTO p_hist_data_exists_flag
FROM DUAL
WHERE EXISTS
(
SELECT NULL
FROM MGMT_CURRENT_METRICS c,
MGMT_METRICS m,
MGMT_TARGETS t,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE m.metric_guid = c.metric_guid
AND m.metric_name = l_hist_metric_name
AND c.target_guid = t.target_guid
AND t.target_name = uit.target_name
AND t.target_type = uit.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = ' ' OR
m.category_prop_1 = t.category_prop_1)
AND (m.category_prop_2 = ' ' OR
m.category_prop_2 = t.category_prop_2)
AND (m.category_prop_3 = ' ' OR
m.category_prop_3 = t.category_prop_3)
AND (m.category_prop_4 = ' ' OR
m.category_prop_4 = t.category_prop_4)
AND (m.category_prop_5 = ' ' OR
m.category_prop_5 = t.category_prop_5)
);
END SET_HIST_DATA_EXISTS_FLAG;
----------------------------------------------------------------------
--
-- Gets usage summary storage values in byte size numbers.
--
-- p_calc_storage_source_flag - flag that tells if we need
-- to calculate values of storage source
-- for db (i.e. values in the
-- following OUT parameters):
--
-- p_db_on_local_disks - database storage from disks
-- p_db_on_volumes - database storage from volumes
-- p_db_on_asm - database storage from asm
-- p_db_on_local_fs database storage from local FS
-- p_db_on_writeable_nfs - database storage from NFS
--
-- It is set to TRUE when executed from UI.
-- It is set to FALSE for repository metric collections.
--
PROCEDURE GET_USAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_calc_storage_source_flag IN NUMBER,
p_db_on_local_disks OUT NUMBER,
p_db_on_volumes OUT NUMBER,
p_db_on_asm OUT NUMBER,
p_db_on_local_fs OUT NUMBER,
p_db_on_writeable_nfs OUT NUMBER,
p_db_allocated OUT NUMBER,
p_db_tablespace_size OUT NUMBER,
p_db_tablespace_used OUT NUMBER,
p_db_used OUT NUMBER,
p_db_free OUT NUMBER,
p_local_fs_on_local_disks OUT NUMBER,
p_local_fs_on_volumes OUT NUMBER,
p_local_fs_used OUT NUMBER,
p_local_fs_free OUT NUMBER,
p_writeable_nfs_used OUT NUMBER,
p_writeable_nfs_free OUT NUMBER) IS
l_db_allocated NUMBER;
l_db_used NUMBER;
l_db_free NUMBER;
BEGIN
IF (p_calc_storage_source_flag = MGMT_GLOBAL.G_TRUE) THEN
GET_DB_STORAGE_SOURCE(p_db_on_local_disks => p_db_on_local_disks,
p_db_on_volumes => p_db_on_volumes,
p_db_on_asm => p_db_on_asm,
p_db_on_local_fs => p_db_on_local_fs,
p_db_on_writeable_nfs => p_db_on_writeable_nfs);
END IF;
GET_DB_TABLESPACE_SIZE_USED(p_tablespace_size => p_db_tablespace_size,
p_tablespace_used => p_db_tablespace_used);
GET_DB_ALLOC_USED_FREE(p_db_allocated => p_db_allocated,
p_db_used => p_db_used,
p_db_free => p_db_free);
IF (p_calc_storage_source_flag = MGMT_GLOBAL.G_TRUE) THEN
GET_VOL_DISK_USED_BY_LAYER(p_storage_layer => G_LOCAL_FILESYSTEM,
p_vol_allocated_by_layer => p_local_fs_on_volumes,
p_disk_allocated_by_layer => p_local_fs_on_local_disks);
END IF;
GET_LOCAL_FS_NFS_USED_FREE(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);
/* With changes done by Ajay earlier, I no longer need
to calculate the LOCAL FS FREE as below. Morever, In the
case of history calculation, we no longer calculate
p_local_fs_on_volumes and p_local_fs_on_local_disks -
so the logic below would yield incorrect value for
local fs free. Hence commenting it out (May 05, 2005)
-- local fs free, if calculated in the same query as used, is inaccurate.
-- I have to subtract the used from total to get the correct value.
IF(NOT(p_local_fs_on_volumes IS NULL AND
p_local_fs_on_local_disks IS NULL AND
p_local_fs_used IS NULL)) THEN
p_local_fs_free := (NVL(p_local_fs_on_volumes,0) +
NVL(p_local_fs_on_local_disks, 0)) -
NVL(p_local_fs_used, 0);
END IF;
*/
END GET_USAGE_SUMMARY_VALUES;
----------------------------------------------------------------------
PROCEDURE GET_ALLOCATION_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_vol_allocated OUT NUMBER,
p_vol_overhead OUT NUMBER,
p_vol_unallocated OUT NUMBER,
p_vol_size OUT NUMBER,
p_asm_allocated OUT NUMBER,
p_asm_unallocated OUT NUMBER,
p_asm_overhead OUT NUMBER,
p_disk_allocated OUT NUMBER,
p_disk_unallocated OUT NUMBER,
p_disk_size OUT NUMBER,
p_asm_size OUT NUMBER,
p_writeable_nfs_allocated OUT NUMBER) IS
BEGIN
GET_VOL_DISK_ALLOCATED(p_disk_allocated => p_disk_allocated,
p_vol_allocated => p_vol_allocated);
GET_VOL_DISK_UNALLOCATED(p_disk_unallocated => p_disk_unallocated,
p_vol_unallocated => p_vol_unallocated);
GET_VOL_DISK_NFS_SIZE(p_disk_size => p_disk_size,
p_vol_size => p_vol_size,
p_writeable_nfs_allocated => p_writeable_nfs_allocated);
IF (NOT(p_vol_size IS NULL AND
p_vol_allocated IS NULL AND
p_vol_unallocated IS NULL))
THEN
p_vol_overhead := NVL(p_vol_size,0) -
(NVL(p_vol_allocated, 0) + NVL(p_vol_unallocated, 0));
END IF;
GET_ASM_ALLOC_UNALLOC_OVERHEAD(p_asm_allocated => p_asm_allocated,
p_asm_unallocated => p_asm_unallocated,
p_asm_overhead => p_asm_overhead);
END GET_ALLOCATION_SUMMARY_VALUES;
----------------------------------------------------------------------
FUNCTION GET_STORAGE_COLLECTION_ERRORS(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_result_cursor SYS_REFCURSOR;
BEGIN
OPEN l_result_cursor FOR
SELECT errors.target_name, types.type_display_name, errors.metric_label,
errors.collection_timestamp, errors.metric_collection_error_msg,
errors.target_type
FROM MGMT_V_STORAGE_COLL_ERRORS errors,
MGMT_TARGET_TYPES types
WHERE errors.target_type = types.target_type
ORDER BY errors.target_name, errors.target_type, errors.collection_timestamp;
RETURN l_result_cursor;
END GET_STORAGE_COLLECTION_ERRORS;
----------------------------------------------------------------------
FUNCTION GET_STORAGE_MAPPING_ISSUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN SYS_REFCURSOR IS
l_result_cursor SYS_REFCURSOR;
BEGIN
OPEN l_result_cursor FOR
SELECT uit.target_name, i.type, i.message_id, i.message_params,
i.action_id, i.action_params, snap.saved_timestamp
FROM MGMT_STORAGE_REPORT_ISSUES i,
MGMT_STORAGE_REPORT_UI_TARGETS uit,
MGMT_ECM_GEN_SNAPSHOT snap
WHERE uit.ecm_snapshot_id = i.ecm_snapshot_id
AND i.ecm_snapshot_id = snap.snapshot_guid;
RETURN l_result_cursor;
END GET_STORAGE_MAPPING_ISSUES;
----------------------------------------------------------------------
FUNCTION GET_SUMMARIZED_ASM_COUNT(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN NUMBER IS
l_summarized_asm_count NUMBER;
BEGIN
-- The algorithm is that if for a target any one of the
-- ASM metrics (currently we only consider DiskGroup_Usage
-- metric) has been collected, then it is considered summarized.
-- In future, we need to make changes during storage calculation
-- to have a check that would prevent an ASM target to be summarized
-- when perhaps any one of the metrics is not collected.
SELECT COUNT (DISTINCT t.target_guid)
INTO l_summarized_asm_count
FROM MGMT_CURRENT_METRICS c,
MGMT_TARGETS t,
MGMT_METRICS m,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE t.target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND uit.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
AND uit.target_name = t.host_name
AND t.target_type = m.target_type
AND t.type_meta_ver = m.type_meta_ver
AND m.metric_name = G_ASM_DG_USAGE_METRIC
AND m.metric_column IN ('total_mb', 'free_mb','usable_file_mb', 'type')
AND (m.category_prop_1 = ' ' OR m.category_prop_1 = t.category_prop_1)
AND (m.category_prop_2 = ' ' OR m.category_prop_2 = t.category_prop_2)
AND (m.category_prop_3 = ' ' OR m.category_prop_3 = t.category_prop_3)
AND (m.category_prop_4 = ' ' OR m.category_prop_4 = t.category_prop_4)
AND (m.category_prop_5 = ' ' OR m.category_prop_5 = t.category_prop_5)
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid;
RETURN l_summarized_asm_count;
END GET_SUMMARIZED_ASM_COUNT;
----------------------------------------------------------------------
PROCEDURE GET_STORAGE_ISSUES_COUNT(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_unmonitored_nfs_count OUT NUMBER,
p_other_issues_count OUT NUMBER) IS
BEGIN
SELECT SUM(DECODE(message_id,
'ERROR_REP_TGT_NOT_MNTRD', 1,
0)) unmonitored_nfs_issue_count,
SUM(DECODE(message_id,
'ERROR_REP_TGT_NOT_MNTRD', 0,
1)) other_issues_count
INTO p_unmonitored_nfs_count,
p_other_issues_count
FROM MGMT_STORAGE_REPORT_ISSUES i,
MGMT_STORAGE_REPORT_UI_TARGETS uit
WHERE uit.ecm_snapshot_id = i.ecm_snapshot_id;
END GET_STORAGE_ISSUES_COUNT;
----------------------------------------------------------------------
--
-- Returns refresh job info if it is not yet completed.
--
PROCEDURE GET_REFRESH_JOB_INFO(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_refresh_job_name OUT MGMT_JOB.job_name%TYPE,
p_refresh_job_exec_id OUT MGMT_JOB_EXEC_SUMMARY.job_id%TYPE,
p_refresh_job_status OUT MGMT_JOB_EXEC_SUMMARY.status%TYPE) IS
l_job_id MGMT_JOB.job_id%TYPE;
BEGIN
IF(p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
p_refresh_job_status := G_NO_REFRESH_JOB_IS_RUNNING;
RETURN;
END IF;
-- Get the latest job executed for this group
-- query on job description
BEGIN
SELECT job_id, job_name
INTO l_job_id, p_refresh_job_name
FROM (
SELECT RANK() OVER( ORDER BY b.start_time DESC ) rk,
a.job_id, a.job_name
FROM MGMT$JOBS a,
MGMT_JOB_EXEC_SUMMARY b
WHERE a.job_description = STORAGE_UI_GROUP_PKG.G_GROUP_JOB_DESC_PREFIX||p_target_name
AND a.job_type = storage_ui_group_pkg.G_GROUP_REFRESH_JOB_TYPE
AND a.job_id = b.job_id
)
WHERE rk = 1
AND ROWNUM = 1;
EXCEPTION
-- No job ever submitted for this target
WHEN NO_DATA_FOUND THEN
p_refresh_job_status := G_NO_REFRESH_JOB_IS_RUNNING;
p_refresh_job_name := NULL;
RETURN;
END;
-- If a job was submitted for this group
-- check for active jobs
BEGIN
SELECT status, execution_id
INTO p_refresh_job_status, p_refresh_job_exec_id
FROM mgmt_job_exec_summary
WHERE job_id = l_job_id
AND status NOT IN
(
mgmt_jobs.COMPLETED_STATUS,
mgmt_jobs.FAILED_STATUS,
mgmt_jobs.ABORTED_STATUS,
mgmt_jobs.STOPPED_STATUS,
mgmt_jobs.SKIPPED_STATUS,
mgmt_jobs.DELETE_PENDING_STATUS
)
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_refresh_job_status := G_NO_REFRESH_JOB_IS_RUNNING;
p_refresh_job_name := NULL;
END;
END GET_REFRESH_JOB_INFO;
----------------------------------------------------------------------
PROCEDURE LOG_STORAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_vol_size IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_disk_size IN NUMBER,
p_asm_size IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_db_on_local_disks IN NUMBER,
p_db_on_volumes IN NUMBER,
p_db_on_asm IN NUMBER,
p_db_on_local_fs IN NUMBER,
p_db_on_writeable_nfs IN NUMBER,
p_db_allocated IN NUMBER,
p_db_tablespace_size IN NUMBER,
p_db_tablespace_used IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_on_local_disks IN NUMBER,
p_local_fs_on_volumes IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_total_allocated IN NUMBER,
p_total_unallocated IN NUMBER,
p_total_overhead IN NUMBER,
p_total_used IN NUMBER,
p_total_free IN NUMBER) IS
BEGIN
EMDW_LOG.DEBUG('Storage summary values begin' , G_MODULE_NAME);
LOG_DASHED_LINE;
EMDW_LOG.DEBUG('p_vol_allocated: ' || p_vol_allocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_vol_overhead: ' || p_vol_overhead , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_vol_unallocated: ' || p_vol_unallocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_vol_size: ' || p_vol_size , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_asm_allocated: ' || p_asm_allocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_asm_unallocated: ' || p_asm_unallocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_asm_overhead: ' || p_asm_overhead , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_disk_allocated: ' || p_disk_allocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_disk_unallocated: ' || p_disk_unallocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_disk_size: ' || p_disk_size , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_asm_size: ' || p_asm_size , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_writeable_nfs_allocated: ' || p_writeable_nfs_allocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_on_local_disks: ' || p_db_on_local_disks , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_on_volumes: ' || p_db_on_volumes , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_on_asm: ' || p_db_on_asm , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_on_local_fs: ' || p_db_on_local_fs , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_on_writeable_nfs: ' || p_db_on_writeable_nfs , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_allocated: ' || p_db_allocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_tablespace_size: ' || p_db_tablespace_size , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_tablespace_used: ' || p_db_tablespace_used , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_used: ' || p_db_used , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_db_free: ' || p_db_free , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_local_fs_on_local_disks: ' || p_local_fs_on_local_disks , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_local_fs_on_volumes: ' || p_local_fs_on_volumes , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_local_fs_used: ' || p_local_fs_used , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_local_fs_free: ' || p_local_fs_free , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_writeable_nfs_used: ' || p_writeable_nfs_used , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_writeable_nfs_free: ' || p_writeable_nfs_free , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_total_allocated: ' || p_total_allocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_total_unallocated: ' || p_total_unallocated , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_total_overhead: ' || p_total_overhead , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_total_used: ' || p_total_used , G_MODULE_NAME);
EMDW_LOG.DEBUG('p_total_free: ' || p_total_free , G_MODULE_NAME);
LOG_DASHED_LINE;
EMDW_LOG.DEBUG(' Storage summary values end.' , G_MODULE_NAME);
END LOG_STORAGE_SUMMARY_VALUES;
----------------------------------------------------------------------
PROCEDURE LOG_DASHED_LINE IS
BEGIN
DBMS_OUTPUT.PUT_LINE('---------------------------');
END LOG_DASHED_LINE;
----------------------------------------------------------------------
PROCEDURE PRINT_STORAGE_SUMMARY_VALUES(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_vol_allocated IN NUMBER,
p_vol_overhead IN NUMBER,
p_vol_unallocated IN NUMBER,
p_vol_size IN NUMBER,
p_asm_allocated IN NUMBER,
p_asm_unallocated IN NUMBER,
p_asm_overhead IN NUMBER,
p_disk_allocated IN NUMBER,
p_disk_unallocated IN NUMBER,
p_disk_size IN NUMBER,
p_asm_size IN NUMBER,
p_writeable_nfs_allocated IN NUMBER,
p_db_on_local_disks IN NUMBER,
p_db_on_volumes IN NUMBER,
p_db_on_asm IN NUMBER,
p_db_on_local_fs IN NUMBER,
p_db_on_writeable_nfs IN NUMBER,
p_db_allocated IN NUMBER,
p_db_tablespace_size IN NUMBER,
p_db_tablespace_used IN NUMBER,
p_db_used IN NUMBER,
p_db_free IN NUMBER,
p_local_fs_on_local_disks IN NUMBER,
p_local_fs_on_volumes IN NUMBER,
p_local_fs_used IN NUMBER,
p_local_fs_free IN NUMBER,
p_writeable_nfs_used IN NUMBER,
p_writeable_nfs_free IN NUMBER,
p_total_allocated IN NUMBER,
p_total_unallocated IN NUMBER,
p_total_overhead IN NUMBER,
p_total_used IN NUMBER,
p_total_free IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Storage summary values begin');
PRINT_DASHED_LINE;
DBMS_OUTPUT.PUT_LINE('p_vol_allocated: ' || p_vol_allocated);
DBMS_OUTPUT.PUT_LINE('p_vol_overhead: ' || p_vol_overhead);
DBMS_OUTPUT.PUT_LINE('p_vol_unallocated: ' || p_vol_unallocated);
DBMS_OUTPUT.PUT_LINE('p_vol_size: ' || p_vol_size);
DBMS_OUTPUT.PUT_LINE('p_asm_allocated: ' || p_asm_allocated);
DBMS_OUTPUT.PUT_LINE('p_asm_unallocated: ' || p_asm_unallocated);
DBMS_OUTPUT.PUT_LINE('p_asm_overhead: ' || p_asm_overhead);
DBMS_OUTPUT.PUT_LINE('p_disk_allocated: ' || p_disk_allocated);
DBMS_OUTPUT.PUT_LINE('p_disk_unallocated: ' || p_disk_unallocated);
DBMS_OUTPUT.PUT_LINE('p_disk_size: ' || p_disk_size);
DBMS_OUTPUT.PUT_LINE('p_asm_size: ' || p_asm_size);
DBMS_OUTPUT.PUT_LINE('p_writeable_nfs_allocated: ' || p_writeable_nfs_allocated);
DBMS_OUTPUT.PUT_LINE('p_db_on_local_disks: ' || p_db_on_local_disks);
DBMS_OUTPUT.PUT_LINE('p_db_on_volumes: ' || p_db_on_volumes);
DBMS_OUTPUT.PUT_LINE('p_db_on_asm: ' || p_db_on_asm);
DBMS_OUTPUT.PUT_LINE('p_db_on_local_fs: ' || p_db_on_local_fs);
DBMS_OUTPUT.PUT_LINE('p_db_on_writeable_nfs: ' || p_db_on_writeable_nfs);
DBMS_OUTPUT.PUT_LINE('p_db_allocated: ' || p_db_allocated);
DBMS_OUTPUT.PUT_LINE('p_db_tablespace_size: ' || p_db_tablespace_size);
DBMS_OUTPUT.PUT_LINE('p_db_tablespace_used: ' || p_db_tablespace_used);
DBMS_OUTPUT.PUT_LINE('p_db_used: ' || p_db_used);
DBMS_OUTPUT.PUT_LINE('p_db_free: ' || p_db_free);
DBMS_OUTPUT.PUT_LINE('p_local_fs_on_local_disks: ' || p_local_fs_on_local_disks);
DBMS_OUTPUT.PUT_LINE('p_local_fs_on_volumes: ' || p_local_fs_on_volumes);
DBMS_OUTPUT.PUT_LINE('p_local_fs_used: ' || p_local_fs_used);
DBMS_OUTPUT.PUT_LINE('p_local_fs_free: ' || p_local_fs_free);
DBMS_OUTPUT.PUT_LINE('p_writeable_nfs_used: ' || p_writeable_nfs_used);
DBMS_OUTPUT.PUT_LINE('p_writeable_nfs_free: ' || p_writeable_nfs_free);
DBMS_OUTPUT.PUT_LINE('p_total_allocated: ' || p_total_allocated);
DBMS_OUTPUT.PUT_LINE('p_total_unallocated: ' || p_total_unallocated);
DBMS_OUTPUT.PUT_LINE('p_total_overhead: ' || p_total_overhead);
DBMS_OUTPUT.PUT_LINE('p_total_used: ' || p_total_used);
DBMS_OUTPUT.PUT_LINE('p_total_free: ' || p_total_free);
PRINT_DASHED_LINE;
DBMS_OUTPUT.PUT_LINE(' Storage summary values end.');
END PRINT_STORAGE_SUMMARY_VALUES;
----------------------------------------------------------------------
PROCEDURE PRINT_DASHED_LINE IS
BEGIN
DBMS_OUTPUT.PUT_LINE('---------------------------');
END PRINT_DASHED_LINE;
----------------------------------------------------------------------
FUNCTION IS_HISTORY_METRIC_DEFINED(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2) RETURN VARCHAR2 IS
l_history_metric_defined_flag VARCHAR2(2);
l_history_metric_name MGMT_METRICS.metric_name%TYPE;
BEGIN
l_history_metric_name := STORAGE_REP_METRICS_PKG.G_GROUP_STORAGE_REP_METRIC;
IF(p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
l_history_metric_name := STORAGE_REP_METRICS_PKG.G_HOST_STORAGE_REP_METRIC;
END IF;
SELECT DECODE(COUNT(*),
0,STORAGE_UI_UTIL_PKG.NO,
STORAGE_UI_UTIL_PKG.YES)
INTO l_history_metric_defined_flag
FROM MGMT_METRICS m,
MGMT_TARGETS t
WHERE m.target_type = p_target_type
AND m.type_meta_ver = t.type_meta_ver
AND m.metric_name = l_history_metric_name
AND t.target_name = p_target_name
AND t.target_type = m.target_type
AND (m.category_prop_1 = ' ' OR m.category_prop_1 = t.category_prop_1)
AND (m.category_prop_2 = ' ' OR m.category_prop_2 = t.category_prop_2)
AND (m.category_prop_3 = ' ' OR m.category_prop_3 = t.category_prop_3)
AND (m.category_prop_4 = ' ' OR m.category_prop_4 = t.category_prop_4)
AND (m.category_prop_5 = ' ' OR m.category_prop_5 = t.category_prop_5);
RETURN l_history_metric_defined_flag;
END IS_HISTORY_METRIC_DEFINED;
----------------------------------------------------------------------
END STORAGE_UI_COMMON_PKG;
/
SHOW ERRORS;