Rem drv: <create type="views" pos="config/config_views.sql+"/>
Rem
Rem $Header: config_gc_views.sql 12-aug-2006.18:00:37 bmallipe Exp $
Rem
Rem config_gc_views.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      config_gc_views.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bmallipe    08/12/06 - removing the CENTRAL condition for
Rem                           mgmt_database_overview compilation
Rem    chyu        05/08/06 - RCU Compliant and RepManager enhancement 
Rem    rreilly     03/18/05 - remove dbrollup and controlfiles_size 
Rem    xshen       03/14/05 - handle target deletion 
Rem    xshen       01/13/05 - update views 
Rem    xshen       11/18/04 - bug 4017776
Rem    akskumar    09/17/04 - akskumar_generic_database_view_snapshot_and_diff_and_fixes
Rem    akskumar    09/15/04 - Created
Rem

  
----------------------------------------------------------------------
-- Database Configuration Snapshot Views for saved and latest both  --
----------------------------------------------------------------------
  
CREATE OR REPLACE VIEW MGMT$DB_DBNINSTANCEINFO_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  i.database_name,
  i.global_name,
  i.banner,
  i.host_name as host,
  i.instance_name,
  i.startup_time,
  i.logins,
  i.log_mode,
  i.open_mode,
  i.default_temp_tablespace,
  i.characterset,
  i.national_characterset
from
  mgmt_targets g,
  mgmt_db_dbninstanceinfo_ecm i,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = i.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);
  
  
CREATE OR REPLACE VIEW MGMT$DB_TABLESPACES_ALL AS 
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  t.tablespace_name,
  t.contents,
  t.status,
  t.extent_management,
  t.allocation_type,
  t.logging,
  t.tablespace_size,
  t.initial_ext_size,
  t.next_extent,
  t.increment_by,
  t.max_extents,
  t.tablespace_used_size,
  t.segment_space_management,
  t.block_size,
  t.min_extents,
  t.min_extlen,
  t.bigfile
from
  mgmt_targets g,
  mgmt_db_tablespaces_ecm t,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = t.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);
    
CREATE OR REPLACE VIEW MGMT$DB_DATAFILES_ALL AS 
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  f.file_name,
  f.tablespace_name,
  f.status,
  f.file_size,
  f.autoextensible,
  f.increment_by,
  f.max_file_size,
  f.os_storage_entity
from
  mgmt_targets g,
  mgmt_db_datafiles_ecm f,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = f.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$DB_LICENSE_ALL AS 
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  f.SESSIONS_MAX,
  f.SESSIONS_WARNING,
  f.SESSIONS_CURRENT,
  f.SESSIONS_HIGHWATER,
  f.USERS_MAX
from
  mgmt_targets g,
  mgmt_db_license_ecm f,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = f.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$DB_OPTIONS_ALL AS 
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  o.name,
  o.selected
from
  mgmt_targets g,
  mgmt_db_options_ecm o,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = o.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$DB_REDOLOGS_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  r.group_num,
  r.status,
  r.members,
  r.file_name,
  r.archived,
  r.logsize,
  r.sequence_num,
  r.first_change_scn,
  r.os_storage_entity,
  r.thread_num
from
  mgmt_targets g,
  mgmt_db_redologs_ecm r,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = r.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$DB_CONTROLFILES_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  c.file_name,
  c.status,
  c.creation_date,
  c.sequence_num,
  c.change_num,
  c.mod_date,
  c.os_storage_entity
from
  mgmt_targets g,
  mgmt_db_controlfiles_ecm c,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = c.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+); 

CREATE OR REPLACE VIEW MGMT$DB_ROLLBACK_SEGS_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  r.rollname,
  r.status,
  r.tablespace_name,
  r.extents,
  r.rollsize,
  r.initial_size,
  r.next_size,
  r.maximum_extents,
  r.minimum_extents,
  r.pct_increase,
  r.optsize,
  r.aveactive,
  r.wraps,
  r.shrinks,
  r.aveshrink,
  r.hwmsize
from
  mgmt_targets g,
  mgmt_db_rollback_segs_ecm r,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = r.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$DB_SGA_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  sg.sganame,
  sg.sgasize
from
  mgmt_targets g,
  mgmt_db_sga_ecm sg,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = sg.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$DB_INIT_PARAMS_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  p.name as name,
  p.isdefault,
  p.value,
  ecm_util.GET_GENERIC_VALS_DATATYPE( p.value ) as datatype
from
  mgmt_targets g,
  mgmt_db_init_params_ecm p,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = p.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$HA_INFO_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  i.dbid,
  i.log_mode,
  i.force_logging,
  i.database_role,
  i.flashback_on
from
  mgmt_targets g,
  mgmt_ha_info_ecm i,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = i.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$HA_INIT_PARAMS_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  p.name,
  p.value
from
  mgmt_targets g,
  mgmt_ha_init_params_ecm p,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = p.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$HA_FILES_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  f.name,
  f.totalsize
from
  mgmt_targets g,
  mgmt_ha_files_ecm f,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = f.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);

CREATE OR REPLACE VIEW MGMT$HA_RMAN_CONFIG_ALL AS
select
  g.host_name,
  s.ecm_snapshot_id as snapshot_guid,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  s.is_current,
  a.name,
  a.value
from
  mgmt_targets g,
  mgmt_ha_rman_config_ecm a,
  mgmt$ecm_visible_snapshots s
where
  s.ecm_snapshot_id = a.ecm_snapshot_id and 
  s.target_guid = g.target_guid (+);