Rem drv: <create type="types"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/eml/eml_types.sql /st_emdbsa_11.2/7 2009/03/19 09:51:15 manaraya Exp $
Rem
Rem eml_types.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      eml_types.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    manaraya  02/23/09 - Bug 7931482
Rem    ysun      01/09/09 - update cell
Rem    ysun      10/31/08 - integrate cell
Rem    yemeng    09/25/08 - XbranchMerge yemeng_bug-7293143_gc from
Rem                         st_emdbgc_10.2.0.1.0
Rem    yemeng    09/11/08 - bug 7293143
Rem    mvajapey  06/03/08 - 
Rem    kramarat  01/18/08 - SIHA Integration
Rem    kramarat  12/10/07 - SI HA Integration
Rem    chyu      06/28/05 - New repmgr header impl 
Rem    pbantis   02/13/05 - Add flash recovery area. 
Rem    kramarat  12/09/04 - add Primary EM Name 
Rem    swexler   11/16/04 - restructure
Rem    jsoule    11/05/03 - dates are DATEs. period. 
Rem    jsoule    10/27/03 - add OpenMode dynamic property 
Rem    jsoule    10/25/03 - return date for time 
Rem    ychan     10/15/03 - Fix bug 3155629 
Rem    hsu       10/03/03 - use page scan rate 
Rem    jsoule    09/02/03 - simplify data type for wait bottleneck computation 
Rem    hying     07/02/03 - Fix bug 2988520, archCount
Rem    ychan     05/09/03 - Add active sess
Rem    jochen    03/28/03 - Add Storage Performance link 
Rem    swexler   11/19/02 - add response severity
Rem    jsoule    10/31/02 - remove user aliasing types
Rem    jsoule    10/30/02 - 
Rem    yfeng     10/22/02 - Add reponse_time to SMP_EMD_DBSITEMAP_OBJ
Rem    jsoule    10/10/02 - 
Rem    jsoule    10/08/02 - add types
Rem    vchao     08/21/02 - Add dupCount to SMP_EMD_DBSITEMAP_OBJ
Rem    jsoule    08/27/02 - modify alert log return data
Rem    jsoule    07/12/02 - remove response measurement
Rem    rpinnama  05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama  05/15/02 - Restructured.
Rem    jsoule    04/04/02 - replace alert log count with alert log tail.
Rem    ancheng   12/20/01 - add fields
Rem    mpawelko  09/25/01 - Pass back severity for avgResp
Rem    mpawelko  09/10/01 - Return target_guid
Rem    mpawelko  08/30/01 - Add support for getting host status
Rem    groyal    08/25/01 - Add Timed_statistics
Rem    groyal    05/30/01 - Improve performance
Rem    hjchu     05/11/01 - Created
Rem

create or replace type SMP_EMD_DBSITEMAP_OBJ as OBJECT (
    TYPE_DISPLAY_NAME     VARCHAR2(256),
    LAST_UPDATED_TIME     DATE,
    CURRENT_STATUS        INTEGER,
    AVAIL_PCT             NUMBER,
    START_TIMESTAMP       DATE,
    timeZone              VARCHAR2(64),
    INSTANCE_NAME         VARCHAR2(4000),
    INSTANCE_STARTTIME    DATE,
    INSTANCE_VERSION      VARCHAR2(4000),
    ORACLE_HOME           VARCHAR2(4000),
    CPU_COUNT             NUMBER,
    OPEN_MODE             VARCHAR2(64),
    SQL_COUNT             NUMBER,
    SQL_SEVERITY          NUMBER,
    TBSP_COUNT            NUMBER,
    TBSP_SEVERITY         NUMBER,
    ARCHAREA_MODE         NUMBER,
    ARCHAREA_PCTUSED      NUMBER,
    ARCHAREA_SEVERITY     NUMBER,
    DUMPAREA_PCTUSED      NUMBER,
    DUMPAREA_SEVERITY     NUMBER,
    ALERTLOG_TIMESTAMP    DATE,
    ALERTLOG_SEVERITY     NUMBER,
    HOST_NAME             VARCHAR2(64),
    TARGET_GUID           VARCHAR2(32),
    DUP_SQL_COUNT         NUMBER,
    RESPONSE_SEVERITY     NUMBER,
    TOP_SQL_COUNT         NUMBER,
    STG_PERF_COUNT        NUMBER,
    STG_PERF_SEVERITY     NUMBER,    
    ARCH_COUNT            NUMBER,
    BADARCH_COUNT         NUMBER,
    LISTENER_NAME         VARCHAR2(4000),
    MTTR                  NUMBER,
    ASM_TARGET_NAME       VARCHAR2(64),
    DB_REGISTERED_WITH_HAS VARCHAR2(32),
    KEYPROFILE_COUNT      NUMBER,
    KEYPROFILE_ENABLE     VARCHAR2(32),
    AUTOIMPL_ENABLE     VARCHAR2(32)
);
/


create or replace type SMP_EMD_DBSITEMAP_COMMON_OBJ as OBJECT (
    TARGET_GUID           VARCHAR2(32),
    DB_SIZE               NUMBER,
    DG_ROLE               VARCHAR2(64),
    PRMY_EM_NAME          VARCHAR2(64),
    PRMY_TYPE             VARCHAR2(64),
    BACKUP_STATUS         VARCHAR2(64),
    BACKUP_TIMESTAMP      DATE,
    FLASHBACK_TIMESTAMP   DATE,
    RECOVERY_PERCENT      NUMBER,
    FLASH_RECOVERY_AREA   VARCHAR2(512),
    LOG_MODE              VARCHAR2(32),
    FLASHBACK_ON          VARCHAR2(32),
    CELL_COUNT            VARCHAR2(512)
);
/

/*
 * mgmt_db_svctime_obj - database type capturing the point-in-time
 *                       decomposition of service time (into wait & cpu)
 *
 *   target_name - the name of the ('oracle_database') target
 *   collection_timestamp - the time at which the measurements were taken
 *   user_wait_time_pct   - % of time spent off-CPU
 */

CREATE OR REPLACE TYPE mgmt_db_svctime_obj AS
  OBJECT(target_name          VARCHAR2(256),
         collection_timestamp DATE,
         user_wait_time_pct   NUMBER);
/

/*
 * mgmt_db_svctime_array - database array type for mgmt_db_svctime_obj's
 */

CREATE OR REPLACE TYPE mgmt_db_svctime_array AS
  TABLE OF mgmt_db_svctime_obj;
/

CREATE OR REPLACE TYPE mgmt_db_statesummary_obj AS
  OBJECT(target_name          VARCHAR2(256),
         collection_timestamp DATE,
         state                VARCHAR2(16));
/

CREATE OR REPLACE TYPE mgmt_db_statesummary_array AS
  TABLE OF mgmt_db_statesummary_obj;
/