Rem drv: <migrate type="schema_upgrade" version="10.1.0.4"/> 
Rem
Rem $Header: ecm_schema_upgrade.sql 13-jan-2005.00:57:55 ktlaw Exp $
Rem
Rem ecm_schema_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_schema_upgrade.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    ktlaw       01/13/05 - add repmgr header 
Rem    smalathe    12/28/04 - Bug3903438: Add column to MGMT_DELTA_IDS table 
Rem    vkhizder    11/24/04 - vkhizder_misc_fixes_nov8_04
Rem    vkhizder    11/18/04 - Schema upgrade from 10.1.0.3.0 to 10.1.0.4.0
Rem

DECLARE

l_ignore VARCHAR2(50);

BEGIN

  -- Create MGMT_ECM_HOST_CONFIGS_TO_DEL table if it does no exist
  BEGIN
    SELECT table_name INTO l_ignore
      FROM user_tables 
     WHERE table_name = 'MGMT_ECM_HOST_CONFIGS_TO_DEL';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      EXECUTE IMMEDIATE
        'CREATE TABLE MGMT_ECM_HOST_CONFIGS_TO_DEL
         (
           SNAPSHOT_GUID RAW(16) NOT NULL,
           TS DATE DEFAULT SYSDATE NOT NULL,
           PRIMARY KEY (SNAPSHOT_GUID)
         )
         MONITORING';
  END;

  -- Create MGMT_INV_FILE_LAST_PATCH_IDX index if it does not exist
  BEGIN
    SELECT index_name INTO l_ignore
      FROM user_indexes 
     WHERE index_name = 'MGMT_INV_FILE_LAST_PATCH_IDX';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      EXECUTE IMMEDIATE
        'CREATE INDEX MGMT_INV_FILE_LAST_PATCH_IDX 
          ON MGMT_INV_FILE (LAST_PATCH_GUID)';
  END;

END;
/

Rem Bug3903438: Add a column to MGMT_DELTA_IDS table and create an index
Rem based on this new column.

ALTER TABLE &EM_REPOS_USER..MGMT_DELTA_IDS ADD
  (LAST_UPDATED_TIME DATE DEFAULT SYSDATE NOT NULL);
CREATE INDEX MGMT_DELTA_IDS_TIME_IDX
  ON MGMT_DELTA_IDS(LAST_UPDATED_TIME);