Rem drv: <create type="views"/>
Rem
Rem $Header: config_views.sql 08-may-2006.08:48:07 chyu Exp $
Rem
Rem config_views.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      config_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    chyu        05/08/06 - RCU Compliant 
Rem    hying       07/31/05 - Swap view column order for mgmt$ha_backup 
Rem    chyu        06/28/05 - New repmgr header impl 
Rem    xuliu       05/11/05 - re-order mgmt$ha_backup columns 
Rem    rreilly     03/18/05 - remove dbrollup and controlfiles_size 
Rem    xshen       01/13/05 - added columns 
Rem    rreilly     12/01/04 - db_controlfiles_size
Rem    rreilly     11/19/04 - add new seg management column 
Rem    rreilly     11/11/04 - add tablespace_used_size to db_tablespaces view 
Rem    jsmoler     09/28/04 - delete commented emrepos lines (preventing ias_cre.sql from being called)
Rem    xshen       09/21/04 - moving delta_init and delta_dbrollup 
Rem    agor        09/20/04 - create views on both for now 
Rem    akskumar    09/16/04 - 
Rem    pbantis     09/07/04 - Update ha_backup. 
Rem    akskumar    09/06/04 - add database config views to be required for 
Rem                           viewwing saved configurations, will get executed 
Rem                           only in CENTRAL mode 
Rem    sbadrina    05/05/04 - adding MGMT$DB_DBROLLUP view 
Rem    xshen       05/04/04 - move delta init view TO sdk
Rem    xshen       11/12/03 - remove rule fw backend 
Rem    xshen       10/29/03 - add type column to init param metric 
Rem    xshen       08/15/03 - final tweaks
Rem    xshen       08/11/03 - remove target assoc views 
Rem    pbantis     07/21/03 - Update MGMT$HA_INFO
Rem    xshen       06/16/03 - add next extent column to tablespace
Rem    xshen       05/22/03 - use same views for snapshot
Rem    xshen       05/19/03 - cutting over to snapshot framework
Rem    xshen       03/28/03 - supporting rac and db targets config views
Rem    pbantis     03/28/03 - Add RAC support
Rem    pbantis     03/20/03 - Add mgmt$ha_rman_config
Rem    pbantis     01/06/03 - Add High Availability views
Rem    xshen       09/18/02 - added results specific to each target/rule pair
Rem    xshen       09/16/02 - added target type, level to rule views
Rem    xshen       09/11/02 - change database_name to target_name
Rem    xshen       08/19/02 - support rule
Rem    xshen       08/08/02 - more config views
Rem    xshen       08/07/02 - adds tbsp datafile sdk view for search using sql
Rem    shuberma    05/17/02 - Remove FORCE if possible, and add column (datatype)..
Rem    rpinnama    05/16/02 - Moved MGMT$DB_INIT_PARAMS
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

  
----------------------------------------------------------------------
--         High Availability (Non-snapshot data) Views              --
----------------------------------------------------------------------
  

CREATE OR REPLACE VIEW MGMT$HA_BACKUP AS
select
  d.host_name as host,
  d.target_name as database_name,
  d.target_type,
  d.display_name,
  b.target_guid,
  b.session_key,
  b.session_recid,
  b.session_stamp,
  b.command_id,
  b.status,
  b.start_time,
  b.end_time,
  b.time_taken_display,
  b.input_type,
  b.output_device_type,
  b.input_bytes_display,
  b.output_bytes_display,
  b.output_bytes_per_sec_display
from
  mgmt_targets d,
  mgmt_ha_backup b
where
  (d.target_type = 'oracle_database' or d.target_type = 'rac_database') and
  d.target_guid = b.target_guid;


CREATE OR REPLACE VIEW MGMT$HA_MTTR AS
select
  d.host_name as host,
  d.target_name as database_name,
  d.target_type,
  m.target_guid,
  m.estimated_mttr
from
  mgmt_targets d,
  mgmt_ha_mttr m
where
  d.target_type = 'oracle_database' and
  d.target_guid = m.target_guid;
  
-----------------------------------------------------------
-- dbconfig init params history                    
-- PURPOSE
--   Used to show any history changes on 
--   init params ecm collection 

CREATE OR REPLACE VIEW MGMT$DELTA_INIT AS 
SELECT 
  e.delta_time, 
  e.operation,  
  g.host_name,
  g.target_guid,
  g.target_name,
  g.target_type,
  n.value as name, 
  d.name as attribute_name, 
  d.old_value, 
  d.value as new_value,
  ecm_util.GET_GENERIC_VALS_DATATYPE( d.value ) as datatype    
FROM 
  mgmt_delta_entry e, 
  mgmt_delta_entry_values d, 
  mgmt_delta_ids i, 
  mgmt_delta_id_values n,
  mgmt_delta_snap p,
  mgmt_targets g
WHERE i.row_guid = e.row_guid and
  i.collection_type = 'MGMT_DB_INIT_PARAMS_ECM' and
  i.row_guid = n.delta_ids_guid and
  n.name='NAME' and
  e.delta_entry_guid = d.delta_entry_guid and
  p.delta_guid = e.delta_guid and
  p.snapshot_type = 'oracle_dbconfig' and
  p.target_type = 'oracle_database' and
  p.new_left_target_name = g.target_name and
  p.old_right_target_name = g.target_name and
  p.target_type = g.target_type and 
  p.delta_type = 'HISTORY';
    
----------------------------------------------------------------------
--          Database Configuration Snapshot Views                   --
----------------------------------------------------------------------
  
CREATE OR REPLACE VIEW MGMT$DB_DBNINSTANCEINFO AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = i.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';
  
  
CREATE OR REPLACE VIEW MGMT$DB_TABLESPACES AS 
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = t.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current  = 'Y';  

CREATE OR REPLACE VIEW MGMT$DB_DATAFILES AS 
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = f.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$DB_LICENSE AS 
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = f.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$DB_OPTIONS 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_gen_snapshot s
where
  s.snapshot_guid = o.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$DB_REDOLOGS AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = r.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$DB_CONTROLFILES AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = c.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y'; 

CREATE OR REPLACE VIEW MGMT$DB_ROLLBACK_SEGS AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = r.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$DB_SGA AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  sg.sganame,
  sg.sgasize
from
  mgmt_targets g,
  mgmt_db_sga_ecm sg,
  mgmt_ecm_gen_snapshot s
where
  s.snapshot_guid = sg.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$DB_INIT_PARAMS AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = p.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$HA_INFO AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  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_gen_snapshot s
where
  s.snapshot_guid = i.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$HA_INIT_PARAMS AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  p.name,
  p.value
from
  mgmt_targets g,
  mgmt_ha_init_params_ecm p,
  mgmt_ecm_gen_snapshot s
where
  s.snapshot_guid = p.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$HA_FILES AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  f.name,
  f.totalsize
from
  mgmt_targets g,
  mgmt_ha_files_ecm f,
  mgmt_ecm_gen_snapshot s
where
  s.snapshot_guid = f.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';

CREATE OR REPLACE VIEW MGMT$HA_RMAN_CONFIG AS
select
  g.host_name,
  s.target_name,
  s.target_type,
  s.target_guid,
  s.start_timestamp as collection_timestamp,
  a.name,
  a.value
from
  mgmt_targets g,
  mgmt_ha_rman_config_ecm a,
  mgmt_ecm_gen_snapshot s
where
  s.snapshot_guid = a.ecm_snapshot_id and 
  s.target_guid = g.target_guid and
  s.is_current = 'Y';
  

-- create views for viewing saved database configurations
-- For now, to be executed only in CENTRAL mode

-- the following is not supported by RCU, would need to
-- use condition attribute in the RepManager header for
-- EM_REPOS_MODE specific script
/*
COLUMN :script_name NEW_VALUE views_file NOPRINT
VARIABLE script_name VARCHAR2(256)
BEGIN
    :script_name :='&EM_SQL_ROOT/db/latest/config/config_gc_views.sql';
END;
/

SELECT :script_name FROM DUAL;

@&views_file  
*/