Rem drv: <create type="tables" params="EM_ECM_DEPOT_TABLESPACE" pos="osm/osm_tables.sql+"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/changemgr/cm_tables.sql /st_emdbsa_11.2/1 2009/02/02 12:13:29 abodge Exp $
Rem
Rem cm_tables.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      cm_tables.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    abodge      01/28/09 - Add MONITORING as needed
Rem    abodge      01/14/09 - Move CM Synch to DB Control
Rem    mahessub    08/08/08 - add column MAX_IR_SEVERITY to rollup warnings
Rem    mahessub    12/18/07 - Remove execution_job_exec_id column from synch versions table
Rem    abodge      10/02/07 - Retain temp BLs in Synch
Rem    mahessub    01/09/07 - Add excluded column to synch objects table
Rem    mahessub    01/05/07 - Add synch impact reports table
Rem    abodge      10/09/06 - Add synch objects table
Rem    hakali      09/12/06 - 
Rem    abodge      06/09/06 - Add obj grant obj type col 
Rem    abodge      03/20/06 - Add Synch tables 
Rem    xshen       10/16/06 - 5014260
Rem    abodge      06/27/06 - Increase size of parameter value columns - bug 
Rem                           5344422 
Rem    abodge      05/13/08 - Migrate 11GC functionality
Rem    abodge      04/10/07 - Bug 5978378 - init param values are 4000 chrs
Rem    abodge      07/19/05 - Add col to baseline deps table 
Rem    chyu        07/15/05 - adding the parameter 
Rem    gsbhatia    07/11/05 - Fix repmgr header 
Rem    chyu        06/28/05 - New repmgr header impl 
Rem    abodge      05/25/05 - Add column to BL objs table 
Rem    xshen       05/17/05 - grabtrans 'lhan_cm_annotate' 
Rem    lhan        05/05/05 - add annotation support
Rem    abodge      03/23/05 - Store DB version with baseline versions 
Rem    abodge      03/18/05 - Add mgmt_cm_baseline_params 
Rem    lhan        02/17/05 - fix bug 4192287
Rem    abodge      11/18/04 - Add version_status column to versions tables 
Rem    abodge      10/15/04 - Increase size of OBJECT_NAME col for comp objects
Rem    abodge      10/01/04 - Add unique constraint on version cols 
Rem    xshen       09/23/04 - adding description column to comparison table 
Rem    lhan        09/14/04 - lhan_cm_0914
Rem    lhan        09/14/02 - Created
Rem

/***

DROP TABLE MGMT_CM_BASELINE_VERSIONS;
DROP TABLE MGMT_CM_BASELINE_OBJECTS;
DROP TABLE MGMT_CM_BASELINE_DEPENDENTS;
DROP TABLE MGMT_CM_BASELINE_OBJGRANTS;
DROP TABLE MGMT_CM_BASELINE_SYSGRANTS;
DROP TABLE MGMT_CM_BASELINE_ROLEGRANTS;
DROP TABLE MGMT_CM_BASELINE_QUOTAGRANTS;
DROP TABLE MGMT_CM_BASELINE_PROXYGRANTS;
DROP TABLE MGMT_CM_BASELINE_CONS_GROUPS;
DROP TABLE MGMT_CM_BASELINE_INIT_PARAMS;
DROP TABLE MGMT_CM_BASELINE_DEPENDENCIES;
DROP TABLE MGMT_CM_BASELINES;
DROP TABLE MGMT_CM_COMPARISON_VERSIONS;
DROP TABLE MGMT_CM_COMPARISON_OBJECTS;
DROP TABLE MGMT_CM_COMPARISON_INIT_PRMS;
DROP TABLE MGMT_CM_SCHEMA_MAPS;
DROP TABLE MGMT_CM_COMPARISONS;
DROP TABLE MGMT_CM_SYNCH_VERSIONS;
DROP TABLE MGMT_CM_SYNCH_SCRIPTS;
DROP TABLE MGMT_CM_SYNCH_IMPACT_REPORTS;
DROP TABLE MGMT_CM_SYNCHRONIZATIONS;
DROP TABLE MGMT_CM_SCOPESPEC_NAMES;
DROP TABLE MGMT_CM_SCOPESPECS;

***/

Rem
Rem This script requires the following arguments
Rem  1. Name of the ecm_depot tablespace where all LOBs are stored
Rem
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"

-- Scope specification

CREATE TABLE MGMT_CM_SCOPESPECS
(
  SS_GUID	RAW(16),
  OBJECT_TYPES	NUMBER NOT NULL,
  FLAGS		NUMBER DEFAULT 0 NOT NULL,
  CONSTRAINT MGMT_CM_SCOPESPECS_PK PRIMARY KEY (SS_GUID)
) MONITORING;

CREATE TABLE MGMT_CM_SCOPESPEC_NAMES
(
  SS_GUID	RAW(16) NOT NULL,
  MATCH_TYPES	NUMBER DEFAULT 0 NOT NULL,
  SCHEMA        VARCHAR2(30),  -- For specific objects
  NAME		VARCHAR2(128) NOT NULL,
  LONGNAME	CLOB,  -- For objects such as Java classes
  CONSTRAINT MGMT_CM_SCOPESPEC_NAMES_FK
    FOREIGN KEY (SS_GUID) REFERENCES MGMT_CM_SCOPESPECS (SS_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_SCOPESPEC_NAMES_UC 
    UNIQUE (SS_GUID, MATCH_TYPES, SCHEMA, NAME)
)
  LOB (LONGNAME) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

-- Baselines

CREATE TABLE MGMT_CM_BASELINES
(
  BASELINE_GUID	RAW(16) DEFAULT SYS_GUID(),
  BASELINE_OWNER	VARCHAR2(30) NOT NULL,  -- or GUID?
  BASELINE_NAME	VARCHAR2(40) NOT NULL,  -- Need 40 chars to fit temp BL name
  BASELINE_SS	RAW(16) NOT NULL,
  SOURCE_ID	RAW(16) NOT NULL,  -- Target GUID of database
  SOURCE_NAME	VARCHAR2(256) NOT NULL,  -- Name of database
  JOB_ID        RAW(16),
  DESCRIPTION   VARCHAR2(2000),    -- description of baseline
  TEMP_BL       NUMBER(1) DEFAULT 0,  -- 0: permanent, 1: temporary, 2: synch
  CONSTRAINT MGMT_CM_BASELINES_PK PRIMARY KEY (BASELINE_GUID),
  CONSTRAINT MGMT_CM_BASELINES_UC 
    UNIQUE (BASELINE_OWNER, BASELINE_NAME)
) MONITORING;

CREATE TABLE MGMT_CM_BASELINE_VERSIONS
(
  BASELINE_GUID	RAW(16) NOT NULL,
  BASELINE_VERSION	NUMBER NOT NULL,
  CAPTURE_TIME          DATE,
  TIMEZONE_REGION       VARCHAR2(64),
  DB_VERSION            VARCHAR2(20),
  JOB_EXECUTION_ID      RAW(16),
  VERSION_STATUS        VARCHAR(3),
  DDL                   BLOB,
  DDL_STATUS            NUMBER DEFAULT -999 NOT NULL,
  DDL_TIME              DATE,
  CONSTRAINT MGMT_CM_BASELINE_VERSIONS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_VERSIONS_UC 
    UNIQUE(BASELINE_GUID, BASELINE_VERSION)
)
  LOB (DDL) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_OBJECTS
(
  BASELINE_GUID	RAW(16) NOT NULL,
  OBJECT_TYPE	NUMBER NOT NULL,
  OBJECT_SCHEMA	VARCHAR2(30),
  OBJECT_NAME	VARCHAR2(128) NOT NULL,
  OBJECT_LONGNAME	CLOB,
  LAST_DDL_TIME         DATE,
  HASH_VALUE    CHAR(32),
  FIRST_VERSION	NUMBER NOT NULL,
  LAST_VERSION	NUMBER DEFAULT 99999 NOT NULL,
  DEFINITION	BLOB, -- NOT NULL,
  ADDNL_INFO    VARCHAR2(30),
  CONSTRAINT MGMT_CM_BASELINE_OBJECTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_OBJECT_UC
    UNIQUE (BASELINE_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 (DEFINITION) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

-- dependents such as comments, object grants, etc.
CREATE TABLE MGMT_CM_BASELINE_DEPENDENTS
(
  BASELINE_GUID         RAW(16) NOT NULL,
  BASE_OBJECT_TYPE	NUMBER NOT NULL,
  BASE_OBJECT_SCHEMA	VARCHAR2(30),
  BASE_OBJECT_NAME	VARCHAR2(128) NOT NULL,
  DEPENDENT_TYPE        NUMBER NOT NULL,
  HASH_VALUE            CHAR(32),
  FIRST_VERSION         NUMBER NOT NULL,
  LAST_VERSION          NUMBER DEFAULT 99999 NOT NULL,
  DEFINITION            BLOB, -- NOT NULL,
  BASE_OBJECT_COLUMN    VARCHAR2(30),
  CONSTRAINT MGMT_CM_BASELINE_DEPENDENTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_DEPEND_UC
    UNIQUE (BASELINE_GUID, BASE_OBJECT_TYPE, BASE_OBJECT_SCHEMA,
            BASE_OBJECT_NAME, DEPENDENT_TYPE, HASH_VALUE, FIRST_VERSION)
)
  LOB (DEFINITION) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_OBJGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  OBJ_OWNER     VARCHAR2(30) NOT NULL,
  OBJ_NAME      VARCHAR2(30) NOT NULL,
  COL_NAME      VARCHAR2(30),
  GRANTOR_NAME  VARCHAR2(30) NOT NULL,
  PRIVILEGE     VARCHAR2(40) NOT NULL,
  FLAG          NUMBER,
  OBJ_TYPE      NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_OBJGRANTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_OBJGRANTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, OBJ_OWNER, 
         OBJ_NAME, COL_NAME, GRANTOR_NAME, PRIVILEGE, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_SYSGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  PRIVILEGE     VARCHAR2(40) NOT NULL,
  FLAG          NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_SYSGRANTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_SYSGRANTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, PRIVILEGE, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_ROLEGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  ROLEPRIV      VARCHAR2(30) NOT NULL,
  FLAG          NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_ROLEGRANTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_ROLEGRANTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, ROLEPRIV, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_QUOTAGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  TABLESPACE    VARCHAR2(30) NOT NULL,
  MAXBYTES      NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_QUOTGRNNTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_QUOTGRNNTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, TABLESPACE, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_PROXYGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  PROXY_USER    VARCHAR2(30) NOT NULL,
  DIRECTION     NUMBER,  -- MGMT_CM.PROXY_USER or PROXIED_FOR_USER
  CONSTRAINT MGMT_CM_BASELINE_PROXGRNTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_PROXGRNTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, PROXY_USER, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_CONS_GROUPS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  CONS_GROUP    VARCHAR2(30) NOT NULL,
  FLAG          NUMBER,  
  CONSTRAINT MGMT_CM_BASELINE_CONSGRPS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_CONSGRPS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, CONS_GROUP, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_INIT_PARAMS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  PARAM_NAME    VARCHAR2(64) NOT NULL,
  PARAM_VALUE   VARCHAR2(4000),
  CONSTRAINT MGMT_CM_BASELINE_PARAMS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_PARAMS_UC
    UNIQUE (BASELINE_GUID, PARAM_NAME, FIRST_VERSION)
) MONITORING
;

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
;

-- Comparisons

CREATE TABLE MGMT_CM_COMPARISONS
(
  COMPARISON_GUID	RAW(16) DEFAULT SYS_GUID(),
  COMPARISON_OWNER	VARCHAR2(30) NOT NULL,  -- or GUID?
  COMPARISON_NAME	VARCHAR2(30) NOT NULL,
  COMPARISON_SS	RAW(16) NOT NULL,
  DESCRIPTION           VARCHAR2(2000),    -- description of comparison
  JOB_ID                RAW(16),
  LEFT_TYPE		VARCHAR2(1) NOT NULL, -- 'B' OR 'D'
  LEFT_ID		RAW(16) NOT NULL,
  LEFT_NAME		VARCHAR2(256) NOT NULL,
  LEFT_VERSION	NUMBER, -- When source is a baseline
  RIGHT_TYPE		VARCHAR2(1) NOT NULL, -- 'B' OR 'D'
  RIGHT_ID		RAW(16) NOT NULL,
  RIGHT_NAME		VARCHAR2(256) NOT NULL,
  RIGHT_VERSION	NUMBER, -- When source is a baseline
  CONSTRAINT MGMT_CM_COMPARISONS_PK PRIMARY KEY (COMPARISON_GUID),
  CONSTRAINT MGMT_CM_COMPARISONS_UC 
    UNIQUE (COMPARISON_OWNER, COMPARISON_NAME)
) MONITORING;

CREATE TABLE MGMT_CM_COMPARISON_VERSIONS
(
  COMPARISON_GUID		RAW(16) NOT NULL,
  COMPARISON_VERSION	NUMBER NOT NULL, -- Comparison version
  JOB_EXECUTION_ID      RAW(16),
  VERSION_STATUS        VARCHAR(3),
  LEFT_VERSION		NUMBER,  -- Actual baseline version used
  RIGHT_VERSION		NUMBER,  -- Actual baseline version used
  COMPARISON_TIME	DATE,
  TIMEZONE_REGION       VARCHAR2(64),
  CONSTRAINT MGMT_CM_COMPARISON_VERSIONS_FK
    FOREIGN KEY (COMPARISON_GUID) 
    REFERENCES MGMT_CM_COMPARISONS (COMPARISON_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_COMPARISON_VERSIONS_UC 
    UNIQUE(COMPARISON_GUID, COMPARISON_VERSION)
) MONITORING;

CREATE TABLE MGMT_CM_COMPARISON_OBJECTS
(
  COMPARISON_GUID	RAW(16) NOT NULL,
  OBJECT_TYPE		NUMBER NOT NULL,
  OBJECT_SCHEMA         VARCHAR2(30),  -- Left-side schema
  OBJECT_NAME		VARCHAR2(128) NOT NULL,  -- Left-side name
  OBJECT_LONGNAME	CLOB,
  FIRST_VERSION         NUMBER NOT NULL,
  LAST_VERSION          NUMBER DEFAULT 99999 NOT NULL,
  LAST_LEFT_DDL_TIME    DATE,
  LEFT_HASH_VALUE       CHAR(32),
  LAST_RIGHT_DDL_TIME   DATE,
  RIGHT_HASH_VALUE      CHAR(32),
  GRANTS_DIFF_HASH      CHAR(32),
  STATE                 NUMBER NOT NULL,
  ANNOTATION_GUID       RAW(16),
  IGNORED               NUMBER(1) DEFAULT 0,
  DIFFERENCES		BLOB,
  COMMENTS_DIFF_HASH    CHAR(32),
  CONSTRAINT MGMT_CM_COMPARISON_OBJECTS_FK
    FOREIGN KEY (COMPARISON_GUID) 
    REFERENCES MGMT_CM_COMPARISONS (COMPARISON_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_COMPARISON_OBJECT_UC
    UNIQUE (COMPARISON_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 (DIFFERENCES) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

CREATE TABLE MGMT_CM_COMPARISON_INIT_PRMS
(
  COMPARISON_GUID	RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  PARAM_NAME    VARCHAR2(64) NOT NULL,
  STATE			NUMBER NOT NULL,
  LEFT_PARAM_VALUE   VARCHAR2(4000),
  RIGHT_PARAM_VALUE   VARCHAR2(4000),
  CONSTRAINT MGMT_CM_COMPARISON_INITP_FK
    FOREIGN KEY (COMPARISON_GUID) 
    REFERENCES MGMT_CM_COMPARISONS (COMPARISON_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_COMPARISON_INITP_UC
    UNIQUE (COMPARISON_GUID, PARAM_NAME, FIRST_VERSION)
) MONITORING
;
 
CREATE TABLE MGMT_CM_SCHEMA_MAPS
(
  OWNER_ID		RAW(16) NOT NULL,
  LEFT_SCHEMA		VARCHAR2(30) NOT NULL,
  RIGHT_SCHEMA	VARCHAR2(30) NOT NULL,
  CONSTRAINT MGMT_CM_SCHMPS_LEFT_UC UNIQUE (OWNER_ID, LEFT_SCHEMA),
  CONSTRAINT MGMT_CM_SCHMPS_RIGHT_UC UNIQUE (OWNER_ID, RIGHT_SCHEMA)
) MONITORING;

-- Tables to support CM Synch (11g)
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), -- NOT NULL?
  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
;