Rem
Rem $Header: e0800050.sql 01-nov-99.17:13:50 rshaikh Exp $
Rem
Rem e0800050.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      e0800050.sql - <one-line expansion of the name>
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.0.5 release.  This
Rem      script provides a direct downgrade path from your current installed
Rem      release to the 8.0.5 release.
Rem
Rem      This script performs the downgrade in the following stages:
Rem        STAGE 1: downgrade from the current release to 8.1.5;
Rem        STAGE 2: downgrade from 8.1.5 to 8.1.4;
Rem        STAGE 3: downgrade from 8.1.4 to 8.1.3;
Rem        STAGE 4: downgrade from 8.1.3 to 8.0.5
Rem
Rem    NOTES
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       rshaikh  11/01/99 - move 8.1.3 and 8.1.4 upgrade here
Rem       rshaikh  09/22/99 - Created
Rem       rshaikh  09/22/99 - bug 994326: move to e0800050.sql
Rem       rshaikh  08/18/99 - lrg34212: recreate dbms_utility
Rem       wesmith  07/23/99 - Bug 928696: metadata of downgraded aggregate and 
Rem                           join view snapshots is incorrect
Rem       rshaikh  07/15/99 - change catspaced to catspacd
Rem       rshaikh  03/10/99 - fix Y2K problem in snap_reftime$
Rem       bnainani 01/07/99 - remove drop dbms_aqin-move to d080103
Rem       bnainani 12/22/98 - drop dbms_aqin during downgrade
Rem       rshaikh  12/16/98 - remove more privileges
Rem       rshaikh  12/04/98 - recompile types on downgrade
Rem       rshaikh  10/26/98 - remove drop tables
Rem       tlahiri  11/09/98 - Add (g)v_instance_recovery views
Rem       rshaikh  09/09/98 - remove sys types downgrade;
Rem			      don't drop ustats$ (needed by 8.1.4)
Rem       ncramesh 08/04/98 - change for sqlplus
Rem       rshaikh  07/21/98 - call d0801030.sql
Rem				move outline
Rem       rshaikh  07/31/98 - remove obsolete fixed view info
Rem       rshaikh  07/21/98 - remove 8.1.3 sys privs
Rem       liwong   07/21/98 - Add repcat$_repcat.flag                      
Rem       jingliu  07/13/98 - downgrade for snap_reftime$             
Rem       rwessman 07/09/98 - Removed proxy# column from i_audit index         
Rem       smuralid 07/10/98 - downgrade system type tables                     
Rem       rshaikh  06/25/98 - fix second downgrade
Rem       ato      06/25/98 - AQ downgrade fix
Rem       rshaikh  06/18/98 - add type downgrade
Rem       arhee    06/15/98 - change name for db scheduler
Rem       ato      06/12/98 - remove queue objects
Rem       rshaikh  06/11/98 - reset compatibility after dropping atemptab
Rem       mkamath  06/10/98 - Adding downgrade for AQ Propagation Scheduling
Rem       akalra   06/09/98 - catschedno.sql -> catrmno.sql
Rem       hasun    06/08/98 - Snapshot/Materialized View downgrade             
Rem       nbhatt   06/08/98 - drop AQ queue table affinites table
Rem       jfinnert 06/05/98 - Reflect changes for summary mgmt
Rem       gclaborn 06/04/98 - Separate new export tables
Rem       usundara 06/03/98 - rework upgrade/downgrade/migration scripts:
Rem                           * change name c813d805.sql --> d0800050.sql
Rem                           * fix comments
Rem                           * add changes from RSHAIKH
Rem       bgoyal   05/19/98 - downgrade script for temporary tables            
Rem       gclaborn 05/19/98 - Add drop of export tables for proc. obj support
Rem       rshaikh  05/18/98 - remove proxy
Rem       svivian  05/07/98 - delete OUTLN tuples from expact
Rem       rshaikh  05/01/98 - add catspace.sql downgrade, remove type$ column
Rem		              fix sys priv statement
Rem       liwong   04/27/98 - set repobject.status to ERROR for $RP/$RL
Rem       rshaikh  04/20/98 - drop sys priv, aud opts, trigger, java, mls,
Rem       mdepledg 04/19/98 - invalidate protected bitmap indexes              
Rem       akalra   04/17/98 - Downgrade for db scheduler
Rem       clei     04/08/98 - Remove rls$ for downgrade
Rem       dmwong   04/03/98 - drop context support in pre-8.1                  
Rem       amozes   03/30/98 - drop package dbms_stats                          
Rem       pravelin 04/01/98 - Delete HS fixed views
Rem       jfeenan  03/24/98 - Remove sumdelta$ and sumdep$ along with their ind
Rem       jfeenan  03/20/98 - Add downgrade information for summaries and dimen
Rem       jstamos  02/27/98 - flavor support                                   
Rem       mmonajje 01/26/98 - Created 8.1.3 to 8.0.5 downgrade script
Rem


Rem =========================================================================
REM    use to be d0801040.sql
Rem =========================================================================
Rem    rshaikh     09/22/99 - bug 994326: call e0801050.sql
Rem    rshaikh     02/12/99 - call 816 script
Rem    rherwadk    02/12/99 - compatibility for parameter tables & views
Rem    vekrishn    01/07/99 - Handle RepCat table constraints during downgrade 
Rem    avaradar    12/30/98 - support for multiple snapshot repgroups          
Rem    liwong      12/07/98 - Support multiple grps at same snap site          
Rem    jiyang      11/16/98 - Delete NLS_UNION_CURRENCY first if there is anyon
Rem    jiyang      11/12/98 - downgrade NLS_DUAL_CURRENCY to NLS_UNION_CURRENCY
Rem    rshaikh     11/17/98 - change query rewrite=>global rewrite
Rem				(for mziauddi)
Rem    masubram    11/07/98 - delete generated queries                         
Rem    rshaikh     11/09/98 - remove ADMINISTER DATABASE TRIGGER
Rem    wesmith     11/03/98 - Populate columns obsoleted as of 8.1.5 in
Rem                           system.repcat_template_sites
Rem    sbasu       10/14/98 -  replace NULL (used to represent default values)
Rem			 with 2147483647 in  PARTOBJ$.[DEFTINIEXTSDEFTEXTSIZE|
Rem			 DEFMINEXTS|DEFMAXEXTS|DEFEXTPCT]
Rem    atsukerm    10/12/98 - reset trigger IOT property.
Rem    rshaikh     10/05/98 - Created
Rem

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

@@e0801050.sql

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

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

REM Add any downgrade here!!!

Rem delete all the snapshot refresh operations.
UPDATE sys.snap$ SET status=0
/
DELETE FROM sys.snap_refop$
/
COMMIT
/
Rem Set the fcmaskvec and ejmaskvec to NULL
UPDATE sys.snap_reftime$ SET fcmaskvec = NULL, ejmaskvec = NULL
/
COMMIT
/
Rem Delete the primary key column entries in mlog_refcol$
DELETE FROM sys.mlog_refcol$ WHERE bitand(nvl(flag, 0), 2) = 2
/
Rem Set the flag to 0 for all the filter columns
UPDATE sys.mlog_refcol$ SET flag = 0
/
COMMIT
/
Rem
Rem system.repcat$_template_sites
Rem

desc system.repcat$_template_sites;

Rem drop constraints
alter table system.repcat$_template_sites
drop constraint repcat$_template_sites_u1;

alter table system.repcat$_template_sites
drop constraint repcat$_template_sites_c2;

Rem remove the not null constraints
alter table system.repcat$_template_sites
modify user_name null;

alter table system.repcat$_template_sites
modify refresh_template_name null;

Rem update the new columns
update system.repcat$_template_sites ts
set refresh_template_id = 
  (select rt.refresh_template_id
   from system.repcat$_refresh_templates rt
   where rt.refresh_template_name = ts.refresh_template_name
   and rt.refresh_group_name = ts.refresh_group_name
   and rt.owner = ts.template_owner),
user_id = 
  (select u.user_id
   from sys.dba_users u
   where u.username = ts.user_name);

Rem update the site_name
update system.repcat$_template_sites ts
set site_name = 
  (select s.site_name
   from sys.snap_site$ s
   where s.site_id = ts.repapi_site_id)
where ts.site_name is NULL
and ts.repapi_site_id is not null;
  
Rem set up the not null constraints
alter table system.repcat$_template_sites
modify site_name not null;

alter table system.repcat$_template_sites
modify user_id not null;

alter table system.repcat$_template_sites
modify refresh_template_id not null;

Rem set up the named constraints
alter table system.repcat$_template_sites 
add constraint repcat$_template_sites_fk1 foreign key (refresh_template_id)
 references system.repcat$_refresh_templates on delete cascade;

alter table system.repcat$_template_sites
add constraint repcat$_template_sites_u1 unique
   (site_name,user_id,refresh_template_id);

Rem
Rem END system.repcat$_template_sites
Rem

Rem
Rem system.def$_pushed_transactions
Rem

Rem drop the constraints we created
alter table system.def$_pushed_transactions
drop constraint def$_pushed_tran_primary;

Rem populate the table
update system.def$_pushed_transactions pt
set source_site = 
 (select s.site_name
  from sys.snap_site$ s
  where s.site_id = pt.source_site_id);

Rem set up the new constraints
alter table system.def$_pushed_transactions
add constraint def$_pushed_tran_primary
PRIMARY KEY(source_site);

Rem
Rem END system.def$_pushed_transactions
Rem

CREATE or replace PROCEDURE revoke_versions_privilege (priv IN VARCHAR2) IS

 cursor C1 is select  
           'REVOKE ' || priv || ' from ' || u.name  
           from sysauth$ s, user$ u, SYSTEM_PRIVILEGE_MAP smp
           where smp.name = priv and smp.privilege = s.privilege#
	   and s.grantee# = u.user#;
	   
  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  iterations number;
  loop_count number;
  my_err     number;
  validate   number;
BEGIN
 loop_count := 0;
 -- To make sure we eventually stop, pick a max number of iterations
 select count(*) into iterations from sysauth$ s, SYSTEM_PRIVILEGE_MAP smp
 	where smp.name = priv and smp.privilege = s.privilege#;

 DDL_CURSOR := dbms_sql.open_cursor;
 OPEN C1;

 LOOP

   BEGIN
     FETCH C1 INTO ddl_statement;
     EXIT WHEN C1%NOTFOUND OR loop_count > iterations;
   EXCEPTION
    WHEN OTHERS THEN
      my_err := SQLCODE;
      IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
       CLOSE C1;
       -- Here is why C1 orders by obj#.  When we restart the query, we 
       -- will only find object with obj# greater than the last one tried.
       -- This keeps us from re-trying objects that failed.
       OPEN  C1;
       GOTO continue;
      ELSE
       RAISE;
      END IF;
   END;

   BEGIN
     -- Issue the Alter Statement  (Parse implicitly executes DDLs)
     dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
   EXCEPTION
     WHEN OTHERS THEN
       null; -- ignore, and proceed.
   END;


 <<continue>>
   loop_count := loop_count + 1;
 END LOOP;
 dbms_sql.close_cursor(DDL_CURSOR);
 CLOSE C1;
END;
/



REM
REM change the name of the privilege back to what it
REM was in 8.1.4
REM
update SYSTEM_PRIVILEGE_MAP set 
        name='REWRITE' where name='QUERY REWRITE';

update SYSTEM_PRIVILEGE_MAP set 
        name='GLOBAL REWRITE' where name=' GLOBAL QUERY REWRITE';

update STMT_AUDIT_OPTION_MAP set 
        name='REWRITE' where name='QUERY REWRITE';

update STMT_AUDIT_OPTION_MAP set 
        name='GLOBAL REWRITE' where name='GLOBAL QUERY REWRITE';

Rem ==================================================================
Rem For partitioned tables/indexes, if no default table/index level storage 
Rem attr.
Rem were specified then we were storing NULLs's in PARTOBJ$.[DEFTINIEXTS|
Rem DEFTEXTSIZE|DEFMINEXTS|DEFMAXEXTS|DEFEXTPCT], replace them with 2147483647
Rem ===================================================================
update partobj$ set deftiniexts = 2147483647 where deftiniexts is null
/

update partobj$ set defextsize = 2147483647 where defextsize is null
/

update partobj$ set defminexts = 2147483647 where defminexts is null
/

update partobj$ set defmaxexts = 2147483647 where defmaxexts is null
/

update partobj$ set defextpct = 2147483647 where defextpct is null
/

alter table partobj$ modify (deftiniexts number not null,
                             defextsize  number not null,
                             defminexts  number not null,
                             defmaxexts  number not null,
                             defextpct   number not null )
/

commit
/

Rem Reset trigger property for triggers on IOTs
update trigger$ set property = bitand(property,63)
 where bitand(property,64) <> 0;
/

Rem First remove NLS_UNION_CURRENCY if there is any
delete from sys.props$ where name='NLS_UNION_CURRENCY';

Rem Downgrate NLS_DUAL_CURRENCY to NLS_UNION_CURRENCY
update sys.props$
 set name='NLS_UNION_CURRENCY', comment$='Union currency symbol'
 where name='NLS_DUAL_CURRENCY';
commit;

execute revoke_versions_privilege('ADMINISTER DATABASE TRIGGER')
/

delete from SYSTEM_PRIVILEGE_MAP where privilege=-228
/

Rem
Rem BEGIN support for multiple grps at same snap site
Rem All repgroups with an owner other than 'PUBLIC' are
Rem dropped.
Rem All generated objects now have a group name which is the 
Rem same as the group name for the base object and a group
Rem owner which is PUBLIC.
Rem Internal packages are assigned a group name and a group
Rem owner. These are the same as the group name and the
Rem group owner for the base snapshot.
Rem

DECLARE
  CURSOR groups_to_be_del IS 
         SELECT sname, gowner from system.repcat$_repcat 
           WHERE gowner != 'PUBLIC';

  CURSOR gen_obj IS 
         SELECT sname, oname, type, base_sname, base_oname, base_type 
           FROM system.repcat$_generated;

  CURSOR int_pkg IS 
     SELECT sname, oname 
       FROM system.repcat$_repobject
       WHERE type = -4;               --internal packages

  group_name  VARCHAR2(30);
  group_owner VARCHAR2(30);
  snap_sname  VARCHAR2(30);
  snap_oname  VARCHAR2(30);
BEGIN

  FOR grp IN groups_to_be_del LOOP
    dbms_repcat.drop_snapshot_repgroup(gname=>grp.sname,gowner=>grp.gowner);
  END LOOP;

  FOR obj IN gen_obj LOOP
    SELECT gname, gowner INTO group_name, group_owner 
     FROM system.repcat$_repobject r
       WHERE r.sname = obj.base_sname AND r.oname = obj.base_oname 
        AND r.type = obj.base_type;
    UPDATE system.repcat$_repobject 
      SET gname = group_name, gowner = group_owner
        WHERE sname = obj.sname AND oname = obj.oname 
          AND type = obj.type;
  END LOOP;

  FOR obj IN int_pkg LOOP
    SELECT vname, sowner INTO snap_oname, snap_sname
      FROM sys.snap$ 
        WHERE mowner = obj.sname AND master = obj.oname
          AND (vname, sowner) IN (SELECT oname, sname 
                                    FROM system.repcat$_repobject
                                     WHERE type IN (-1,-3)); 
    SELECT gname, gowner INTO group_name, group_owner
      FROM system.repcat$_repobject 
       WHERE sname = snap_sname AND oname = snap_oname
         AND type  IN (-1, -3); -- snapshot/ updatable snapshot
    UPDATE system.repcat$_repobject 
      SET gname = group_name, gowner = group_owner
       WHERE sname = obj.sname AND oname = obj.oname
         AND type = -4;
   END LOOP;
END;
/

COMMIT
/

Rem
Rem END support for multiple grps at same snap site
Rem

DELETE FROM system.repcat$_snapgroup WHERE gowner != 'PUBLIC';
COMMIT
/

DELETE FROM system.repcat$_repgroup_privs WHERE
  gowner IS NOT NULL AND gowner != 'PUBLIC';
COMMIT
/

---
--- DROP FK Constraints
---
--- repschema
ALTER TABLE system.repcat$_repschema DROP CONSTRAINT repcat$_repschema_prnt;

--- repobject
ALTER TABLE system.repcat$_repobject DROP CONSTRAINT repcat$_repobject_prnt;

--- repgroup_privs
ALTER TABLE system.repcat$_repgroup_privs DROP CONSTRAINT 
repcat$_repgroup_privs_fk;

--- flavor_objects
ALTER TABLE system.repcat$_flavor_objects DROP CONSTRAINT 
repcat$_flavor_objects_fk1;
ALTER TABLE system.repcat$_flavor_objects DROP CONSTRAINT
repcat$_flavor_objects_fk2;

--- flavors
ALTER TABLE system.repcat$_flavors DROP CONSTRAINT repcat$_flavors_fk1;

---
--- REPLACE PK Constraints
---
--- repcat
ALTER TABLE system.repcat$_repcat DROP CONSTRAINTS repcat$_repcat_primary;
ALTER TABLE system.repcat$_repcat ADD CONSTRAINT repcat$_repcat_primary
  PRIMARY KEY(sname)
/

--- repschema
ALTER TABLE system.repcat$_repschema DROP CONSTRAINT repcat$_repschema_primary;
ALTER TABLE system.repcat$_repschema ADD CONSTRAINT repcat$_repschema_primary
  PRIMARY KEY(sname, dblink)
/

--- repgroup_privs
ALTER TABLE system.repcat$_repgroup_privs DROP CONSTRAINT 
repcat$_repgroup_privs_uk;
ALTER TABLE system.repcat$_repgroup_privs ADD CONSTRAINT
  repcat$_repgroup_privs_uk UNIQUE (userid, gname)
/

--- flavors
ALTER TABLE system.repcat$_flavors DROP CONSTRAINT repcat$_flavors_unq1
  CASCADE
/
ALTER TABLE system.repcat$_flavors ADD CONSTRAINT repcat$_flavors_unq1
  UNIQUE (gname, flavor_id)
/

--- flavor_objects
ALTER TABLE system.repcat$_flavor_objects DROP CONSTRAINT
  repcat$_flavor_objects_pk
/
ALTER TABLE system.repcat$_flavor_objects ADD 
  CONSTRAINT repcat$_flavor_objects_pk
     PRIMARY KEY (sname, oname, type, gname, flavor_id)
/

--- snapgroup
DROP INDEX system.i_repcat$_snapgroup1;
CREATE UNIQUE INDEX system.i_repcat$_snapgroup1 ON 
  system.repcat$_snapgroup(gname, dblink)
/

---
--- Replace FK Constraints
---
--- repschema
ALTER TABLE system.repcat$_repschema  ADD CONSTRAINT repcat$_repschema_prnt 
  FOREIGN KEY(sname)
    REFERENCES system.repcat$_repcat(sname)
      ON DELETE CASCADE
/

--- repobject
ALTER TABLE system.repcat$_repobject ADD CONSTRAINT repcat$_repobject_prnt
  FOREIGN KEY(gname) REFERENCES system.repcat$_repcat(sname)
  ON DELETE CASCADE
/

--- repgroup_privs
ALTER TABLE system.repcat$_repgroup_privs
  ADD CONSTRAINT repcat$_repgroup_privs_fk  
    FOREIGN KEY (gname)
      REFERENCES system.repcat$_repcat(sname)
        ON DELETE CASCADE
/

--- flavor_objects
ALTER TABLE system.repcat$_flavor_objects ADD CONSTRAINT
   repcat$_flavor_objects_fk1
     FOREIGN KEY (gname)
       REFERENCES system.repcat$_repcat(sname)
         ON DELETE CASCADE
/
ALTER TABLE system.repcat$_flavor_objects ADD CONSTRAINT
  repcat$_flavor_objects_fk2
    FOREIGN KEY (gname, flavor_id)
      REFERENCES system.repcat$_flavors(gname, flavor_id)
        ON DELETE CASCADE
/

--- flavors
ALTER TABLE system.repcat$_flavors ADD  CONSTRAINT repcat$_flavors_fk1
  FOREIGN KEY (gname) REFERENCES system.repcat$_repcat(sname)
  ON DELETE CASCADE
/

---
--- Indexes
---
DROP INDEX system.repcat$_flavors_gname;
CREATE UNIQUE INDEX system.repcat$_flavors_gname ON
  system.repcat$_flavors(gname, fname)
/

DROP INDEX system.repcat$_repobject_gname;
CREATE INDEX system.repcat$_repobject_gname ON
  system.repcat$_repobject(gname, oname, type)
/

DROP INDEX system.repcat$_flavor_objects_fg;
CREATE INDEX system.repcat$_flavor_objects_fg ON
  system.repcat$_flavor_objects(flavor_id, gname)
/

Rem =========================================================================
Rem Parameter fixed views:
Rem GV$PARAMETER2, V$PARAMETER2, GV$SYSTEM_PARAMETER2, V$SYSTEM_PARAMETER2
Rem =========================================================================

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

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

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

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

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

Rem =========================================================================
Rem BEGIN STAGE 3: downgrade 8.1.4 to 8.1.3
REM    use to be d0801030.sql
Rem =========================================================================

Rem    bnainani  01/07/99 - drop dbms_aqin during downgrade
Rem    rshaikh   11/17/98 - remove table drops
Rem    rshaikh   11/17/98 - remove exu8tneb and drop tables
Rem    rshaikh   11/09/98 - move revoke_versions_privilege to 8.1.5 downgrade
Rem    rshaikh   10/05/98 - update for 815
Rem    rshaikh   09/11/98 - move stage1 to the top
Rem    syeung    09/03/98 - set obj$.dataobj# to obj# for composite partitions
Rem                         (8.1.3)
Rem    attran    09/01/98 - PIOT: dataobj# = MINUB4MAXVAL ((ub4) 4294967295)
Rem    syeung    08/26/98 - [tab|ind]compart$.dataobj# are not nullble in 8.1.3
Rem    nagarwal  08/19/98 - downgrade for ext idx privs
Rem    nagarwal  08/17/98 - remove EXE ANY OPER/IDXTYPE privs
Rem    attran      08/18/98 - PIOT: add NOT-NULL constraint
Rem				 on dataobj# of tabpart$
Rem    amozes	   07/14/98 - mon_mods$
Rem    rshaikh     07/14/98 - create downgrade script
Rem    rshaikh     07/14/98 - Created
Rem

REM remove execute any operator and execute any indextype privileges

revoke EXECUTE ANY OPERATOR, EXECUTE ANY INDEXTYPE from dba;
/
execute revoke_versions_privilege('EXECUTE ANY OPERATOR');
/
execute revoke_versions_privilege('EXECUTE ANY INDEXTYPE');
/

delete from SYSTEM_PRIVILEGE_MAP where 
  PRIVILEGE=-204  or PRIVILEGE=-212
/
 

Rem Don't drop mon_mods$ because it may be accessed during downgrade
update tab$ set flags = flags - 2097152 
where bitand(flags,2097152) = 2097152
/
alter system flush shared_pool
/
delete from mon_mods$
/

Rem Trigger downgrade
update trigger$ set nttrigatt = null
/
delete from viewtrcol$
/

Rem The column dataobj# of tabpart$ and tabsubpart$ are not-null in 8.1.3
update tabpart$ set dataobj# = 4294967295 where dataobj# is null;
update tabsubpart$ set dataobj# = 4294967295 where dataobj# is null;
alter table tabpart$ modify (dataobj# number not null);
alter table tabsubpart$ modify (dataobj# number not null);
/
Rem
Rem [tab|ind]compart$.dataobj# are nullable in 8.1.4 but not in 8.1.3
Rem

update tabcompart$ set dataobj#=obj# where dataobj# is null
/
update indcompart$ set dataobj#=obj# where dataobj# is null
/
alter table tabcompart$ modify (dataobj# number not null)
/
alter table indcompart$ modify (dataobj# number not null)
/
update obj$ set dataobj#=obj# where obj# in
    (select tcp.obj# from tabcompart$ tcp)
/
update obj$ set dataobj#=obj# where obj# in 
    (select icp.obj# from indcompart$ icp)
/ 

REM
REM  Drop DBMS_AQIN package - created in 8.1.4 for java aq api  
REM
DROP PACKAGE dbms_aqin
/  
  
  
REM
REM remove import/export views
REM
drop view exu8tneb
/
drop view EXU81JAVI
/
drop view EXU81JAVC
/

drop type rowset
/

Rem =========================================================================
Rem END STAGE 3: downgrade from 8.1.4 to 8.1.3
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 4: downgrade from 8.1.3 to 8.0.5
Rem =========================================================================
REM 
REM recompile all types under 8.0 compatibility because 
REM 8.0 format operands are seen as null while 8.1 format
REM operands are a zero length string.  This causes compilation
REM errors on the lower version.  Recompile here so that we
REM do run into compilation errors later.
REM

REM lrg34212
@@dbmsutil
@@prvtutil.plb

update obj$ set status = 6 
        where type# in (13, 14) 
        and status not in (5,6) 
        and linkname is null 
        and (oid$ is null or oid$ not in (select toid from type$ 
             where bitand(properties, 16) = 16)) 
/ 
commit 
/ 
alter system flush shared_pool
/

DECLARE
 obj_number number := 0;

 cursor C1 is select o.obj#, 
           'ALTER ' || decode (o.type#,
                               13, 'TYPE ',
                               14, 'TYPE ',
                               ' ') ||
            u.name || '.' || o.name || ' COMPILE ' ||
                       decode (o.type#,
                               13, 'SPECIFICATION',
                               14, 'BODY',
                               ' ')
           from obj$ o, user$ u
           where o.obj# > obj_number and 
           u.user# = o.owner# and o.remoteowner is NULL and
           o.status in (4,5,6) and o.type# in (13, 14)
           order by o.obj#;

  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  iterations number;
  loop_count number;
  my_err     number;
  validate   number;
BEGIN
 loop_count := 0;
 -- To make sure we eventually stop, pick a max number of iterations
 select count(*) into iterations from obj$ where remoteowner is NULL and
           status in (4,5,6) and type# in (13, 14); 

 DDL_CURSOR := dbms_sql.open_cursor;
 OPEN C1;

 LOOP

   BEGIN
     FETCH C1 INTO obj_number, ddl_statement;
     EXIT WHEN C1%NOTFOUND OR loop_count > iterations;
   EXCEPTION
    WHEN OTHERS THEN
      my_err := SQLCODE;
      IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
       CLOSE C1;
       -- Here is why C1 orders by obj#.  When we restart the query, we 
       -- will only find object with obj# greater than the last one tried.
       -- This keeps us from re-trying objects that failed.
       OPEN  C1;
       GOTO continue;
      ELSE
       RAISE;
      END IF;
   END;

   -- Check to see if already validated as a result of earlier compiles
   select count(*) into validate from obj$ where obj# = obj_number and
    status in (4,5,6);

   IF validate = 1 THEN
     BEGIN
       -- Issue the Alter Statement  (Parse implicitly executes DDLs)
       dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
     EXCEPTION
       WHEN OTHERS THEN
        null; -- ignore, and proceed.
     END;
   END IF;

 <<continue>>
   loop_count := loop_count + 1;
 END LOOP;
 dbms_sql.close_cursor(DDL_CURSOR);
 CLOSE C1;
END;
/

alter package DBMS_AQ compile
/
alter package DBMS_AQ compile body
/
alter package DBMS_AQADM compile
/
alter package DBMS_AQADM compile body
/
alter package DBMS_PRVTAQIS compile
/
alter package DBMS_PRVTAQIS compile body
/
alter package DBMS_PRVTAQIM compile
/
alter package DBMS_PRVTAQIM compile body
/

Rem =========================================================================
Rem NULL refresh related downgrade: Set snap_reftime$.refscn to 0
Rem =========================================================================
UPDATE sys.snap_reftime$ SET refscn = 0;
/
commit;
/
Rem =========================================================================
Rem Begin temporary tables related downgrade:
Rem First drop all temporary tables that may have been created for
Rem snapshot logs.
Rem Drop ATEMPTAB$ created in 8.1 and recreate it as permanent table 
Rem and change it to 8.0.3 temporary table
Rem We need to reset compatibility after dropping this table so 
Rem that startup on 8.0.x will work.  
Rem =========================================================================
---
--- Drop temporary updatable snapshot logs
---
DECLARE
  sql_cur  BINARY_INTEGER;
  dummy    BINARY_INTEGER;
  new_flag BINARY_INTEGER;

  CURSOR mv_logs IS
    SELECT '"'||mowner||'"."'||temp_log||'"' temp_log,
           flag, mowner, master
      FROM mlog$ m
     WHERE temp_log IS NOT NULL
    FOR UPDATE;
BEGIN
  sql_cur := dbms_sql.open_cursor;
  FOR alog IN mv_logs LOOP
    new_flag := alog.flag;
    IF dbms_ijob.bit(new_flag, 64) THEN ---KKZLOGTUPS
      new_flag := new_flag - 64;
    END IF;

    BEGIN
      dbms_sql.parse(sql_cur, 'DROP TABLE ' || alog.temp_log, dbms_sql.v7);
      dummy := dbms_sql.execute(sql_cur);

      UPDATE mlog$ m
         SET flag = new_flag, temp_log = NULL
       WHERE m.mowner = alog.mowner AND m.master = alog.master;
    EXCEPTION WHEN others THEN
      NULL; --- Ignore the error
    END;
  END LOOP;
  dbms_sql.close_cursor(sql_cur);
  COMMIT;
EXCEPTION WHEN others THEN
  IF dbms_sql.is_open(sql_cur) THEN
    dbms_sql.close_cursor(sql_cur);
  END IF;
  RAISE;
END;
/
drop table atemptab$
/
create table atemptab$
(
	id number
)
/
comment on table ATEMPTAB$ is
'Temporary table whose definition is used internally.  Never contains data'
/
create index atempind$ on atemptab$(id)
/             /* indexes backing up workspaces on disk claim to be atempind$ */

update obj$ set flags = flags + 2 where name = 'ATEMPTAB$' or name ='ATEMPIND$'
/
commit
/

update refcon$ 
set    reftyp = 3
where  reftyp = 1
/

REM remove column added to i_audit for proxy auditing.

drop index i_audit
/
create unique index i_audit on audit$(user#, option#)
/

REM remove 8.1 opaque types

drop type RowSet force;
drop type RowType force;

REM remove new views created in 8.1 in catalog.sql

drop view SESSION_CONTEXT;
drop public synonym SESSION_CONTEXT;

drop view ALL_CONTEXT;
drop public synonym ALL_CONTEXT;

drop view DBA_CONTEXT;
drop public synonym DBA_CONTEXT;

drop view ALL_SUMDELTA;
drop public synonym ALL_SUMDELTA;
 
drop view ALL_OPERATORS;
drop public synonym ALL_OPERATORS;

drop view USER_OPERATORS;
drop public synonym USER_OPERATORS;

drop view DBA_OPBINDINGS;
drop public synonym DBA_OPBINDINGS;

drop view USER_OPBINDINGS;
drop public synonym USER_OPBINDINGS;

drop view ALL_OPBINDINGS;
drop public synonym ALL_OPBINDINGS;

drop view DBA_OPANCILLARY;
drop public synonym DBA_OPANCILLARY;

drop view USER_OPANCILLARY;
drop public synonym USER_OPANCILLARY;

drop view ALL_OPANCILLARY;
drop public synonym ALL_OPANCILLARY;

drop view USER_INDEXTYPES;
drop public synonym USER_INDEXTYPES;

drop view ALL_INDEXTYPES;
drop public synonym ALL_INDEXTYPES;

drop view DBA_INDEXTYPE_OPERATORS;
drop public synonym DBA_INDEXTYPE_OPERATORS;

drop view USER_INDEXTYPE_OPERATORS;
drop public synonym USER_INDEXTYPE_OPERATORS;

drop view ALL_INDEXTYPE_OPERATORS;
drop public synonym ALL_INDEXTYPE_OPERATORS;

drop view USER_UNUSED_COL_TABS;
drop public synonym USER_UNUSED_COL_TABS;

drop view ALL_UNUSED_COL_TABS;
drop public synonym ALL_UNUSED_COL_TABS;

drop view USER_PARTIAL_DROP_TABS;
drop public synonym USER_PARTIAL_DROP_TABS;

drop view ALL_PARTIAL_DROP_TABS;
drop public synonym ALL_PARTIAL_DROP_TABS;

drop view USER_ASSOCIATIONS;
drop public synonym USER_ASSOCIATIONS;

drop view ALL_ASSOCIATIONS;
drop public synonym ALL_ASSOCIATIONS;

drop view DBA_USTATS;
drop public synonym DBA_USTATS;

drop view USER_USTATS;
drop public synonym USER_USTATS;

drop view ALL_USTATS;
drop public synonym ALL_USTATS;

drop view EXU81FIL;

REM
REM Remove outline information
REM
delete from expact$ where owner='OUTLN'
/
commit
/


--- =====================================================================
--- BEGIN Downgrade for V8.1.3 Snapshots/Materialized Views
--- =====================================================================
drop table snap_logdep$
/
delete snap_loadertime$
/
delete snap_site$
/
drop sequence snapsite_id$
/
drop table system.def$_pushed_transactions
/
---
--- Remove all RepAPI snapshots and meta-data
---
DELETE FROM rgchild$      WHERE instsite != 0;
DELETE FROM rgroup$       WHERE instsite != 0;
DELETE FROM snap_reftime$ WHERE instsite != 0;
DELETE FROM snap_refop$   WHERE instsite != 0;
DELETE FROM snap_colmap$  WHERE instsite != 0;
DELETE FROM snap_colmap$  WHERE instsite != 0;
DELETE FROM snap$         WHERE instsite != 0;
COMMIT
/
---
--- Remove all Summary Management meta-data
---
DECLARE
  sql_cur BINARY_INTEGER;
  dummy   BINARY_INTEGER;

  --- Find all local snapshots that have an associated SM object
  CURSOR mv_snaps IS
    SELECT '"'||owner||'"."'||name||'"' mv_name FROM dba_snapshots sn
     WHERE master_link IS NULL
       AND EXISTS (SELECT 1 FROM dba_summaries sm
                    WHERE sm.container_owner = sn.owner
                      AND sm.container_name  = sn.name);
BEGIN
  sql_cur := dbms_sql.open_cursor;
  FOR mv IN mv_snaps LOOP
    BEGIN
      dbms_sql.parse(sql_cur,
        'ALTER SNAPSHOT ' || mv.mv_name || ' DISABLE QUERY REWRITE', 
        dbms_sql.v7);
      dummy := dbms_sql.execute(sql_cur);
    EXCEPTION WHEN others THEN
      NULL; --- Ignore the error
    END;
  END LOOP;
  dbms_sql.close_cursor(sql_cur);
EXCEPTION WHEN others THEN
  IF dbms_sql.is_open(sql_cur) THEN
    dbms_sql.close_cursor(sql_cur);
  END IF;
  RAISE;
END;
/
---
--- Drop materialized views that can't be downgraded
---
DECLARE
  sql_cur BINARY_INTEGER;
  dummy   BINARY_INTEGER;

  --- Find all snapshots without a view or that use a prebuild table
  CURSOR mv_snaps IS
    SELECT '"'||sowner||'"."'||vname||'"' mv_name FROM snap$ sn
     WHERE vname = tname
        OR bitand(flag, 131072) != 0
        OR auto_fast = 'N';
BEGIN
  sql_cur := dbms_sql.open_cursor;
  FOR mv IN mv_snaps LOOP
    BEGIN
      dbms_sql.parse(sql_cur, 'DROP SNAPSHOT ' || mv.mv_name, dbms_sql.v7);
      dummy := dbms_sql.execute(sql_cur);
    EXCEPTION WHEN others THEN
      NULL; --- Ignore the error
    END;
  END LOOP;
  dbms_sql.close_cursor(sql_cur);
EXCEPTION WHEN others THEN
  IF dbms_sql.is_open(sql_cur) THEN
    dbms_sql.close_cursor(sql_cur);
  END IF;
  RAISE;
END;
/
---
--- Force BUILD DEFERRED materialized views to complete refresh
---
UPDATE snap_reftime$ r SET snaptime = TO_DATE('01-JAN-1950', 'DD-MON-YYYY')
 WHERE (r.sowner, r.vname) IN
       (SELECT s.sowner, s.vname FROM snap$ s
         WHERE bitand(s.flag, 524288) != 0)
/
COMMIT
/
---
--- Convert new V8.1.3 materialized views to complex snapshots
---
DECLARE
  new_flag NUMBER;
  -- Find all new V8.1.3 materialized views: 
  -- AGG, JVS, AV1
  CURSOR mv_snaps IS
    SELECT sowner, vname, flag FROM snap$ sn
    WHERE  bitand(flag,   4096   -- AGG
                        + 8192   -- JVS
                        + 16384  -- AV1
                                        ) != 0
    FOR UPDATE;

BEGIN
  FOR mv IN mv_snaps LOOP
    new_flag := mv.flag;

    IF dbms_ijob.bit(new_flag, 1) THEN      -- KKZFCUL
      new_flag := new_flag - 1;
    END IF;

    IF dbms_ijob.bit(new_flag, 16) THEN     -- KKZFURI
      new_flag := new_flag - 16;
    END IF;

    IF dbms_ijob.bit(new_flag, 32) THEN     -- KKZFUPK
      new_flag := new_flag - 32;
    END IF;

    IF dbms_ijob.bit(new_flag, 128) THEN    -- KKZFUSQ
      new_flag := new_flag - 128;
    END IF;

    IF dbms_ijob.bit(new_flag, 4096) THEN   -- KKZFAGG
      new_flag := new_flag - 4096;
    END IF;

    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

    -- sys.snap$:
    --   1. update flag
    --   2. set tables => 1
    --   3. make sure auto_fast is not 'F'
    --   4. set status = 0 (force regeneration of refresh operations)
    UPDATE sys.snap$ s SET s.flag = new_flag, s.tables = 1, 
           s.auto_fast = decode(s.auto_fast, 'F', 'C', s.auto_fast),
           s.status = 0
    WHERE  s.sowner = mv.sowner AND s.vname = mv.vname;

    -- sys.snap_reftime$:
    --   delete all rows except for 0-level table
    DELETE from sys.snap_reftime$ r
    WHERE  r.sowner = mv.sowner AND r.vname = mv.vname
    AND    r.tablenum > 0;

    -- sys.snap_colmap$:
    --   delete all column mapping information
    DELETE from sys.snap_colmap$ c
    WHERE  c.sowner = mv.sowner AND c.vname = mv.vname;

  END LOOP;
  COMMIT;
END;
/
---
--- Ensure that flag will fit into a UB2
---
DECLARE
  new_flag NUMBER;
  --- Find all new V8.1.3 materialized views
  CURSOR mv_snaps IS
    SELECT sowner, vname, flag FROM snap$ sn
    FOR UPDATE;
BEGIN
  FOR mv IN mv_snaps LOOP
    new_flag := mv.flag;
    IF dbms_ijob.bit(new_flag, 32768) THEN ---KKZFCMT
      new_flag := new_flag - 32768;
    END IF;
    IF dbms_ijob.bit(new_flag, 65536) THEN ---KKZFHID
      new_flag := new_flag - 65536;
    END IF;
    IF dbms_ijob.bit(new_flag, 524288) THEN ---KKZFDEFR
      new_flag := new_flag - 524288;
    END IF;
    IF dbms_ijob.bit(new_flag, 1048576) THEN ---KKZFEQR
      new_flag := new_flag - 1048576;
    END IF;
    IF dbms_ijob.bit(new_flag, 2097152) THEN ---KKZFDQR
      new_flag := new_flag - 2097152;
    END IF;

    UPDATE snap$ s SET s.flag = new_flag
     WHERE s.sowner = mv.sowner AND s.vname = mv.vname;
  END LOOP;
  COMMIT;
END;
/
---
--- Drop rowid snapshot logs with filter columns
---
DECLARE
  sql_cur BINARY_INTEGER;
  dummy   BINARY_INTEGER;

  CURSOR mv_logs IS
    SELECT '"'||mowner||'"."'||master||'"' mastab
      FROM mlog$ m
     WHERE bitand(flag, 5) = 5 AND bitand(flag, 2) = 0
    FOR UPDATE;
BEGIN
  sql_cur := dbms_sql.open_cursor;
  FOR alog IN mv_logs LOOP
    BEGIN
      dbms_sql.parse(sql_cur, 'DROP SNAPSHOT LOG ON' || alog.mastab, 
                     dbms_sql.v7);
      dummy := dbms_sql.execute(sql_cur);
    EXCEPTION WHEN others THEN
      NULL; --- Ignore the error
    END;
  END LOOP;
  dbms_sql.close_cursor(sql_cur);
  COMMIT;
EXCEPTION WHEN others THEN
  IF dbms_sql.is_open(sql_cur) THEN
    dbms_sql.close_cursor(sql_cur);
  END IF;
  RAISE;
END;
/
---
--- Drop all generated queries
---
update snap$ set status=0;
delete from snap_refop$;
/
COMMIT
/
--- =====================================================================
--- END Downgrade for V8.1.3 Snapshots/Materialized Views
--- =====================================================================

-------------------------------------------------------------------------
--- Revoke new AQ system privilege created in 8.1
-------------------------------------------------------------------------
BEGIN
  dbms_aqadm.revoke_system_privilege(privilege => 'MANAGE_ANY', 
  	grantee => 'AQ_ADMINISTRATOR_ROLE');
END;
/
BEGIN
  dbms_aqadm.revoke_system_privilege(privilege => 'ENQUEUE_ANY',
	grantee => 'AQ_ADMINISTRATOR_ROLE');
END;
/
BEGIN
  dbms_aqadm.revoke_system_privilege(privilege => 'DEQUEUE_ANY',
	grantee => 'AQ_ADMINISTRATOR_ROLE');
END;
/

REM lrg34212
drop package dbms_utility
/

Rem =========================================================================
Rem downgrade for AQ propagation scheduling starts here
Rem =========================================================================
Rem for every schedule delete the existing propagation job 
DECLARE
  CURSOR prop_job is 
    SELECT DISTINCT jobno 
      FROM sys.aq$_schedules 
      WHERE jobno IS NOT NULL;
BEGIN
  FOR this_job IN prop_job LOOP
    UPDATE sys.aq$_schedules 
      SET jobno = NULL
      WHERE jobno = this_job.jobno;
    dbms_job.remove(this_job.jobno);
  END LOOP;
END;
/
Rem copy every schedule entry from sys.aq$_schedules to system.aq$_schedules;
INSERT into system.aq$_schedules 
       (oid, destination, start_time, duration, next_time, latency, last_time)
SELECT oid, destination, start_time, duration, next_time, latency, last_run
  FROM sys.aq$_schedules;
/
Rem create 805 style propagation job for each schedule
Rem and update jobno in system.aq$_schedules;
DECLARE
  newjob               integer;
  interval             varchar2(128);
  what                 varchar2(2048);
  window_start         varchar2(30);
  window_size          varchar2(30);
  qdata_type           number;
  objno                number;
  toid                 raw(16);
  toid_char            varchar2(32);
  version              number;
  version_char         varchar2(8);
  qname                varchar2(30);
  qschema              varchar2(30);
  sched_latency        number;
  AQADM$OBJECT_TYPE    CONSTANT BINARY_INTEGER := 1;
  CURSOR prop_sched is 
    SELECT *
    FROM system.aq$_schedules 
    WHERE jobno IS NULL
    FOR UPDATE;
BEGIN
  FOR this_sched IN prop_sched LOOP
    window_size := NVL(this_sched.duration, 0);
    window_start := TO_CHAR(NVL(this_sched.start_time, SYSDATE), 
                                'MM-DD-YYYY HH24:MI:SS');
    sched_latency := this_sched.latency;
    IF this_sched.latency is null then
      interval := 'SYSDATE';
    else
      interval := 'SYSDATE + ' || to_char(sched_latency) || '/86400';
    END IF;
    SELECT t.udata_type, q.name, t.schema, t.objno
      INTO qdata_type, qname, qschema, objno
      FROM system.aq$_queues q, system.aq$_queue_tables t
      WHERE q.oid = this_sched.oid and q.table_objno = t.objno;
    IF qdata_type = AQADM$OBJECT_TYPE THEN
      SELECT ct.toid, ct.version# into toid, version
        FROM sys.col$ c, sys.coltype$ ct
        WHERE c.col# = ct.col# and
              c.obj# = ct.obj# and
              c.name = 'USER_DATA' and
              c.obj# = objno;
      toid_char := RAWTOHEX(toid);
      version_char := TO_CHAR(version);
    END IF;
    -- create what string for propagation job
    what := 'next_date := sys.dbms_aqadm.aq$_propaq(job, next_date, 
            qname=>''' || qname || '''';
    what := what || ', schema=>''' || qschema || '''';
    what := what || ', start_time =>''' || window_start || '''';
    IF this_sched.destination IS NOT NULL THEN 
      what := what || ', destination=>''' || this_sched.destination || '''';
    END IF;
    IF qdata_type = AQADM$OBJECT_TYPE THEN
      what := what || ', toid_char=>''' || toid_char || '''';
      what := what || ', version_char=>''' || version_char || '''';
    END IF;
    IF this_sched.duration is NOT NULL THEN
      what := what || ', duration=>'''|| window_size || '''';
    END IF;
    IF this_sched.next_time is NOT NULL THEN
      what := what || ', next_time=>'''|| this_sched.next_time || '''';
    END IF;
    IF this_sched.latency IS NULL THEN
      what := what || ', latency=>'''|| 0 || '''';
    ELSE
      what := what || ', latency=>'''|| this_sched.latency || '''';
    END IF;
    what := what || ');';
    -- now submit job
    dbms_job.submit(newjob, what, this_sched.start_time, interval, TRUE);
    -- update job number in schedule table
    UPDATE system.aq$_schedules 
      SET jobno = newjob
      WHERE oid = this_sched.oid;
  END LOOP;
END;
/
COMMIT;
/
Rem drop the 8.1 style schedules table
DELETE FROM sys.aq$_schedules;
/
COMMIT;
/
Rem =========================================================================
Rem downgrade for AQ propagation scheduling ends here
Rem =========================================================================

-------------------------------------------------------------------------
--- Eliminate AQ entries in obj$, oid$, and objauth$
--- The following four statements should run as a transaction
-------------------------------------------------------------------------
REM hold this aq info around in case for some reason the 
REM compatibility was not reset to 8.0 before the downgrade 
REM happened
drop table temp_objauth$_for_aq_downgrade
/
drop table temp_oid$_for_aq_downgrade
/
drop table temp_obj$_for_aq_downgrade
/
create table temp_objauth$_for_aq_downgrade as select * from
	objauth$ where obj# IN (SELECT obj# FROM obj$ WHERE type#=24)
/ 
create table temp_oid$_for_aq_downgrade as select * from
	oid$ WHERE obj# IN (SELECT obj# FROM obj$ WHERE type#=24)
/
create table temp_obj$_for_aq_downgrade as select * from 
	obj$ WHERE type#=24
/

DELETE FROM objauth$ WHERE obj# IN
 (SELECT obj# FROM obj$ WHERE type#=24)
/ 
DELETE FROM oid$ WHERE obj# IN
 (SELECT obj# FROM obj$ WHERE type#=24)
/
DELETE FROM obj$ WHERE type#=24
/
COMMIT
/
UPDATE system.aq$_queues SET eventid = system.aq$_qidseq.NEXTVAL
/
COMMIT
/
DROP TABLE system.aq$_queue_upgrade_tmp
/

-------------------------------------------------------------------------
--- Drop AQ dictionary tables and views  that were created in 8.1.3
-------------------------------------------------------------------------
DROP VIEW all_queues
/
DROP PUBLIC SYNONYM all_queues
/
DROP VIEW queue_privileges
/
DROP PUBLIC SYNONYM queue_privileges
/
DELETE FROM sys.aq$_queue_table_affinities
/

-------------------------------------------------------------------------
--- eliminate flavor information from replication catalog
-------------------------------------------------------------------------
DROP TABLE system.repcat$_flavor_objects
/
DROP TABLE system.repcat$_flavors
/
UPDATE system.repcat$_repcat SET flavor_id = 0
/
UPDATE system.repcat$_repcat SET flag = '00000000'
/
UPDATE system.repcat$_snapgroup SET flavor_id = 0
/
UPDATE system.repcat$_repcolumn
SET
  ctype = NULL, length = NULL, precision# = NULL, scale = NULL,
  null$ = NULL, charsetid = NULL, charsetform = NULL,
  flag = utl_raw.bit_and(flag, '0300')
/
UPDATE system.repcat$_repobject
SET
  flag = utl_raw.bit_and(flag, '07000000')
/
DELETE system.repcat$_resolution
WHERE method_name = 'USER FLAVOR FUNCTION'
/
COMMIT
/


UPDATE system.repcat$_repobject o set o.status = 4
  WHERE o.type IN (9, 11)
    AND exists (SELECT 1 FROM system.repcat$_generated g
      WHERE g.sname = o.sname
        AND g.oname = o.oname
        AND g.type = o.type
        AND g.reason IN (1, 9)
        AND g.base_type = 2)
/
COMMIT
/

Rem Remove the summary and dimension views created for 8.1
@@catsumno



Rem =========================================================================
Rem drop 8.1.3 packages which need to reload again after downgrade
Rem =========================================================================

DROP package dbms_stats
/

DROP package dbms_rcvman
/

Rem =========================================================================
Rem Heterogeneous Services fixed views:
Rem V$HS_AGENT, V$HS_SESSION, GV$HS_AGENT, GV$HS_SESSION 
Rem =========================================================================

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

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

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

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

Rem =========================================================================
Rem v$ stuff
Rem =========================================================================
drop public synonym v$dlm_all_locks;
drop view v_$dlm_all_locks;

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

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

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

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

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

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

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

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

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

drop public synonym gv$rsrc_consumer_group_cpu_mth;
drop view gv_$rsrc_consume_group_cpu_mth;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

drop public synonym V$TEMP_PING;
drop view V_$TEMP_PING;

drop public synonym GV$TEMP_PING;
drop view GV_$TEMP_PING;

drop public synonym V$TARGETRBA;
drop view V_$TARGETRBA;

drop public synonym GV$TARGETRBA;
drop view GV_$TARGETRBA;

drop public synonym V$INSTANCE_RECOVERY;
drop view V_$INSTANCE_RECOVERY;

drop public synonym GV$INSTANCE_RECOVERY;
drop view GV_$INSTANCE_RECOVERY;

drop public synonym V$TEMPSTAT;
drop view V_$TEMPSTAT;

drop public synonym GV$TEMPSTAT;
drop view GV_$TEMPSTAT;

drop public synonym V$PROXY_DATAFILE;
drop view V_$PROXY_DATAFILE;

drop public synonym GV$PROXY_DATAFILE;
drop view GV_$PROXY_DATAFILE;

drop public synonym V$PROXY_ARCHIVEDLOG;
drop view V_$PROXY_ARCHIVEDLOG;

drop public synonym GV$PROXY_ARCHIVEDLOG;
drop view GV_$PROXY_ARCHIVEDLOG;

drop public synonym V$TEMPFILE;
drop view V_$TEMPFILE;

drop public synonym GV$TEMPFILE;
drop view GV_$TEMPFILE;

drop public synonym V$ARCHIVE_PROCESSES;
drop view V_$ARCHIVE_PROCESSES;

drop public synonym GV$ARCHIVE_PROCESSES;
drop view GV_$ARCHIVE_PROCESSES;

drop public synonym V$BACKUP_SYNC_IO;
drop view V_$BACKUP_SYNC_IO;

drop public synonym GV$BACKUP_SYNC_IO;
drop view GV_$BACKUP_SYNC_IO;

drop public synonym V$BACKUP_ASYNC_IO;
drop view V_$BACKUP_ASYNC_IO;

drop public synonym GV$BACKUP_ASYNC_IO;
drop view GV_$BACKUP_ASYNC_IO;

drop public synonym V$TEMPORARY_LOBS;
drop view V_$TEMPORARY_LOBS;

drop public synonym GV$TEMPORARY_LOBS;
drop view GV_$TEMPORARY_LOBS;

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

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

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

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

drop public synonym V$LOGMNR_CONTENTS;
drop view V_$LOGMNR_CONTENTS;

drop public synonym GV$LOGMNR_CONTENTS;
drop view GV_$LOGMNR_CONTENTS;

drop public synonym V$LOGMNR_PARAMETERS;
drop view V_$LOGMNR_PARAMETERS;

drop public synonym GV$LOGMNR_PARAMETERS;
drop view GV_$LOGMNR_PARAMETERS;

drop public synonym V$LOGMNR_DICTIONARY;
drop view V_$LOGMNR_DICTIONARY;

drop public synonym GV$LOGMNR_DICTIONARY;
drop view GV_$LOGMNR_DICTIONARY;

drop public synonym V$LOGMNR_LOGS;
drop view V_$LOGMNR_LOGS;

drop public synonym GV$LOGMNR_LOGS;
drop view GV_$LOGMNR_LOGS;

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

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

Rem remove space management views and v$
@@catspacd


REM
REM revoke all 8.1.3 privileges
REM

REM we need to do this because the QUEUE privileges need
REM to be revoked a certain way.  revoke_session_privileges won't
REM handle this.  Also, we need to remove the query rewrite privileges.
REM this is a special case because in 8.1.5 the name of the 
REM privilege changed.  Since we need to make the 8.1.5 downgrade
REM change before we run revoke_session_privileges to remove
REM query rewrite, the privilege name can't be found.  Thus,
REM we do it here.
DECLARE
 cursor C1 is select u.name, s.privilege# 
           from sysauth$ s, user$ u, SYSTEM_PRIVILEGE_MAP smp
           where smp.name in	
		('MANAGE ANY QUEUE', 'ENQUEUE ANY QUEUE', 'DEQUEUE ANY QUEUE',
		 'ADMINISTER RESOURCE MANAGER')
	        and smp.privilege = s.privilege# and s.grantee# = u.user#
	union 
	   select u.name, s.privilege# from sysauth$ s, user$ u
	   where s.grantee# = u.user# and s.privilege# in (-210, -211);
	   
  DDL_CURSOR integer;
  user       varchar(200);
  priv       number;
BEGIN
 DDL_CURSOR := dbms_sql.open_cursor;
 OPEN C1;

 LOOP

   FETCH C1 INTO user, priv;
   EXIT WHEN C1%NOTFOUND;

   BEGIN
     if priv = -227 then
	dbms_resource_manager_privs.revoke_system_privilege(revokee_name =>
		 user, privilege_name => 'ADMINISTER_RESOURCE_MANAGER');
     elsif priv = -210 then
	dbms_sql.parse(DDL_CURSOR, 'revoke QUERY REWRITE from '|| user,
		 dbms_sql.native);
     elsif priv = -211 then
	dbms_sql.parse(DDL_CURSOR, 'revoke GLOBAL QUERY REWRITE from '|| user,
		 dbms_sql.native);
     elsif priv = -218 then
	dbms_aqadm.revoke_system_privilege(privilege => 'MANAGE_ANY',	
		grantee=>user);
     elsif priv = -219 then
     	dbms_aqadm.revoke_system_privilege(privilege => 'ENQUEUE_ANY', 
		grantee=>user);
     else  -- priv=-220
	dbms_aqadm.revoke_system_privilege(privilege => 'DEQUEUE_ANY', 
		grantee=>user);
     end if;

   END;

 END LOOP;
 dbms_sql.close_cursor(DDL_CURSOR);
 CLOSE C1;
END;
/

REM revoke_versions_privilege was created in d0801030.sql
execute revoke_versions_privilege('CREATE OPERATOR');
execute revoke_versions_privilege('CREATE ANY OPERATOR');
execute revoke_versions_privilege('DROP ANY OPERATOR');
execute revoke_versions_privilege('CREATE INDEXTYPE');
execute revoke_versions_privilege('CREATE ANY INDEXTYPE');
execute revoke_versions_privilege('ALTER ANY INDEXTYPE');
execute revoke_versions_privilege('DROP ANY INDEXTYPE');
execute revoke_versions_privilege('CREATE DIMENSION');
execute revoke_versions_privilege('CREATE ANY DIMENSION');
execute revoke_versions_privilege('ALTER ANY DIMENSION');
execute revoke_versions_privilege('DROP ANY DIMENSION');
execute revoke_versions_privilege('CREATE ANY CONTEXT');
execute revoke_versions_privilege('DROP ANY CONTEXT');
execute revoke_versions_privilege('CREATE ANY OUTLINE');
execute revoke_versions_privilege('ALTER ANY OUTLINE');
execute revoke_versions_privilege('DROP ANY OUTLINE');


REM  remove these two ALTER OPERATOR, EXECUTE OPERATOR, 

delete from SYSTEM_PRIVILEGE_MAP where 
  PRIVILEGE=-186 or (PRIVILEGE <= -200 and PRIVILEGE >= -227)
/
delete from STMT_AUDIT_OPTION_MAP where
  OPTION# in (34, 39) or (OPTION# >= 210 and OPTION# <= 226)
/
delete from TABLE_PRIVILEGE_MAP where
  PRIVILEGE in (20,21)
/

Rem remove DB Resource Manager info.
@@catrmno

Rem =========================================================================
Rem Bitmap invalidation for indexes protected by the stored records per block
Rem =========================================================================
Rem mark indexes invalid and reset tab$ spare1 for all protected tables.
alter system flush shared_pool;

update sys.ind$ set flags=(2 *(floor(flags/2)))+1 where bo# in 
(select i.bo# from sys.ind$ i, sys.tab$ t where t.spare1 > 32767 
   AND t.obj# = i.bo# AND i.type#=2);

Rem mark all related index partitions invalid
update sys.indpart$ set flags=(2 *(floor(flags/2)))+1 where bo# in 
(select p.bo# from sys.ind$ i, sys.tab$ t, sys.indpart$ p 
where t.spare1 > 32767 AND t.obj# = i.bo# AND i.type#=2 and p.bo# = i.obj#);

Rem reset related spare1 values in tab$ 
update sys.tab$ set spare1 = 0 where obj# in 
(select t.obj# from sys.tab$ t where t.spare1 > 32767);

commit;

Rem delete triggers on database events or call/JAVA style triggers
REM don't get rid of this info because it should be gone if 
REM all incompatible objects were dropped properly
REM delete trigger$ where bitand(property,26) > 0;

commit;

REM we need to remove a column from type$ because it causes
REM a recursive sql error in 8.0.5 or lower if there is an extra
REM column
create table temp_type$ as select toid, version#, version, tvoid,
  typecode, properties, attributes, methods, supertypes, subtypes,
  spare1, spare2, spare3 from type$
/
drop table type$
/
rename temp_type$ to type$
/
create unique index i_type1 on type$(toid, version)
/
create unique index i_type2 on type$(tvoid)
/

Rem =========================================================================
Rem Update flags for all varray and ADT columns that are not virtual columns.
Rem =========================================================================

update coltype$ ct
set ct.flags = ct.flags - 128
where exists
   (select c.intcol# from col$ c
    where c.obj# = ct.obj# and c.intcol# = ct.intcol# and
       (c.type# = 123 or /* Varray column */
        (c.type# = 121 and bitand(c.property, 8) != 8)))
   and bitand(ct.flags,128)=128;


Rem =========================================================================
Rem Update the flags for nested table columns that will ultimately involve
Rem using the old image format.
Rem =========================================================================

update coltype$ ct
set ct.flags = ct.flags - 128
where bitand(ct.flags, 4) = 4 and
   exists
   (select *
    from   ntab$ n, coltype$ ct2
    where  n.ntab# = ct2.obj# and
           n.obj# = ct.obj# and
           n.intcol# = ct.intcol# and
           bitand(ct2.flags, 128) = 128)
   and bitand(ct.flags,128)=128;

alter table tabpart$ modify (
  ts#         number NOT NULL,   
  file#       number NOT NULL,   
  block#      number NOT NULL)
/
alter table partobj$ modify (
  defts#      number NOT NULL)
/

commit
/

delete from javasnm$
/

Rem
Rem Drop 8.1 tables for export/import support of procedural objects & actions
Rem
delete from expdepobj$
/
delete from expdepact$
/
delete from exppkgobj$
/
delete from exppkgact$
/
commit
/

Rem =========================================================================
Rem END STAGE 4: downgrade from 8.1.3 to 8.0.5
Rem =========================================================================

Rem *************************************************************************
Rem END e0800050.sql
Rem *************************************************************************


