Rem drv: <migrate type="schema_downgrade" version="11.2.0.3"/>
Rem
Rem
Rem cell_schema_downgrade.sql
Rem
Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      cell_schema_downgrade.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      ystein    03/18/11  - Exadata plugin schema downgrade
Rem

BEGIN
-- create matched before-11.2.0.3 version
-- TABLE MGMT_EMX_CELL_C_CONFIG "SNMPSUBSCRIBER"'s type from VARCHAR2(512) to VARCHAR2(200)
-- TABLE MGMT_EMX_CELL_PD_CONFIG needs to delete new column DISKTYPE.

 ------------------ Create Table: MGMT_EMX_CELL_C_CONFIG  --------
  EXECUTE IMMEDIATE ' ALTER TABLE MGMT_EMX_CELL_C_CONFIG MODIFY ( SNMPSUBSCRIBER VARCHAR2(200) ) ';

 ------------------ Create View: MGMT_EMX_CELL_C_CONFIG_VIEW  -----
  EXECUTE IMMEDIATE ' CREATE OR REPLACE VIEW  MGMT_EMX_CELL_C_CONFIG_VIEW AS 
  SELECT t.ECM_SNAPSHOT_ID,t.NAME,t.STATUS,t.REALMNAME,t.ID,t.MAKEMODEL,t.LOCATION,t.BMCTYPE,t.IPBLOCK,t.FANCOUNT,t.POWERCOUNT,t.METRICHISTORYDAYS,t.SNMPSUBSCRIBER,t.SMTPSERVER,t.SMTPPORT,t.IPADDRESS1,t.IPADDRESS2,t.IPADDRESS3,t.IPADDRESS4,t.KERNELVERSION,t.OSSVERSION,t.INTERCONNECTCOUNT,t.CPUCOUNT ,
    s.TARGET_GUID, s.START_TIMESTAMP, s.TARGET_NAME, s.TARGET_TYPE 
  FROM MGMT_EMX_CELL_C_CONFIG t, MGMT$ECM_CURRENT_SNAPSHOTS s
  WHERE t.ECM_SNAPSHOT_ID = s.ECM_SNAPSHOT_ID ';

 --------   Granting the privileges to view ------ 
  EXECUTE IMMEDIATE ' 
  GRANT SELECT ON MGMT_EMX_CELL_C_CONFIG_VIEW TO ' || MGMT_VIEW_PRIV.MGMT_VIEW_USER;

  EXECUTE IMMEDIATE ' 
  CREATE OR REPLACE PUBLIC SYNONYM MGMT_EMX_CELL_C_CONFIG_VIEW FOR MGMT_EMX_CELL_C_CONFIG_VIEW ';


 ------------------ Create Table: MGMT_EMX_CELL_PD_CONFIG  --------
  EXECUTE IMMEDIATE ' ALTER TABLE MGMT_EMX_CELL_PD_CONFIG DROP ( DISKTYPE ) ';

 ------------------ Create View: MGMT_EMX_CELL_PD_CONFIG_VIEW  -----
  EXECUTE IMMEDIATE ' CREATE OR REPLACE VIEW  MGMT_EMX_CELL_PD_CONFIG_VIEW AS 
  SELECT t.ECM_SNAPSHOT_ID,t.NAME,t.CELLNAME,t.REALMNAME,t.STATUS,t.ID,t.MAKEMODEL,t.LUNS,t.ERRORCOUNT,t.CTRLFIRMWARE,t.CTRLHWVERSION,t.PHYSINTERFACE,t.PHYSFIRMWARE,t.PHYSSIZE,t.PHYSSERIAL,t.PHYSUSETYPE,t.PHYSPORT,t.PHYSINSERTTIME, 
    s.TARGET_GUID, s.START_TIMESTAMP, s.TARGET_NAME, s.TARGET_TYPE 
  FROM MGMT_EMX_CELL_PD_CONFIG t, MGMT$ECM_CURRENT_SNAPSHOTS s
  WHERE t.ECM_SNAPSHOT_ID = s.ECM_SNAPSHOT_ID ';

 --------   Granting the privileges to view ------ 
  EXECUTE IMMEDIATE ' 
  GRANT SELECT ON MGMT_EMX_CELL_PD_CONFIG_VIEW TO ' || MGMT_VIEW_PRIV.MGMT_VIEW_USER;

  EXECUTE IMMEDIATE ' 
  CREATE OR REPLACE PUBLIC SYNONYM MGMT_EMX_CELL_PD_CONFIG_VIEW FOR MGMT_EMX_CELL_PD_CONFIG_VIEW ';

END;
/