Rem drv: <migrate type="schema_upgrade" version="11.2.0.0"/>
Rem
Rem
Rem cm_schema_upgrade.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      cm_schema_upgrade.sql - bring pre-11.2
Rem      DB control cm schema to 11.2
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    abodge      01/14/09 - Move CM Synch to DB Control
Rem    mahessub    08/11/08 - Add max_ir_severity column
Rem    abodge      05/30/08 - Fold in Synch stuff
Rem    abodge      05/28/08 - Merge 11GC func. to 10.2.0.5
Rem    abodge      01/16/08 - Increase length of init_param columns
Rem    xshen       09/18/07 - pre-upgrade re-factoring
Rem    hakali      02/02/07 - upgrade for synch support
Rem    xshen       10/16/06 - 5014260
Rem    xshen       10/16/06 - Created
Rem


-- Fixing bug 5014260.  

ALTER TABLE "MGMT_CM_SCOPESPEC_NAMES" MODIFY ("NAME" VARCHAR2(128));

-- Upgrade Changes for Synchronization

ALTER TABLE "MGMT_CM_BASELINE_OBJGRANTS" ADD ("OBJ_TYPE" NUMBER);

-- Fix upgrade LRG 3257664

ALTER TABLE "MGMT_CM_BASELINE_INIT_PARAMS" MODIFY (
  "PARAM_VALUE" VARCHAR2(4000)
);

ALTER TABLE "MGMT_CM_COMPARISON_INIT_PRMS" MODIFY (
  "LEFT_PARAM_VALUE" VARCHAR2(4000),
  "RIGHT_PARAM_VALUE" VARCHAR2(4000)
);

-- Type renamed

DROP TYPE COMPARISON_OBJ_LIST;

DROP TYPE COMPARISON_OBJ_RECORD;

CREATE OR REPLACE TYPE CM_OBJ_RECORD AS OBJECT
(
  GUID RAW(16),
  OBJECT_TYPE NUMBER,
  OBJECT_SCHEMA VARCHAR2(30),
  OBJECT_NAME VARCHAR2(128),
  VERSION_NUMBER NUMBER
);
/

CREATE OR REPLACE TYPE CM_OBJ_LIST AS TABLE OF CM_OBJ_RECORD;
/

-- Change Manager Synchronize - now added to 10.2.0.5
CREATE TABLE MGMT_CM_BASELINE_DEPENDENCIES
(
  BASELINE_GUID 	RAW(16) NOT NULL,
  FIRST_VERSION 	NUMBER NOT NULL,
  LAST_VERSION  	NUMBER DEFAULT 99999 NOT NULL,
  TYPE          	NUMBER NOT NULL,
  OWNER         	VARCHAR2(30) NOT NULL,
  NAME          	VARCHAR2(30) NOT NULL,
  REFERENCED_TYPE 	NUMBER NOT NULL,
  REFERENCED_OWNER	VARCHAR2(30) NOT NULL,
  REFERENCED_NAME	VARCHAR2(30) NOT NULL,
  DEPENDENCY_TYPE	VARCHAR2(4) NOT NULL,
  CONSTRAINT MGMT_CM_BASELINE_DEPS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_DEPS_UC
    UNIQUE (BASELINE_GUID, TYPE, OWNER, NAME, 
            REFERENCED_TYPE, REFERENCED_OWNER, REFERENCED_NAME, 
            FIRST_VERSION)
) MONITORING;

CREATE TABLE MGMT_CM_SYNCHRONIZATIONS
(
  SYNCH_GUID		RAW(16) DEFAULT SYS_GUID(),
  SYNCH_OWNER		VARCHAR2(30) NOT NULL,  
  SYNCH_NAME		VARCHAR2(30) NOT NULL,
  SYNCH_SS		RAW(16) NOT NULL,  -- GUID of scope specification
  DESCRIPTION           VARCHAR2(2000),    -- description of synchronization
  JOB_ID                RAW(16), 
  SOURCE_TYPE		VARCHAR2(1) NOT NULL, -- 'B' OR 'D'
  SOURCE_ID		RAW(16) NOT NULL,
  SOURCE_NAME		VARCHAR2(256) NOT NULL,
  SOURCE_VERSION	NUMBER, -- When source is a baseline
  SOURCE_TEMP_BL_ID     RAW(16),
  TARGET_ID		RAW(16) NOT NULL,
  TARGET_NAME		VARCHAR2(256) NOT NULL,
  TARGET_TEMP_BL_ID     RAW(16),
  CONSTRAINT MGMT_CM_SYNCHS_PK PRIMARY KEY (SYNCH_GUID),
  CONSTRAINT MGMT_CM_SYNCHS_UC UNIQUE (SYNCH_OWNER, SYNCH_NAME)
) MONITORING;

CREATE TABLE MGMT_CM_SYNCH_VERSIONS
(
  SYNCH_GUID		RAW(16) NOT NULL,  -- Owning synch
  SYNCH_VERSION		NUMBER NOT NULL,
  VERSION_STATUS	VARCHAR(3),
  VERSION_STATE         NUMBER DEFAULT 0 NOT NULL, -- Current processing state
  JOB_EXECUTION_ID	RAW(16), -- End-to-end mode job
  SOURCE_VERSION	NUMBER,  -- Actual baseline version used
  GENERATION_TIME	DATE,  -- Time script generation started
  TIMEZONE_REGION	VARCHAR2(64),
  EXECUTION_TIME	DATE,  -- Time script execution started
  IMPACT_REPORT		BLOB,  -- HTML document containing impact report
  SCRIPT		BLOB,  -- PL/SQL script (may not be stored this way)
  COMPARISON_JOB_EXEC_ID RAW(16), -- Interactive mode job
  GENERATION_JOB_EXEC_ID RAW(16), -- Interactive mode job
  PROCESSING_MODE  NUMBER NOT NULL,
  MAX_IR_SEVERITY  NUMBER, -- Summary for rollups
  CONSTRAINT MGMT_CM_SYNCH_VERSIONS_FK FOREIGN KEY 
    (SYNCH_GUID) 
    REFERENCES MGMT_CM_SYNCHRONIZATIONS (SYNCH_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_SYNCH_VERSIONS_UC 
    UNIQUE (SYNCH_GUID, SYNCH_VERSION) 
) 
  LOB (IMPACT_REPORT, SCRIPT) STORE AS (
    TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW
  )
MONITORING;

CREATE TABLE MGMT_CM_SYNCH_OBJECTS
(
  SYNCH_GUID	        RAW(16) NOT NULL,
  OBJECT_TYPE		NUMBER NOT NULL,
  OBJECT_SCHEMA         VARCHAR2(30),  -- target-side schema
  OBJECT_NAME		VARCHAR2(128) NOT NULL,
  OBJECT_LONGNAME	CLOB,
  FIRST_VERSION         NUMBER NOT NULL,
  LAST_VERSION          NUMBER DEFAULT 99999 NOT NULL,
  SOURCE_HASH_VALUE     CHAR(32),
  TARGET_HASH_VALUE     CHAR(32),
  GRANTS_DIFF_HASH      CHAR(32),
  COMMENTS_DIFF_HASH    CHAR(32),
  ADDNL_INFO            VARCHAR2(30),
  COMP_STATE            NUMBER NOT NULL,
  PROCESSING_STATE      NUMBER DEFAULT -1 NOT NULL,
  SXML_DOCUMENT		 BLOB,
  EXCLUDED              NUMBER(1) DEFAULT 0,
  CONSTRAINT MGMT_CM_SYNCH_OBJECTS_FK
    FOREIGN KEY (SYNCH_GUID) 
    REFERENCES MGMT_CM_SYNCHRONIZATIONS (SYNCH_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_SYNCH_OBJECT_UC
    UNIQUE (SYNCH_GUID, OBJECT_TYPE, 
            OBJECT_SCHEMA, OBJECT_NAME, FIRST_VERSION)
)
  LOB (OBJECT_LONGNAME) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING,
  LOB (SXML_DOCUMENT) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

CREATE TABLE MGMT_CM_SYNCH_SCRIPTS
(
  SYNCH_GUID		RAW(16) NOT NULL,  -- Owning synch version	
  SYNCH_VERSION		NUMBER NOT NULL,
  OBJECT_TYPE		NUMBER,
  OBJECT_SCHEMA		VARCHAR2(30),
  OBJECT_NAME		VARCHAR2(128),
  SCRIPT_LINE_NO	NUMBER NOT NULL,
  SCRIPT_STEP_NO	NUMBER NOT NULL,
  SCRIPT_LINE_TYPE	VARCHAR2(15) DEFAULT 'unknown' NOT NULL,
  SCRIPT_SECTION	VARCHAR2(10) DEFAULT 'unknown' NOT NULL,
  LINE_EDITED		VARCHAR2(1) DEFAULT 'N' NOT NULL,
  SCRIPT_LINE		BLOB,
  CONSTRAINT MGMT_CM_SYNCH_SCRIPTS_FK FOREIGN KEY
    (SYNCH_GUID) 
    REFERENCES MGMT_CM_SYNCHRONIZATIONS (SYNCH_GUID)
    ON DELETE CASCADE 
) 
  LOB (SCRIPT_LINE) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW)
MONITORING;

CREATE TABLE MGMT_CM_SYNCH_IMPACT_REPORTS
(
  SYNCH_GUID		RAW(16) NOT NULL,  -- Owning synch version	
  SYNCH_VERSION		NUMBER NOT NULL,
  MESSAGE_TYPE      VARCHAR2(20) NOT NULL,
  MESSAGE_SEVERITY  NUMBER NOT NULL,
  OBJECT_TYPE		NUMBER,
  OBJECT_SCHEMA		VARCHAR2(30),
  OBJECT_NAME		VARCHAR2(128),
  PROBLEM         	BLOB,
  ACTION           	BLOB,
  CONSTRAINT MGMT_CM_SYNCH_IMPACT_REPORT_FK FOREIGN KEY
    (SYNCH_GUID) 
    REFERENCES MGMT_CM_SYNCHRONIZATIONS (SYNCH_GUID)
    ON DELETE CASCADE 
) 
  LOB (PROBLEM) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING,
  LOB (ACTION) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING;

COMMIT;