Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="provision_schema_upgrade.sql+" condition="EM_REPOS_MODE=CENTRAL"/>
Rem
Rem $Header: esm_schema_upgrade.sql 23-apr-2007.23:28:48 jsadras Exp $
Rem
Rem esm_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      esm_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    jsadras     04/23/07 - Backport jsadras_bug-5934433 from main
Rem    jsadras     04/03/07 - Bug:5964364: Make this file GC specific
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    eujang      03/10/05 - eujang_rep_upgrade_debug
Rem    dsukhwal    02/22/05 - Created
Rem


CREATE TABLE esm_collection_new_dummy(
  "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "esm_collection0_2" 
                            REFERENCES mgmt_ecm_gen_snapshot (snapshot_guid) 
							ON DELETE CASCADE,
  "PROPERTY" VARCHAR2(64),
  "VALUE" VARCHAR2(512),
  "VALUE2" VARCHAR2(512) DEFAULT 'NA',
  CONSTRAINT "esm_collection_pk_2" 
  PRIMARY KEY ("ECM_SNAPSHOT_ID", "PROPERTY", "VALUE", "VALUE2")
)
ORGANIZATION INDEX
COMPRESS 2
MONITORING;

CREATE TABLE mgmt_esa_report (
  "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "mgmt_esa_report0" 
                            REFERENCES mgmt_ecm_gen_snapshot (snapshot_guid) 
							ON DELETE CASCADE,
  "PRINCIPAL" VARCHAR2(512),
  "OBJECT_NAME" VARCHAR2(512),
  "REPORT_NAME" VARCHAR2(512),
  CONSTRAINT "MGMT_ESA_REPORT_PK" 
  PRIMARY KEY ("ECM_SNAPSHOT_ID", "PRINCIPAL", "OBJECT_NAME", "REPORT_NAME")
)
ORGANIZATION INDEX
COMPRESS
MONITORING;

ALTER TABLE esm_collection 
      ADD "VALUE2" VARCHAR2(512) DEFAULT 'NA' NOT NULL;
ALTER TABLE esm_collection 
      MODIFY "VALUE" VARCHAR2(512);


DECLARE
  no_errors NUMBER := 0;
BEGIN
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('&&EM_REPOS_USER', 'esm_collection', 'esm_collection_new_dummy');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('&&EM_REPOS_USER', 'esm_collection', 'esm_collection_new_dummy');
    no_errors := 1;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('&&EM_REPOS_USER', 'esm_collection', 'esm_collection_new_dummy');
	  EXECUTE IMMEDIATE 'ALTER TABLE esm_collection DROP COLUMN "VALUE2" ';
	  EXECUTE IMMEDIATE 'ALTER TABLE esm_collection MODIFY "VALUE" VARCHAR2(64)' ;
	  EXECUTE IMMEDIATE 'DROP TABLE mgmt_esa_report';
  END;

  IF no_errors = 1 THEN 
    EXECUTE IMMEDIATE 'DROP TABLE esm_collection_new_dummy';
    EXECUTE IMMEDIATE 'ALTER TABLE esm_collection '||
	                  'RENAME CONSTRAINT "esm_collection_pk_2" TO "ESM_COLLECTION_PK"';
    EXECUTE IMMEDIATE 'ALTER INDEX "esm_collection_pk_2" '||
	                  'RENAME TO "ESM_COLLECTION_PK" ';
	EXECUTE IMMEDIATE 'ALTER TABLE esm_collection '||
	                  'RENAME CONSTRAINT "esm_collection0_2" TO "ESM_COLLECTION0" ';
  END IF;
END;
/