Rem
Rem $Header: i0800050.sql 27-mar-00.10:27:11 jdavison Exp $
Rem
Rem i0800050.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      i0800050.sql - load 8.1 specific tables that are need to
Rem		 	process basic sql statements
Rem
Rem    DESCRIPTION
Rem
Rem	 This script MUST be the first thing called from the 
Rem	 one path upgrade scripts (ie - u0703040.sql, u0800020.sql, ...)
Rem
Rem	 Only put statements in here that must be run in order
Rem	 to process basic sql commands.  For example, in order to 
Rem	 drop a package the code depends on the tables below.
Rem	 If these tables do not exist, we get a recursive sql error
Rem	 causing the command to be aborted.
Rem
Rem      The upgrade is performed in the following stages:
Rem        STAGE 1: load 8.1.3 specific tables 
Rem
Rem    RETURNS
Rem 
Rem    NOTES
Rem      You must connect internal to run this script
Rem 
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jdavison    03/27/00 - move alter hist_head due to fix for bug 1080644
Rem    rshaikh     06/04/99 - bug 902213: update trigger and related trig table
Rem    rshaikh     08/31/98 - move ustats to i0800050.sql
Rem    ncramesh    08/04/98 - change for sqlplus
Rem    rshaikh     06/17/98 - creation
Rem    rshaikh     06/17/98 - Created
Rem

Rem =========================================================================
Rem BEGIN STAGE 1: load 8.1.3 specific tables 
Rem =========================================================================

Rem =========================================================================
Rem 8.1.3 tables needed to perform the rest of the upgrade
Rem =========================================================================
create table icoldep$                   /* which columns an index depends on */
( obj#          number not null,                                 /* index id */
  bo#           number not null,                                 /* table id */
  intcol#       number not null    /* intcol# in table that index depends on */
)
cluster c_obj#(bo#)
/
create index i_icoldep$_obj on icoldep$ (obj#)
/
create table association$
(
  obj#            number not null,    /* obj# of table, type, pkg or func */
  property        number not null,     /* 1-column, 2-type, 3-pkg, 4-func */
                                                      /* 5-idx, 6-idxtype */
  intcol#         number,            /* intcol# if association for column */
  statstype#      number,        /* object number for the statistics type */
  default_selectivity  number,                     /* default selectivity */
  default_cpu_cost     number,                       /* defaault CPU cost */
  default_io_cost      number,                        /* default i/o cost */
  default_net_cost     number,                    /* default network cost */
  spare1               number,
  spare2               number
)
/
create unique index assoc1 on association$(obj#, intcol#)
/
create index assoc2 on association$(statstype#)
/
create table context$
(
  obj#          number  not null,                       /* object number */
  schema        varchar2(30) not null,                    /* schema name */
  package       varchar2(30) not null                    /* package name */
)
/
create unique index i_context on context$(obj#)
/
create table operator$
( obj#          number not null,                            /* object number */
  numbind       number not null,                       /* number of bindings */
  property      number not null                             /* property flag */
)
/
create unique index oper1 on operator$(obj#)
/
create table opbinding$
( obj#          number not null,                     /* obj# of the operator */
  bind#         number not null,                                 /* binding# */
  functionname  varchar2(92),                 /* function name in form A.B.C */
  returnschema  varchar2(30),      /* schema of return type (for ADTs) */
  returntype    varchar2(30),              /* return type of binding   */
  impschema     varchar2(30),       /* indextype implementation schema */
  imptype       varchar2(30),         /* indextype implementation type */
  property      number not null,                            /* property flag */
                                                /* 0x01 = WITH INDEX CONTEXT */
                                             /* 0x02 = RETURN ANCILLARY DATA */
                                                      /* 0x04 = ANCILLARY TO */
  spare1        varchar2(30),
  spare2        varchar2(30),
  spare3        number
)
/
create unique index opbind1 on opbinding$(obj#,bind#)
/
create table opancillary$
( obj#      number not null,          /* object number of ancillary operator */
  bind#     number not null,            /* bind number of ancillary operator */
  primop#   number not null,            /* object number of primary operator */
  primbind# number not null               /* bind number of primary operator */
)
/
create index opanc1 on opancillary$(obj#,bind#)
/
create table oparg$
( obj#          number not null,                     /* obj# of the operator */
  bind#         number not null,                                 /* binding# */
  position      number not null,                    /* position of arguments */
  type           varchar2(61)                    /* datatype of the argument */
)
/
create index oparg1 on oparg$(obj#)
/
alter table refcon$ add (
  expctoid      raw(16) /* TOID of exploded columns when ref is user-defined */
)
/
create table subpartcol$ (
  obj#   number not null,     /* object number of partitioned table or index */
  intcol# number not null,                         /* internal column number */
  col#   number not null,                                   /* column number */
  pos#   number not null,    /* position of column in key (1 = first column) */
  spare1 number)          /* spare column for any unanticipated future needs */
/
create index i_subpartcol$ on subpartcol$(obj#)
/

create table ustats$
(
  obj#            number  not null,    /* obj# for table(column) or index */
  intcol#         number,                  /* intcol# if stats for column */
  statstype#      number,                  /* obj# of the statistics type */
  property        number,      /* 1-index, 2-column, 3-implicit, 4-direct */
  statistics      raw(2000),                  /* uninterpreted statistics */
  spare1          number,                      /* support partition tab#  */
  spare2          number                      /* support partition number */
)
/
create unique index ustats1 on ustats$(obj#, intcol#)
/

Rem
Rem trigger upgrade (moved from c0800050.sql)
Rem
alter table trigger$ add
(
  sys_evts      number,                                    /* system events */
  nttrigcol     number,               /* intcol# on which trigger is defined */
  refprtname    varchar2(30)               /* PARENT referencing name */
)
/
update trigger$ set sys_evts=0, nttrigcol=0
/

create table triggerjavaf$
( obj#          number not null,                            /* object number */
  flags         long raw,                                  /* ub1 flag array */
  flaglength    number)                          /* length of ub1 flag array */
/
create table triggerjavas$
( obj#          number not null,                            /* object number */
  signature     long not null,                           /* method signature */
  siglength     number not null)               /* length of method signature */
/
create table triggerjavac$
( obj#          number not null,                            /* object number */
  ownername     varchar2(30) not null,             /* class owner name */
  ownerlength   number not null,               /* length of class owner name */
  classname     long not null,                          /* method class name */
  classlength   number not null)              /* length of method class name */
/
create table triggerjavam$
( obj#          number not null,                            /* object number */
  methodname    long not null,                                /* method name */
  methodlength  number not null,                 /* length of ub1 flag array */
  cookiesize    number not null)                              /* cookie size */
/
create unique index i_triggerjavaf on triggerjavaf$(obj#)
/
create unique index i_triggerjavas on triggerjavas$(obj#)
/
create unique index i_triggerjavac on triggerjavac$(obj#)
/
create unique index i_triggerjavam on triggerjavam$(obj#)
/

Rem
Rem trigger upgrade (moved from c0801030.sql)
Rem
alter table trigger$ add (nttrigatt     number)
/

Rem
Rem hist_head$ column additions (moved from c0800050.sql)
Rem
alter table sys.hist_head$
add
(
  avgcln          number,                           /* average column length */
  spare3          number,                                           /* spare */
  spare4          number                                            /* spare */
)
/
 
Rem =========================================================================
Rem END STAGE 1: load 8.1 specific tables
Rem =========================================================================

@@i0801050.sql

Rem *************************************************************************
Rem END i0800050.sql
Rem *************************************************************************
