Rem drv: <migrate type="schema_upgrade" version="11.0.0.0"/>
Rem
Rem $Header: config_schema_upgrade.sql 26-jan-2007.11:43:14 xshen Exp $
Rem
Rem config_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      config_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    xshen       01/26/07 - bug 5682327
Rem    xshen       01/24/07 - Fix 5682310
Rem    xshen       08/18/06 - upgrade V$PARAMETER.VALUE to varchar2(4000)
Rem    xshen       02/22/06 - Upgrade banner column per dbms change 
Rem

ALTER TABLE "MGMT_DB_DBNINSTANCEINFO_ECM" MODIFY ("BANNER" VARCHAR2(80));

-- Add overflow: alters should be separated for add, including and modify!
ALTER TABLE "MGMT_DB_INIT_PARAMS_ECM" ADD OVERFLOW;

ALTER TABLE "MGMT_DB_INIT_PARAMS_ECM" INCLUDING "NAME";

ALTER TABLE "MGMT_DB_INIT_PARAMS_ECM" MODIFY ("VALUE" VARCHAR2(4000));

ALTER TABLE "MGMT_DB_INIT_PARAMS_ECM" MODIFY ("NAME" VARCHAR2(80));

ALTER TABLE "MGMT_DB_INIT_PARAMS_ECM" MODIFY ("ISDEFAULT" VARCHAR2(9));

ALTER TABLE "MGMT_DB_ROLLBACK_SEGS_ECM" MODIFY ("STATUS" VARCHAR2(16));

-------------------------------------------
-- Fix bug 5682310.  DBC 10.2
-- repository still have delta views which
-- were removed in 10.2 core. Remove these
-- if we found them.
-------------------------------------------
DECLARE
    l_count NUMBER := 0;
BEGIN

    -- Drop public synonym that is defined on public views
    select count(*) into l_count from all_objects
    where upper(object_name) = 'MGMT$DELTA_TABLESPACES'
      and upper(object_type) = 'SYNONYM'
      and upper(owner) = 'PUBLIC';

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP PUBLIC SYNONYM MGMT$DELTA_TABLESPACES ';
    END IF;

    -- Drop view that was not there in 11g dbc repository
    select count(*) into l_count from all_objects
    where upper(object_name) = 'MGMT$DELTA_TABLESPACES'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW MGMT$DELTA_TABLESPACES ';
    END IF;

END;
/

COMMIT;

-------------------------------------------
-- Fix bug 5682327.  
-------------------------------------------
DECLARE
    l_count NUMBER := 0;
BEGIN
    -- Bug 5682327
    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_ASSOCIATEDDBSYSTEM'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_ASSOCIATEDDBSYSTEM ';
    END IF;

    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_CURRENTDBINSTANCESTATS'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_CURRENTDBINSTANCESTATS ';
    END IF;

    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_CURRENTDBSTATISTICS'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_CURRENTDBSTATISTICS ';
    END IF;
 
    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_DATABASE'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_DATABASE ';
    END IF;

    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_DATABASEINSTANCE'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_DATABASEINSTANCE ';
    END IF;

    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_DATABASESTATISTICS'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_DATABASESTATISTICS ';
    END IF;

    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_DATABASESYSTEM'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_DATABASESYSTEM ';
    END IF;

    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_DBINSTANCESTATISTICS'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_DBINSTANCESTATISTICS ';
    END IF;

    select count(*) into l_count from all_objects
    where upper(object_name) = 'ORACLE_INSTANCEAVAILABLETODB'
      and upper(object_type) = 'VIEW'
      and upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 1) THEN
        EXECUTE IMMEDIATE ' DROP VIEW ORACLE_INSTANCEAVAILABLETODB ';
    END IF;

END;
/

COMMIT;