Rem
Rem $Header: c0801030.sql 04-dec-98.09:28:42 rshaikh Exp $
Rem
Rem c0801030.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998. All Rights Reserved.
Rem
Rem    NAME
Rem      c0801030.sql - upgrade Oracle RDBMS from 8.1.3 to the new release
Rem
Rem    DESCRIPTION
Rem
Rem	 Put any dictionary related changes here (ie-create, alter, 
Rem    	 update,...).  DO NOT put PL/SQL modules in this script.  
Rem	 If you must upgrade using PL/SQL, put the module in a0801030.sql
Rem	 as catalog.sql and catproc.sql will be run before a0801030.sql
Rem	 is invoked.
Rem
Rem	 This script is called from u0801030.sql and c0800050.sql
Rem
Rem
Rem      This script performs the upgrade in the following stages:
Rem        STAGE 1: upgrade from 8.1.3 to 8.1.4
Rem        STAGE 2: call catalog.sql and catproc.sql
Rem
Rem    NOTES
Rem
Rem      * This script needs to be run in the new release's environment
Rem        (after installing the release to which you want to upgrade).
Rem
Rem      * You must be connected as INTERNAL to run this script.
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rshaikh     12/04/98 - swap alter and update stmts for tab[sub]part$
Rem    sbedarka    10/09/98 - #(725220) set maxvalue cycle for ora_tq_base$ seq
Rem    rshaikh     10/05/98 - update for 815
Rem    rshaikh     09/29/98 - drop type rowset
Rem    syeung      09/03/98 - set existing dataobj# values to NULLs for 
Rem                           composite partitions (8.1.4 and beyond)
Rem    attran      09/01/98 - PIOT: dataobj# = MINUB4MAXVAL ((ub4) 4294967295)
Rem    akruglik    08/24/98 - correct comment                                  
Rem    syeung      08/26/98 - make [ind|tab]compart$.dataobj# nullable
Rem    rshaikh     08/17/98 - bug 706032: remove OPERATOR system privs
Rem    akruglik    08/19/98 - replace 65535 (used to represent default 
Rem                           BUFFER_POOL when no value was specified) with 0 
Rem                           (making specifying nothing equivalent to 
Rem                           specifying BUFFER_POOL DEFAULT) in 
Rem                           PARTOBJ$.SPARE1; 
Rem                           replace NULL (used to represent default 
Rem                           BUFFER_POOL when DEFAULT or no value was 
Rem                           specified) with 0 in TABCOMPART$.DEFBUFPOOL, 
Rem                           INDCOMPART$.DEFBUFPOOL, PARTLOB$.DEFBUFPOOL, 
Rem                           and LOBCOMPPART$.DEFBUFPOOL
Rem    nagarwal    08/17/98 - add privs# 204, 212
Rem    attran      08/18/98 - PIOT: remove NOT-NULL constraints on
Rem				 dataobj# of tabpart$ & tabsubpart$
Rem    amozes      07/14/98 - add mon_mods$
Rem    rshaikh     07/14/98 - create
Rem    rshaikh     07/14/98 - Created
Rem

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

Rem
Rem monitoring modifications
Rem
create table mon_mods$
(
  obj#              number,                                 /* object number */
  inserts           number,  /* approx. number of inserts since last analyze */
  updates           number,  /* approx. number of updates since last analyze */
  deletes           number,  /* approx. number of deletes since last analyze */
  timestamp         date,     /* timestamp of last time this row was changed */
  flags             number                                          /* flags */
                                           /* 0x01 object has been truncated */
)
/
create unique index i_mon_mods$_obj on mon_mods$(obj#)
/

Rem
Rem trigger upgrade
Rem
alter table trigger$ add (nttrigatt     number)
/
create table viewtrcol$                      /* triggering view column table */
( obj#          number not null,             /* object number of base object */
  intcol#       number not null,                   /* internal column number */
  attribute#    number not null,          /* attribute# inside col for views */
  name          varchar2(4000) not null)         /* fully-qualified name */
cluster c_obj#(obj#)
/
create unique index i_viewtrcol1 on viewtrcol$(obj#, intcol#,attribute#)
/

REM
REM remove EXECUTE OPERATOR and ALTER OPERATOR system privileges
REM
delete from SYSTEM_PRIVILEGE_MAP where privilege=-204
/
delete from sysauth$ where privilege# = -204 or privilege# = -202
/
commit
/
alter system flush shared_pool
/
Rem add new system privilege
insert into SYSTEM_PRIVILEGE_MAP values (-204, 'EXECUTE ANY OPERATOR', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-212, 'EXECUTE ANY INDEXTYPE', 0);

grant all privileges to dba with admin option
/

Rem if no BUFFER_POOL was specified, 65535 was being stored in 
Rem PARTOBJ$.SPARE1 (from 8.0 to 8.1.3); 0 should have been stored instead 
Rem (other legal values are 1 and 2)
Rem =========================================================================

update partobj$ set spare1=0 where spare1 > 2
/

Rem =========================================================================
Rem if no BUFFER_POOL was specified or if DEFAULT was specified, NULL was 
Rem being stored in TABCOMPART$.DEFBUFPOOL, INDCOMPART$.DEFBUFPOOL, 
Rem PARTLOB$.DEFBUFPOOL, and LOBCOMPPART$.DEFBUFPOOL (from 8.1.0 to 8.1.3); 
Rem 0 should have been stored instead 
Rem =========================================================================

update tabcompart$ set defbufpool=0 where defbufpool is null
/

update indcompart$ set defbufpool=0 where defbufpool is null
/

update partlob$ set defbufpool=0 where defbufpool is null
/

update lobcomppart$ set defbufpool=0 where defbufpool is null
/

Rem The column dataobj# of tabpart$ and tabsubpart$ are nullable in 8.1.4
alter table tabpart$ modify (dataobj# number null);
alter table tabsubpart$ modify (dataobj# number null);
update tabpart$ set dataobj# = NULL where dataobj# = 4294967295;
update tabsubpart$ set dataobj# = NULL where dataobj# = 4294967295;
/
Rem
Rem make [tab|ind]compart$ nullable and set existing dataobj# values in 
Rem the tables and obj$ table to NULLs
Rem
alter table tabcompart$ modify (dataobj# number NULL)
/
alter table indcompart$ modify (dataobj# number NULL)
/
update tabcompart$ set dataobj#=NULL
/
update indcompart$ set dataobj#=NULL
/
update obj$ set dataobj#=NULL where obj# in   
    (select tcp.obj# from tabcompart$ tcp)
/
update obj$ set dataobj#=NULL where obj# in 
    (select icp.obj# from indcompart$ icp)
/
REM
REM Bug 725220 - Parallel Query Option uses the sequence ora_tq_base$ to
REM generate Table Queue ids, but with the prior nomaxvalue nocycle
REM specification, an internal overflow could occur. To prevent this overflow
REM a ceiling is specified, along with cycle.
REM
REM It is important to:
REM
REM      * Ensure Parallel Query Option is not in use during this upgrade
REM
REM      * Database closed and restarted after the upgrade is completed.
REM
 
drop sequence ora_tq_base$
/
create sequence ora_tq_base$
  start with 1
  increment by 1
  nominvalue
  maxvalue 4294967  /* Ceiling as anything higher causes overflow internally */
  nocache
  noorder
  cycle                        /* Needed to complement the ceiling specified */
/

REM rowset removed from dbmsindx.sql
drop type rowset
/

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

Rem =========================================================================
Rem BEGIN STAGE 2: run the 8.1.4 version of catalog and catproc
Rem =========================================================================

Rem For the next release, we'll need to create a file called c0801040.sql,
Rem which should call catalog and catproc.
Rem When updating this script for the next release, delete the calls to
Rem the scripts above and uncomment the call to c0801040.sql below.

@@c0801040

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

Rem *************************************************************************
Rem END c0801030.sql
Rem *************************************************************************
