Rem
Rem $Header: d0801040.sql 16-nov-98.17:19:56 jiyang Exp $
Rem
Rem d0801040.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      d0801040.sql -  downgrade Oracle RDBMS from current release to 8.1.4
Rem
Rem    DESCRIPTION
Rem
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.4 release.  This
Rem      script provides a direct downgrade path from your current installed
Rem      release to the 8.1.4 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                 this stage is a no-op for 8.1.5 since the current release
Rem                 is 8.1.5; for the next release:
Rem                 call d0801050.sql to do this.
Rem        STAGE 2: downgrade from 8.1.5 to 8.1.4
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    rherwadk    02/03/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 =========================================================================

Rem For the next release, we'll need to create a file called d0801060.sql
Rem and insert the following line here.  We'll also need to MOVE the
Rem compatibility check below to the new script:
Rem
Rem @@d0801050.sql

REM
REM COMPATIBILITY CHECK
REM Always make sure this is the first thing to be executed during 
REM downgrade as a safety check 
REM 
whenever sqlerror exit;
set serveroutput on

DECLARE
  reset_compatible_parameter EXCEPTION;
  compat varchar2(20);
BEGIN
  select value into compat from v$parameter where name='compatible';

  IF compat > '8.1' THEN
    RAISE reset_compatible_parameter;
  END IF;

  EXCEPTION
    WHEN reset_compatible_parameter THEN
	dbms_output.put_line('RESET YOUR COMPATIBILITY LEVEL');
	RAISE;
END;
/

whenever sqlerror continue;

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 END d0801040.sql
Rem *************************************************************************
