Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" condition="EM_REPOS_MODE=CENTRAL"/>
Rem
Rem $Header: config_schema_upgrade.sql 23-aug-2006.13:08:59 bmallipe 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    bmallipe    08/23/06 - conditioning this to be CENTRAL only mode
Rem    xshen       08/24/05 - remove public synonym on mgmt$delta_tablespaces 
Rem    chyu        07/12/05 - adding the new rep manager upgrade header 
Rem    rreilly     03/18/05 - remove dbrollup and controlfiles_size 
Rem    xshen       03/14/05 - obsolete bkcomp elements 
Rem    xshen       02/27/05 - adding drop view
Rem    xshen       02/08/05 - comment out rename constraint due to issues on 
Rem                           db 9.0.1.5.0
Rem    xshen       02/04/05 - xshen_bug-4160666
Rem    xshen       02/04/05 - Created
Rem

CREATE TABLE "MGMT_DB_OPTIONS_ECM" (
  "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_OPTIONS_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
  "NAME" VARCHAR2(30),
  "SELECTED" VARCHAR2(5),
  CONSTRAINT "MGMT_DB_OPTIONS_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "NAME")
) MONITORING;


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

ALTER TABLE "MGMT_DB_REDOLOGS_ECM"
    ADD ("OS_STORAGE_ENTITY" VARCHAR2(512),
         "THREAD_NUM" NUMBER);

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

ALTER TABLE "MGMT_DB_TABLESPACES_ECM"
    ADD ("TABLESPACE_USED_SIZE" NUMBER,
         "SEGMENT_SPACE_MANAGEMENT" VARCHAR2(6),
         "BLOCK_SIZE" NUMBER,
         "MIN_EXTENTS" NUMBER,
         "MIN_EXTLEN" NUMBER,
         "BIGFILE" VARCHAR2(3));

ALTER TABLE "MGMT_DB_DBNINSTANCEINFO_ECM"
    ADD ("DEFAULT_TEMP_TABLESPACE" VARCHAR2(30),
         "CHARACTERSET" VARCHAR2(64),
         "NATIONAL_CHARACTERSET" VARCHAR2(64));

-- 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;