Rem
Rem $Header: e0801050.sql 09-jun-00.10:09:16 bnainani Exp $
Rem
Rem e0801050.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      e0801050.sql - downgrade Oracle RDBMS from current release to 8.1.5
Rem
Rem    DESCRIPTION
Rem      See the README file and the Oracle Migration book for details.
Rem
Rem      This script is to be used for downgrading your database from the
Rem      current release you have installed to the 8.1.5 release.  This
Rem      script provides a direct downgrade path from your current installed
Rem      release to the 8.1.5 release.
Rem
Rem      This script performs the downgrade in the following stages:
Rem        STAGE 1: downgrade from the current release to 8.1.6;
Rem                 this stage is a no-op for 8.1.6 since the current release
Rem                 is 8.1.6; for the next release:
Rem                 call d0801060.sql to do this.
Rem        STAGE 2: downgrade from 8.1.6 to 8.1.5
Rem
Rem    NOTES
Rem	 * This script must be run using SQL*PLUS
Rem      * This script needs to be run in the current release's environment
Rem        (before installing the release to which you want to downgrade).
Rem
Rem      * You must be connect as INTERNAL to run this script.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bnainani    06/09/00 - drop jms classes during downgrade
Rem    bnainani    05/31/00 - remove aqjms classes during downgrade
Rem    mjungerm    05/17/00 - javavm up/downgrade
Rem    jdavison    05/08/00 - Add e0801060.sql for downgrade
Rem    nireland    02/18/00 - drop dlm_traffic_controllers. #1201953
Rem    rshaikh     12/17/99 - 
Rem    rshaikh     10/21/99 - bug 1045377 - drop aq$_jms_* types
Rem    rshaikh     10/29/99 - call 8.1.6
Rem    anithrak    09/27/99 - Drop v$buffer_pool_statistics
Rem    rshaikh     09/22/99 - Created
Rem    rshaikh     09/22/99 - bug 994326: move to e0801050.sql
Rem    smuthuli    08/30/99 - drop dba_?mt_free_space, dba_?mt_used_extent: 
Rem                              merge from 817
Rem    rshaikh     08/02/99 - downgrade datetime sys types
Rem    attran      07/13/99 - PIOT: set TAB$/pctused to ZERO.
Rem    rshaikh     07/19/99 - remove views that depend on fixed views
Rem    evoss       07/12/99 - v$hs_parameter views                             
Rem    kdias       07/01/99 - nested MJV and MAV1 MVs -> complex MVs 
Rem    cmlim       06/11/99 - lrg33357: drop gv_$loadistat, v_$loadistat
Rem    amganesh    06/02/99 - drop new 8.1.6 synonyms
Rem    nvishnub    04/19/99 - Drop index on expdepobj$ for parent object.
Rem    rshaikh     02/12/99 - Created
Rem

Rem =========================================================================
Rem BEGIN STAGE 1: downgrade from the current release to 8.1.6
Rem =========================================================================

@@e0801060.sql

Rem =========================================================================
Rem END STAGE 1: downgrade from the current release to 8.1.6
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 2: downgrade from 8.1.6 to 8.1.5
Rem =========================================================================

REM drop datetime system types
CREATE LIBRARY UPGRADE_LIB TRUSTED AS STATIC
/

CREATE OR REPLACE PROCEDURE downgrade_system_types_to_815 IS
LANGUAGE C
NAME "FROM_816"
LIBRARY UPGRADE_LIB;
/

execute downgrade_system_types_to_815();

REM Add any downgrade here!!!
drop index i_parentobj
/

REM Begin of Drop all new views and synonyms created in 816
drop view v_$hs_parameter;
drop public synonym v$hs_parameter;

drop view v_$bsp;
drop public synonym v$bsp;

drop view v_$fast_start_servers;
drop public synonym v$fast_start_servers;

drop view v_$fast_start_transactions;
drop public synonym v$fast_start_transactions;

drop view v_$loadistat;
drop public synonym v$loadistat;

drop view v_$buffer_pool_statistics;
drop public synonym v$buffer_pool_statistics;

drop view gv_$hs_parameter;
drop public synonym gv$hs_parameter;

drop view gv_$bsp;
drop public synonym gv$bsp;

drop view gv_$obsolete_parameter;
drop public synonym v$obsolete_parameter;

drop view gv_$fast_start_servers;
drop public synonym gv$fast_start_servers;

drop view gv_$fast_start_transactions;
drop public synonym gv$fast_start_transactions;

drop view gv_$loadistat;
drop public synonym gv$loadistat;

drop view gv_$buffer_pool_statistics;
drop public synonym gv$buffer_pool_statistics;

drop public synonym gv$dlm_traffic_controller;
drop view gv_$dlm_traffic_controller;

drop public synonym v$dlm_traffic_controller;
drop view v_$dlm_traffic_controller;

drop view USER_SOURCE;
drop view DBA_SOURCE;
drop view ALL_SOURCE;

drop view dba_dmt_free_space;
drop view dba_lmt_free_space;
drop view dba_dmt_used_extents;
drop view dba_lmt_used_extents;

drop package dbms_utility;

REM set pctused of ALL IOTs to zero.
update TAB$ set pctused$=0 where bitand(property, 64)!=0;

REM End of Drop all new views and synonyms created in 816

--- =====================================================================
--- BEGIN Downgrade for V8.1.6 non-complex Nested Materialized Views
--- =====================================================================
---
--- Convert all nested MJVs and MAV1s to COMPLEX
---
DECLARE
  new_flag NUMBER;
  --- find the relevent MVs
  CURSOR nest_mjvmav1 IS
    SELECT sowner, vname, flag FROM snap$ sn
     WHERE bitand(flag,24576) !=0 and bitand(flag,67108864) !=0
    FOR UPDATE;
BEGIN
  FOR mv IN nest_mjvmav1 LOOP
    new_flag := mv.flag;
    IF dbms_ijob.bit(new_flag, 8192) THEN ---KKZFJVS
      new_flag := new_flag - 8192;
    END IF;
    IF dbms_ijob.bit(new_flag, 16384) THEN ---KKZFAV1
      new_flag := new_flag - 16384;
    END IF;
    new_flag := new_flag + 256; ---KKZFUCX

    UPDATE snap$ s SET s.flag = new_flag
     WHERE s.sowner = mv.sowner AND s.vname = mv.vname; 
  END LOOP;
  COMMIT;
END;
/    
--- =====================================================================
--- END Downgrade for V8.1.6 non-complex Nested Materialized Views
--- =====================================================================   

drop package DBMS_AQJMS
/

drop type aq$_jms_text_message;
drop type aq$_jms_bytes_message;
drop type aq$_jms_stream_message;
drop type aq$_jms_map_message;
drop type aq$_jms_object_message;
drop type aq$_jms_header;
drop type aq$_jms_userproparray;
drop type aq$_jms_userproperty;

REM Downgrade JAVAVM from 8.1.6 to 8.1.5
@@jvme815


Rem =========================================================================
Rem END STAGE 2: downgrade from 8.1.6 to 8.1.5
Rem =========================================================================

Rem *************************************************************************
Rem END e0801050.sql
Rem *************************************************************************
