Rem
Rem $Header: a0801040.sql 05-oct-98.13:04:13 rshaikh Exp $
Rem
Rem a0801040.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      a0801040.sql - additional ANONYMOUS BLOCK dictionary upgrade.
Rem      		Upgrade Oracle RDBMS from 8.1.4 to the new release
Rem
Rem
Rem    DESCRIPTION
Rem
Rem      Additional upgrade script to be run during the migration of a
Rem      8.1.4 database to the new release.
Rem
Rem	 This script is called from u0801040.sql and a0801030.sql
Rem
Rem	 Put any anonymous block related changes here.
Rem	 Any dictionary create, alter, updates and deletes  
Rem 	 that must be performed before catalog.sql and catproc.sql go 
Rem	 in c0801040.sql
Rem
Rem      The upgrade is performed in the following stages:
Rem        STAGE 1: additional steps to upgrade from 8.1.4 to 8.1.5
Rem        STAGE 2: upgrade from 8.1.4 to the new release
Rem
Rem    RETURNS
Rem 
Rem    NOTES
Rem      You must connect internal to run this script
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    liwong      12/07/98 - Support multiple grps at same snap site          
Rem    rshaikh     10/05/98 - create
Rem    rshaikh     10/05/98 - Created
Rem

Rem =========================================================================
Rem BEGIN STAGE 1: upgrade from 8.1.4 to 8.1.5
Rem =========================================================================

REM INSERT YOUR UPGRADE STATEMENTS HERE

Rem =========================================================================
Rem END STAGE 1: upgrade from 8.1.4 to 8.1.5
Rem =========================================================================

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcat ADD(gowner VARCHAR2(30) DEFAULT 'PUBLIC')
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_repcat SET gowner = 'PUBLIC'
/
COMMIT
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcat DROP CONSTRAINT repcat$_repcat_primary
  CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repcat ADD CONSTRAINT repcat$_repcat_primary
  PRIMARY KEY(sname, gowner)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavors ADD(gowner VARCHAR2(30) DEFAULT 'PUBLIC')
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_flavors SET gowner = 'PUBLIC'
/
COMMIT
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavors DROP CONSTRAINT repcat$_flavors_unq1
  CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavors ADD CONSTRAINT repcat$_flavors_unq1
                       UNIQUE (gname, flavor_id, gowner)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavors ADD  CONSTRAINT repcat$_flavors_fk1
                       FOREIGN KEY (gname, gowner)
                       REFERENCES system.repcat$_repcat(sname, gowner)
                       ON DELETE CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
DROP INDEX system.repcat$_flavors_gname
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_snapgroup ADD(gowner VARCHAR2(30) DEFAULT 'PUBLIC')
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_snapgroup SET gowner = 'PUBLIC'
/
COMMIT
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
DROP INDEX system.i_repcat$_snapgroup1
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repobject ADD(gowner VARCHAR2(30))
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_repobject o SET o.gowner = NULL,
                                      o.gname = NULL
  WHERE (o.sname, o.oname, o.type) IN
    (SELECT DISTINCT g.sname, g.oname, g.type
        FROM system.repcat$_generated g
          WHERE g.reason IN (1, 9)
            AND g.type IN (9,11)
            AND g.base_type = -1)
   OR o.type = -4
/

REM Update all other rows
UPDATE system.repcat$_repobject SET gowner = 'PUBLIC'
  WHERE gname IS NOT NULL
/
COMMIT
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repobject ADD CONSTRAINT repcat$_repobject_prnt
                      FOREIGN KEY(gname, gowner)
                      REFERENCES system.repcat$_repcat(sname, gowner)
                      ON DELETE CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
DROP INDEX system.repcat$_repobject_gname
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repgroup_privs ADD(gowner VARCHAR2(30))
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repgroup_privs
  ADD CONSTRAINT repcat$_repgroup_privs_fk  
    FOREIGN KEY (gname, gowner)
      REFERENCES system.repcat$_repcat(sname, gowner)
        ON DELETE CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repgroup_privs DROP CONSTRAINT
  repcat$_repgroup_privs_uk
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repgroup_privs ADD CONSTRAINT
  repcat$_repgroup_privs_uk UNIQUE (userid, gname, gowner)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repschema ADD(gowner VARCHAR2(30) DEFAULT 'PUBLIC')
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_repschema SET gowner = 'PUBLIC'
/
COMMIT
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repschema  ADD CONSTRAINT repcat$_repschema_prnt 
  FOREIGN KEY(sname, gowner)
    REFERENCES system.repcat$_repcat(sname, gowner)
      ON DELETE CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repschema DROP CONSTRAINT
  repcat$_repschema_primary
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repschema ADD CONSTRAINT repcat$_repschema_primary
                      PRIMARY KEY(sname, dblink, gowner)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavor_objects ADD
  (gowner VARCHAR2(30) DEFAULT 'PUBLIC')
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
UPDATE system.repcat$_flavor_objects SET gowner = 'PUBLIC'
/
COMMIT
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavor_objects DROP CONSTRAINT
  repcat$_flavor_objects_pk
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavor_objects ADD 
  CONSTRAINT repcat$_flavor_objects_pk
     PRIMARY KEY (sname, oname, type, gname, flavor_id, gowner)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavor_objects ADD CONSTRAINT
   repcat$_flavor_objects_fk1
     FOREIGN KEY (gname, gowner)
       REFERENCES system.repcat$_repcat(sname, gowner)
         ON DELETE CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_flavor_objects ADD CONSTRAINT
  repcat$_flavor_objects_fk2
    FOREIGN KEY (gname, flavor_id, gowner)
      REFERENCES system.repcat$_flavors(gname, flavor_id, gowner)
        ON DELETE CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
DROP INDEX system.repcat$_flavor_objects_fg
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_generated DROP CONSTRAINT repcat$_repgen_primary
  CASCADE
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_generated ADD CONSTRAINT repcat$_repgen_primary
  PRIMARY KEY(sname, oname, type, base_sname, base_oname, base_type)
/

Rem =========================================================================
Rem BEGIN STAGE 2: upgrade from 8.1.4 to the new release
Rem =========================================================================
Rem

Rem END HERE FOR RELEASE 8.1.4
Rem For the next release we have to add a file here called
Rem @@a0801050

Rem =========================================================================
Rem END STAGE 2: upgrade from 8.1.4 to the new release
Rem =========================================================================

Rem *************************************************************************
Rem END a0801040.sql
Rem *************************************************************************
