Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="audit_schema_upgrade.sql+"/>
Rem
Rem $Header: availability_schema_upgrade.sql 25-jul-2005.13:45:41 chyu Exp $
Rem
Rem availability_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      availability_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        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    rpinnama    05/16/05 - Remove max_avail_sev_ts column
Rem    rpinnama    05/16/05 - Add max_avail_sev_ts column to 
Rem                           mgmt_availability_marker table 
Rem    jsadras     03/30/05 - Bug:3952025, convert mgmt_availability to IOT 
Rem    eujang      03/03/05 - remove the extra pk indexes that was already 
Rem                           renamed 
Rem    eujang      03/03/05 - add the MGMT_CURRENT_AVAILABILITY_PK index 
Rem    scgrover    02/07/05 - fix upgrade 
Rem    scgrover    01/27/05 - add table 
Rem    scgrover    01/26/05 - scgrover_bug-4092276
Rem    scgrover    01/26/05 - add index rename 
Rem    scgrover    01/26/05 - Created
Rem

CREATE TABLE MGMT_CURRENT_AVAILABILITY_INT
    (target_guid                RAW(16)        NOT NULL,
     severity_guid              RAW(16)        DEFAULT NULL,
     current_status             NUMBER         NOT NULL,
     start_collection_timestamp DATE           NOT NULL,
     CONSTRAINT MGMT_CURRENT_AVAIL_PK_INT PRIMARY KEY (target_guid)
) ORGANIZATION INDEX MONITORING;

Rem redefine the table to be an IOT
DECLARE
  no_errors NUMBER := 0;
BEGIN
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_CURRENT_AVAILABILITY', 'MGMT_CURRENT_AVAILABILITY_INT');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_CURRENT_AVAILABILITY', 'MGMT_CURRENT_AVAILABILITY_INT');
    no_errors := 1;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_CURRENT_AVAILABILITY', 'MGMT_CURRENT_AVAILABILITY_INT');
  END;

  EXECUTE IMMEDIATE 'DROP TABLE MGMT_CURRENT_AVAILABILITY_INT';

  IF no_errors = 1 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_CURRENT_AVAILABILITY RENAME CONSTRAINT MGMT_CURRENT_AVAIL_PK_INT TO CUR_AVAIL_PRIMARY_KEY';
    EXECUTE IMMEDIATE 'ALTER INDEX MGMT_CURRENT_AVAIL_PK_INT RENAME TO MGMT_CURRENT_AVAILABILITY_PK';
  END IF;
END;
/


Rem redefine MGMT_AVAILABILITY_MARKER as an iot

CREATE TABLE MGMT_AVAILABILITY_MARKER_INT
    (target_guid                RAW(16)        NOT NULL,
     marker_timestamp           DATE           NOT NULL,
     marker_avail_status        NUMBER         NOT NULL,
     CONSTRAINT MGMT_AVAIL_MARKER_PK_INT PRIMARY KEY (target_guid)
    )
    ORGANIZATION INDEX MONITORING;

DECLARE
  no_errors NUMBER := 0;
BEGIN
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_AVAILABILITY_MARKER', 'MGMT_AVAILABILITY_MARKER_INT');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_AVAILABILITY_MARKER', 'MGMT_AVAILABILITY_MARKER_INT');
    no_errors := 1;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_AVAILABILITY_MARKER', 'MGMT_AVAILABILITY_MARKER_INT');
  END;

  EXECUTE IMMEDIATE 'DROP TABLE MGMT_AVAILABILITY_MARKER_INT';

  IF no_errors = 1 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_AVAILABILITY_MARKER RENAME CONSTRAINT MGMT_AVAIL_MARKER_PK_INT TO MGMT_AVAIL_MARKER_PK';
    EXECUTE IMMEDIATE 'ALTER INDEX MGMT_AVAIL_MARKER_PK_INT RENAME TO MGMT_AVAIL_MARKER_PK';
  END IF;
END;
/

Rem redefine mgmt_availability as IOT

CREATE TABLE MGMT_AVAILABILITY_INT
    (target_guid                RAW(16)        NOT NULL,
     severity_guid              RAW(16)        DEFAULT NULL,
     current_status             NUMBER         NOT NULL,
     start_collection_timestamp DATE           NOT NULL,
     end_collection_timestamp   DATE           DEFAULT NULL,
    CONSTRAINT availability_primary_key_int
       PRIMARY KEY (target_guid, start_collection_timestamp, current_status))
  ORGANIZATION INDEX
  COMPRESS 1
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING
/

DECLARE
  no_errors NUMBER := 0;
BEGIN
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_AVAILABILITY', 'MGMT_AVAILABILITY_INT');
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_AVAILABILITY', 'MGMT_AVAILABILITY_INT');
    no_errors := 1;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_REDEFINITION.ABORT_REDEF_TABLE('&&EM_REPOS_USER', 'MGMT_AVAILABILITY', 'MGMT_AVAILABILITY_INT');
  END;

  EXECUTE IMMEDIATE 'DROP TABLE MGMT_AVAILABILITY_INT';

  IF no_errors = 1 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MGMT_AVAILABILITY 
                       RENAME CONSTRAINT availability_primary_key_int TO availability_primary_key';
    EXECUTE IMMEDIATE 'ALTER INDEX availability_primary_key_int RENAME TO availability_primary_key';
  END IF;
END;
/