Rem
Rem $Header: storage_ui_host_views.sql 26-jul-2005.01:03:58 sreddy Exp $
Rem
Rem storage_report_views.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      storage_ui_host_views.sql - 
Rem        contains views used in getting host storage info for
Rem        storage reports UI code.
Rem
Rem    DESCRIPTION
Rem      views used to get data for host storage UI.
Rem
Rem    NOTES
Rem      NOTE 1: The views with names beginning with "mgmt_v" 
Rem              must not to be exposed as public views. 
Rem      Note 2: Many of the "mgmt_v" views are parameterized views -
Rem              i.e. they pick some of their where clause binds
Rem              from sys_context variables that must be set
Rem              for them to return correct answers.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sreddy      07/23/05 - Fix the views using 
Rem                           mgmt_v_storage_report_data and filter out
Rem                           non current snapshot rows from computations
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    rmenon      02/23/05 - file systems writeable flag needs
Rem                           to return different values based
Rem                           on various criteria.
Rem    rmenon      02/10/05 - 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    rmenon      02/04/05 - 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    rmenon      12/17/04 - corrected mgmt_v_host_fs_list, if
Rem                           nfs_mount_privilege is null, we now assume
Rem                           that the mount is not writeable
Rem    rmenon      12/03/04 - modified mgmt_v_host_fs_list as per
Rem                           Ajay's suggestion.
Rem    rmenon      10/28/04 - 
Rem    rmenon      10/13/04 - 
Rem    rmenon      09/20/04 - 
Rem    rmenon      09/08/04 - groups storage views - perhaps
Rem                           they should go in a different file
Rem                           will do that next time I touch this
Rem                           file.
Rem    rmenon      09/02/04 - The ora-600 internal error came back.
Rem                           This time the error triggered only in 
Rem                           Abhijit Sawant's machine as far as I know.
Rem                           The culprit view was the same one
Rem                           i.e. mgmt_v_host_db_storage. We noticed that
Rem                           it was using a "with" clause and a union.
Rem                           We refactored the with clause into a
Rem                           separate view;ended up merging this new
Rem                           view with an existing view mgmt_v_host_dbs
Rem                           with minor changes.
Rem    rmenon      08/26/04 - 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    ajdsouza    08/20/04 - 
Rem    rmenon      08/16/04 - 
Rem    ajdsouza    08/10/04 - ajdsouza_ecm_processing
Rem    ajdsouza    08/03/04 - 
Rem    rmenon      08/02/04 - rmenon_storage_refresh_error_page_checkin
Rem    rmenon      07/20/04 - Created
Rem

DEFINE LOCAL_FILESYSTEM            = LOCAL_FILESYSTEM
DEFINE NFS                         = NFS
DEFINE OS_DISK                     = OS_DISK
DEFINE VOLUME_MANAGER              = VOLUME_MANAGER
DEFINE MOUNT_POINT                 = Mountpoint
DEFINE SWAP                        = swap

---------------------------------------------------------
-- NAME: mgmt_v_host_disk_list
--
-- PURPOSE: 
--    This view gives the list of disks and disk partitions
--    on a host.
--
-- IMPLMENTATION NOTES:
--   NOTE: For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
-----------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_host_disk_list
AS
  SELECT target_name, 
         target_type, 
         ecm_snapshot_id,
         global_unique_id,
         resource_name, 
         resource_type, 
         file_type AS device_type,
         disk_vendor AS vendor, 
         storage_size, 
         allocated, 
         unallocated,
         count(DISTINCT target_name) 
           OVER( PARTITION BY target_type, global_unique_id ) 
           AS shared_count
  FROM 
  (
    SELECT data.global_unique_id,
           nvl(data.disk_used_path, name) as resource_name,
           data.entity_type AS resource_type, 
           file_type,
           data.disk_vendor, 
           nvl(data.sizeb,0) AS storage_size,
           nvl(data.usedb,0) AS allocated,
           nvl(data.freeb,0) AS unallocated,
           data.target_name, data.target_type,
           data.ecm_snapshot_id as ecm_snapshot_id
    FROM mgmt_v_storage_report_data data,
         mgmt$ecm_current_snapshots snap
    WHERE snap.ecm_snapshot_id = data.ecm_snapshot_id
      AND data.storage_layer = '&OS_DISK'
  );

---------------------------------------------------------
-- NAME: mgmt_v_host_vol_list
--
-- PURPOSE: 
--    This view gives the list of volumes on a host.
--
-- IMPLMENTATION NOTES:
--   NOTE : For this view to work, you need to populate the temporary
--         table mgmt_storage_report_ui_targets
--         using the method 
--         storage_ui_common_pkg.insert_targets_for_ui_queries.
--   Note 2: Note that we always count across all hosts in
--           EM to get the shared_count regardless of whether
--           this query is being executed for a group or not.
-----------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_host_vol_list
AS
  SELECT target_name, 
         target_type, 
         ecm_snapshot_id,
         global_unique_id, 
         resource_name, 
         resource_type, 
         disk_group,
         os_path,
         vendor, 
         product, 
         storage_size, 
         allocated, 
         unallocated,
         configuration,
         count(DISTINCT target_name)  -- across all hosts in EM
           OVER( PARTITION BY target_type, global_unique_id ) 
           AS shared_count
  FROM 
  (
    SELECT data.global_unique_id,
           data.name AS resource_name, 
           data.entity_type AS resource_type, 
           volume_manager_disk_group disk_group,
           data.volume_manager_vendor AS vendor, 
           data.volume_manager_product AS product, 
           data.volume_manager_used_path AS os_path,
           nvl(data.sizeb,0) AS storage_size,
           nvl(data.usedb,0) AS allocated,
           nvl(data.freeb,0) AS unallocated,
           nvl( data.volume_manager_configuration,
                'NOT_APPLICABLE' ) AS configuration, 
           data.target_name, 
           data.target_type,
           data.ecm_snapshot_id as ecm_snapshot_id
    FROM mgmt_v_storage_report_data data,
         mgmt$ecm_current_snapshots snap
    WHERE snap.ecm_snapshot_id = data.ecm_snapshot_id
      AND data.storage_layer = '&VOLUME_MANAGER'
  );

---------------------------------------------------------
-- NAME: mgmt_v_host_fs_list
--
-- PURPOSE: 
--    This view gives the list of file systems on a host.
--
-- IMPLMENTATION NOTES:
--   NOTE : For this view to work, you need to populate the temporary
--          table mgmt_storage_report_ui_targets
--          using the method:
--          storage_ui_common_pkg.insert_targets_for_ui_queries.
--   Note 2: Note that we always count across all hosts in
--           EM to get the shared_count regardless of whether
--           this query is being executed for a group or not.
-----------------------------------------------------------

CREATE OR REPLACE VIEW mgmt_v_host_fs_list
AS
  SELECT global_unique_id,
         resource_name,
         resource_type,
         mount_point,
         storage_size,
         used,
         writeable,
         target_type,
         target_name,
         ecm_snapshot_id,
         count(DISTINCT target_name)
           OVER( PARTITION BY target_type, global_unique_id )
           AS shared_count
  FROM
  (
    SELECT global_unique_id,
           decode( storage_layer, '&LOCAL_FILESYSTEM', local_fs,
                   '&NFS', nfs ) AS resource_name,
           decode( storage_layer, '&LOCAL_FILESYSTEM', local_fs_type,
                   '&NFS', 'nfs' ) AS resource_type,
           decode( storage_layer, '&LOCAL_FILESYSTEM', local_fs_mountpoint,
                   '&NFS', nfs_mountpoint ) AS mount_point,
           nvl(sizeb,0) AS storage_size,
           nvl(usedb,0) AS used,
           CASE 
             WHEN storage_layer = '&LOCAL_FILESYSTEM' THEN
               'NOT_APPLICABLE'
             WHEN  storage_layer = '&NFS' THEN
               decode( instr(nfs_mount_privilege, 'WRITE'), 
                  null, 'UNKNOWN', 0, 'NO', 'YES' ) 
           END AS writeable,
           data.target_type,
           data.target_name,
           data.ecm_snapshot_id as ecm_snapshot_id
    FROM mgmt_v_storage_report_data data,
         mgmt$ecm_current_snapshots snap
    WHERE snap.ecm_snapshot_id = data.ecm_snapshot_id
      AND storage_layer in( '&LOCAL_FILESYSTEM', '&NFS' )
      AND ( entity_type = '&MOUNT_POINT'
            OR local_fs_type = '&SWAP' )
  );