Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" condition="EM_REPOS_MODE=SYSAUX"/>
Rem
Rem $Header: config_schema_upgrade_dbcontrol_only.sql 27-nov-2006.18:21:17 chyu Exp $
Rem
Rem config_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2006, 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    chyu        11/27/06 - fixing the error noted in bug 5683821
Rem    chyu        10/16/06 - fixing upgrade from 10.2.0.1 dbcontrol
Rem    chyu        08/24/06 - factoring out the proper schema gap for DBControl 
Rem


-- ALTER TABLE "MGMT_DB_CONTROLFILES_ECM"
--    ADD ("OS_STORAGE_ENTITY" VARCHAR2(512));

-- ALTER TABLE "MGMT_DB_REDOLOGS_ECM"
--    ADD ("OS_STORAGE_ENTITY" VARCHAR2(512));

-- ALTER TABLE "MGMT_DB_DATAFILES_ECM"
--    ADD ("OS_STORAGE_ENTITY" VARCHAR2(512));

-- ALTER TABLE "MGMT_DB_TABLESPACES_ECM"
--     ADD ("TABLESPACE_USED_SIZE" NUMBER);

DECLARE
    l_count NUMBER := 0;
BEGIN
    SELECT count(*)
    INTO l_count
    FROM ALL_TAB_COLUMNS
    WHERE upper(table_name) = 'MGMT_DB_CONTROLFILES_ECM'
      AND upper(column_name) = 'OS_STORAGE_ENTITY'
      AND upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 0) THEN
        EXECUTE IMMEDIATE ' ALTER TABLE MGMT_DB_CONTROLFILES_ECM ADD (OS_STORAGE_ENTITY VARCHAR2(512))';
    END IF;

    SELECT count(*)
    INTO l_count
    FROM ALL_TAB_COLUMNS
    WHERE upper(table_name) = 'MGMT_DB_REDOLOGS_ECM'
      AND upper(column_name) = 'OS_STORAGE_ENTITY'
      AND upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 0) THEN
        EXECUTE IMMEDIATE ' ALTER TABLE MGMT_DB_REDOLOGS_ECM ADD (OS_STORAGE_ENTITY VARCHAR2(512))';
    END IF;

    SELECT count(*)
    INTO l_count
    FROM ALL_TAB_COLUMNS
    WHERE upper(table_name) = 'MGMT_DB_DATAFILES_ECM'
      AND upper(column_name) = 'OS_STORAGE_ENTITY'
      AND upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 0) THEN
        EXECUTE IMMEDIATE ' ALTER TABLE MGMT_DB_DATAFILES_ECM ADD (OS_STORAGE_ENTITY VARCHAR2(512))';
    END IF;

    SELECT count(*)
    INTO l_count
    FROM ALL_TAB_COLUMNS
    WHERE upper(table_name) = 'MGMT_DB_TABLESPACES_ECM'
      AND upper(column_name) = 'TABLESPACE_USED_SIZE'
      AND upper(owner) = upper('&EM_REPOS_USER');

    IF (l_count = 0) THEN
        EXECUTE IMMEDIATE ' ALTER TABLE MGMT_DB_TABLESPACES_ECM ADD (TABLESPACE_USED_SIZE NUMBER)';
    END IF;
END;
/

-- Changed from regular table to an IOT with compression 1.

-- 1.  Create interim table for online redefinition
CREATE TABLE "INITPARAMS_INT" (
  "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "INITPARAMS_INT_FK" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
  "NAME" VARCHAR2(64),
  "VALUE" VARCHAR2(512),
  "ISDEFAULT" VARCHAR2(6),
  CONSTRAINT "INITPARAMS_INT_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "NAME")
) ORGANIZATION INDEX 
  COMPRESS 1
  MONITORING;

DECLARE 
  no_errors NUMBER := 0;
BEGIN
  -- 2.  Redifinition
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('SYSMAN', 'MGMT_DB_INIT_PARAMS_ECM', 'INITPARAMS_INT');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('SYSMAN', 'MGMT_DB_INIT_PARAMS_ECM', 'INITPARAMS_INT');
    no_errors := 1;
  EXCEPTION 
    WHEN OTHERS THEN 
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('SYSMAN', 'MGMT_DB_INIT_PARAMS_ECM', 'INITPARAMS_INT');
  END;

  -- 3.  Drop interim table
  EXECUTE IMMEDIATE 'DROP TABLE INITPARAMS_INT';

  -- Rename constraints
  IF no_errors = 1 THEN 
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_DB_INIT_PARAMS_ECM RENAME CONSTRAINT INITPARAMS_INT_PK TO MGMT_DB_INIT_PARAMS_ECM_PK';
    EXECUTE IMMEDIATE 'ALTER INDEX INITPARAMS_INT_PK RENAME TO MGMT_DB_INIT_PARAMS_ECM_PK';
  END IF;

END;
/

-- Drop PUBLIC synonym that was created on public views.
DROP PUBLIC SYNONYM MGMT$DELTA_TABLESPACES;

-- Drop View MGMT$DELTA_TABLESPACES
DROP VIEW MGMT$DELTA_TABLESPACES;

-----------------------------------------------
-- Drop Agent 4.0.0 backward compatible elements
-- We do not support 4.0.0 in 10.2 GC repository
-- Only Agent 10.1.0.2 or up.
-- Confirmed from Murali and Narain.
-- Only exist in GC.
-----------------------------------------------
DECLARE
  l_cnt NUMBER := 0;
BEGIN
  IF ('&EM_REPOS_MODE' = 'CENTRAL') THEN
   -- Trigger
   EXECUTE IMMEDIATE 'DROP TRIGGER MGMT_DBCONFIG_COMPATIBLE_TR';
   -- Package
   EXECUTE IMMEDIATE 'DROP PACKAGE MGMT_DBCONFIG_COMPATIBLE';
   -- Tables - Old 4.0.0 tables with no _ECM.
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_DBNINSTANCEINFO';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_INIT_PARAMS';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_TABLESPACES';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_DATAFILES';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_REDOLOGS';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_CONTROLFILES';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_ROLLBACK_SEGS';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_SGA';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_LICENSE';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DB_OPTION';
   EXECUTE IMMEDIATE 'DROP TABLE MGMT_DBCONFIG_LOG';
 END IF;
END;
/ 


COMMIT;