Rem
Rem $Header: i0800050.sql 31-aug-98.12:23:36 rshaikh Exp $
Rem
Rem i0800050.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998. 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    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 END STAGE 1: load 8.1 specific tables
Rem =========================================================================


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