Rem drv: <migrate type="schema_downgrade" version="10.1.0.4"/>
Rem
Rem $Header: availability_schema_downgrade.sql 19-jul-2005.13:21:59 chyu Exp $
Rem
Rem availability_schema_downgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      availability_schema_downgrade.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/19/05 - modifying the new rep manager header 
Rem    rpinnama    05/16/05 - Remove max_avail_sev_ts column from 
Rem                           mgmt_availability_marker 
Rem    scgrover    02/07/05 - scgrover_bug-4169595
Rem    scgrover    02/07/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)
) MONITORING;

Rem redefine the table to be an regular
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_AVAIL_PK';
  END IF;
END;
/

Rem redefine MGMT_AVAILABILITY_MARKER as a regular table

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