rem 
rem $Header: cat7301.sql,v 1.7 1995/05/31 16:46:01 msimon Exp $ 
rem 
Rem  Copyright (c) 1992 by Oracle Corporation 
Rem    NAME
Rem      cat7301.sql - upgrade Oracle RDBMS from version 7.2.X to 7.3
Rem    DESCRIPTION
Rem
Rem    RETURNS
Rem 
Rem    NOTES
Rem      Run this as INTERNAL.  Run it before installing catalog or pl/sql.
Rem      It is preferable to run it on 7.2.
Rem    MODIFIED   (MM/DD/YY)
Rem     msimon     05/31/95 -  Bug 282581 Make 7.3 sequences compatible with 7.2
Rem     rmarimon   04/06/95 -  Bug 110894: add column to dependency$ for EXPORT
Rem     hasun      03/22/95 -  Upgrade for Sync Replication
Rem     hasun      03/08/95 -  Fix merge conflicts
Rem     hasun      03/01/95 -  Add upgrade for Rep3: Object Groups
Rem     mramache   03/06/95 -  stored triggers
Rem     glumpkin   11/27/94 -  Creation
 
rem
rem  Histograms
rem
create cluster c_obj#_col#
( obj#            number,                                   /* object number */
  col#            number)                                   /* column number */
  pctfree 5
/
create index i_obj#_col# on cluster c_obj#_col#
/
create table histgrm$                                     /* histogram table */
( obj#            number not null,                          /* object number */
  col#            number not null,                          /* column number */
  row#            number not null,              /* row number (in row cache) */
  bucket          number not null,                          /* bucket number */
  endpoint        number not null)                  /* endpoint hashed value */
cluster c_obj#_col#(obj#, col#)
/
create table hist_head$                            /* histogram header table */
 (obj#            number not null,                          /* object number */
  col#            number not null,                          /* column number */
  bucket_cnt      number not null,                      /* number of buckets */
  row_cnt         number not null,           /* number of rows in histogram$ */
  cache_cnt       number not null,           /* number of rows in histogram$ */
  null_cnt        number,                  /* number of nulls in this column */
  timestamp       date,                   /* date of histogram's last update */
  sample_size     number,             /* for estimated stats, size of sample */
  minimum         number,           /* minimum value (if 1-bucket histogram) */
  maximum         number,           /* minimum value (if 1-bucket histogram) */
  spare1          number,                                           /* spare */
  spare2          number)                                           /* spare */
/

rem
rem Replication
rem

rem Add object group name to tables
ALTER TABLE system.repcat$_repcatlog ADD (gname VARCHAR2(30))
/
ALTER TABLE system.repcat$_repobject ADD (gname VARCHAR2(30))
/
ALTER TABLE system.def$_call ADD (groupname VARCHAR2(30))
/

rem Drop obsolete constraints
ALTER TABLE system.repcat$_repobject 
  DROP CONSTRAINT repcat$_repobject_prnt
/
ALTER TABLE system.repcat$_repprop 
  DROP CONSTRAINT repcat$_repprop_prnt2
/
ALTER TABLE system.repcat$_resolution 
  DROP CONSTRAINT repcat$_resolution_f2
/
ALTER TABLE system.repcat$_repobject
  DROP CONSTRAINT repcat$_repobject_type
/
ALTER TABLE system.repcat$_generated
  DROP CONSTRAINT repcat$_generated_obj
/
ALTER TABLE system.repcat$_repcatlog
  DROP CONSTRAINT repcat$_repcatlog_type
/
ALTER TABLE system.repcat$_repcatlog
  DROP CONSTRAINT repcat$_repcatlog_status
/
ALTER TABLE system.repcat$_repcatlog
  DROP CONSTRAINT repcat$_repcatlog_request
/


rem Add New constraints
ALTER TABLE system.repcat$_repobject 
  ADD CONSTRAINT repcat$_repobject_prnt FOREIGN KEY(gname)
      REFERENCES system.repcat$_repcat(sname)
      ON DELETE CASCADE
/
ALTER TABLE system.repcat$_repobject 
  ADD CONSTRAINT repcat$_repobject_type
      CHECK (type IN (-1, 1, 2, 4, 5, 7, 8, 9, 11, 12, -3))
/
ALTER TABLE system.repcat$_generated
  ADD CONSTRAINT repcat$_generated_obj
      CHECK (reason IN (0, 1, 2, 3, 4, 5, 6, 7))
/
ALTER TABLE system.repcat$_repcatlog
  ADD CONSTRAINT repcat$_repcatlog_request
      CHECK (request IN (-1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11))
/
ALTER TABLE system.repcat$_repcatlog
  ADD CONSTRAINT repcat$_repcatlog_type
      CHECK (type IN (-1, 0, 1, 2, 4, 5, 7, 8, 9, 11, 12, -3))
/
ALTER TABLE system.repcat$_repcatlog
  ADD  CONSTRAINT repcat$_repcatlog_status
       CHECK (status IN (0, 1, 2, 3, 4))
/

rem Convert repschemas to object group
UPDATE system.repcat$_repobject SET gname=sname
/
UPDATE system.def$_call SET groupname=schemaname
  WHERE destination_list = 'R'
/

rem
rem Invalidate non-stored triggers so that they will be recompiled under
rem stored trigger release. Status has no meaning for triggers prior to
rem 7.3, so this can be done under 7.2 and earlier.
rem
update obj$ set status = 5 where type = 12
/

rem
rem Bug 110894 requires a d_owner# column be added to dependency$
rem to be used by EXPORT.  
rem The d_owner# is explicitly set to NOT NULL in the last statement to
rem cover the situation where an upgrade, followed by a downgrade and then
rem another upgrade occurs.  The downgrade script does not remove the
rem d_owner#, it merely allows NULLs to be inserted.  When this upgrade script
rem is run the second time, a non-fatal error will occur on the attempt to
rem add the existing d_owner# column and then it will be set to NOT NULL.

ALTER TABLE dependency$
   ADD (d_owner# number)
/
UPDATE dependency$ SET d_owner# =
    (SELECT owner# FROM obj$ 
     WHERE obj# = d_obj#)
/
ALTER TABLE dependency$
     MODIFY (d_owner# number CONSTRAINT n_d_owner# NOT NULL)
/	

rem
rem In release 7.2 and earlier it was possible for sequences with illegal
rem parameters to be created. The following statements clip any illegal 
rem parameters into the legal range. Nothing needs to be done for a downgrade
rem because the old code would not properly deal with illegal parameters
rem anyway so we are just playing safe in any case. The 7.3 code silently
rem clips illegal parameters into the legal range.
rem
rem Sequences with illegal hiwater marks will have the hiwater marks updated
rem in such a way that the sequence will be considered saturated - i.e.
rem incapable of generating any more values.
rem

update seq$
  set   MINVALUE = -99999999999999999999999999
  where MINVALUE < -99999999999999999999999999
/
update seq$
  set   MINVALUE = 9999999999999999999999999999
  where MINVALUE > 9999999999999999999999999999
/
update seq$
  set   MAXVALUE = -99999999999999999999999999
  where MAXVALUE < -99999999999999999999999999
/
update seq$
  set   MAXVALUE = 9999999999999999999999999999
  where MAXVALUE > 9999999999999999999999999999
/
update seq$
  set   INCREMENT$ = -99999999999999999999999999
  where INCREMENT$ < -99999999999999999999999999
/
update seq$
  set   INCREMENT$ = 9999999999999999999999999999
  where INCREMENT$ > 9999999999999999999999999999
/
update seq$
  set   CACHE = -99999999999999999999999999
  where CACHE < -99999999999999999999999999
/
update seq$
  set   CACHE = 9999999999999999999999999999
  where CACHE > 9999999999999999999999999999
/
update seq$
  set   HIGHWATER = INCREMENT$ + MAXVALUE
  where HIGHWATER > INCREMENT$ + MAXVALUE and INCREMENT$ > 0
/
update seq$
  set   HIGHWATER = INCREMENT$ + MINVALUE
  where HIGHWATER < INCREMENT$ + MINVALUE and INCREMENT$ < 0
/
COMMIT
/
