Rem
Rem $Header: c0800050.sql 12-jun-00.14:45:18 arithikr Exp $
Rem
Rem c0800050.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      c0800050.sql - upgrade Oracle RDBMS from 8.0.5 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 a0800050.sql
Rem	 as catalog.sql and catproc.sql will be run before a0800050.sql
Rem	 is invoked.
Rem
Rem	 This script is called from u0800050.sql and c0800040.sql
Rem
Rem
Rem      This script performs the upgrade in the following stages:
Rem        STAGE 1: upgrade from 8.0.5 to 8.1.3
Rem        STAGE 2: upgrade from 8.1.3 to 8.1.4
Rem        STAGE 3: upgrade from 8.1.4 to 8.1.5
Rem        STAGE 4: upgrade to new release
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    MODIFIED   (MM/DD/YY)
Rem    arithikr    06/12/00 - Bug1309921: fix LENGTHB when populate snap$
Rem    jdavison    06/02/00 - Remove backslashes causing duplicate stmt executi
Rem    sbalaram    06/01/00 - Fix after resolving Bug 1292760
Rem    jdavison    03/27/00 - move alter hist_head due to fix for bug 1080644
Rem    rshaikh     11/01/99 - move 8.1.3 and 8.1.4 upgrade here
Rem    rshaikh     06/04/99 - bug 902213: mv trigger and related trig tables
Rem    rshaikh     03/10/99 - add dbmsotpt.sql before calling dbms_output
Rem    masubram    01/18/99 - update the flag col in mlog$ and snap$ only once
Rem    sbodagal    12/14/98 - change outln privileges
Rem    rshaikh     11/17/98 - dont create OUTLN if it already exists
Rem    rshaikh     10/08/98 - bug 678295: swap owner# in con$
Rem    rshaikh     08/31/98 - move ustats to i0800050.sql
Rem    ncramesh    08/04/98 - change for sqlplus
Rem    rshaikh     07/21/98 - call d0801030.sql
Rem    rshaikh     07/31/98 - remove obsolete fixed view info
Rem    rshaikh     07/21/98 - add 8.1.3 sys privs to dba
Rem    jingliu     07/13/98 - Add refscn to snap_reftime$ for upgrade   
Rem    rwessman    07/09/98 - Added column to i_audit
Rem    mkamath     08/04/98 - set owner instance of queue-tables to the current
Rem    hasun       06/30/98 - Apply snapshot upgrade from old u0800050
Rem    rshaikh     06/17/98 - rework upgrade:
Rem			      * move all pre-catalog and catproc stmts from 
Rem				u0800050.sql here
Rem			      * fix bug 685836
Rem    ato         06/12/98 - recreate existing AQ objects as first class obje
Rem    akalra      06/12/98 - remove @@catrm - done in catproc. add comments
Rem    akalra      06/10/98 - scheduler -> resource manager
Rem    akalra      06/03/98 - db scheduling -> db resource manager             
Rem    mkamath     06/10/98 - Adding upgrade for AQ Propagation Scheduling
Rem    nbhatt      06/08/98 - add AQ queue table affinity table
Rem    rshaikh     06/08/98 - add snapshot cols and fix privilege and audit
Rem			      statements added by asurpur
Rem    jfinnert    06/05/98 - Reflect changes for summary mgmt
Rem    gclaborn    06/05/98 - Separate exp tables for actions and objects
Rem    rshaikh     06/04/98 - update trigger.sys_evts and nttrigcol
Rem    usundara    06/03/98 - rework upgrade/downgrade/migration scripts:
Rem                           * change name c805u813.sql -> u0800050.sql;
Rem                           * update comments;
Rem                           * [rshaikh] add changes from RSHAIKH and Murali;
Rem    bgoyal      05/19/98 - upgrade script for temporary tables              
Rem    asurpur     06/03/98 - Altering AUDIT_OPTION_MAP to add new column
Rem    asurpur     06/02/98 - Altering table SYSTEM_PRIVILEGE_MAP to add new co
Rem    rshaikh     05/22/98 - take out type# and add defloggin to tabcompar
Rem    mjungerm    05/20/98 - modify javasnm$ longname to be type long raw
Rem    gclaborn    05/19/98 - Add exppkgs$ and expdep$
Rem    rshaikh     05/18/98 - add proxy
Rem    nagarwal    05/19/98 - add indexes to operator dict
Rem    rshaikh     05/08/98 - add extra columns 
Rem	               diffed up to label RDBMS_8.1.3_SOLARIS_980512
Rem    rshaikh     05/05/98 - remove spool
Rem    rshaikh     04/20/98 - add sys priv, aud opts, trigger, java, mls,
Rem				 repli, partitions, oper, assoc, and ustats 
Rem    akalra      04/17/98 - Upgrade for db scheduling
Rem    jstamos     04/08/98 - more flavor support                              
Rem    clei        04/03/98 - Add row level security metadata
Rem    dmwong      04/03/98 - add context for app ctx                          
Rem    amozes      03/26/98 - add column epvalue to histgrm$                  
Rem    jfeenan     04/08/98 - Modify sumdep$
Rem    amozes      03/10/98 - new hist_head columns                            
Rem    jfeenan     03/24/98 - Add summary and dimension indicies to upgrade scr
Rem    jfeenan     03/20/98 - Add upgrade metadata for summaries and dimensions
Rem    jstamos     02/27/98 - Lik's replication changes and flavor support
Rem    rjenkins    02/03/98 - adding icoldep
Rem    mmonajje    01/26/98 - upgrade script; Moved to new file name 
Rem    mkrishna    01/09/98 - upgrade script for 8.0.4 to 8.1.2
Rem    mkrishna    01/09/98 - Created
Rem

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

REM
REM bug 678295: a user may have migrated from v7 to v8 before
REM the fix was put into migrate.bsq.  For these users we need to
REM convert the owner#'s in con$.  Since the migrate user was dropped
REM as part of the migration, we know which constraints belong to 
REM that user because the user# doesn't exist in user$
REM
alter system flush shared_pool
/
update con$ set owner# = (select user# from user$ where name='SYS')
   where owner# not in (select user# from user$)
/
alter system flush shared_pool
/


Rem Add tables for summary management/query rewrite
create table sum$                     /* summary table */
( obj#             number not null,   /* object number */
  containerobj#    number not null,   /* object number of the container tab */
  containertype    number not null,   /* type of container - snap, table */
  containernam     varchar2(30),/* name of container */
  fullrefreshtim   number,            /* number in seconds for full refresh */
  Increfreshtim    number,            /* number in seconds for full refresh */
  lastrefreshscn   number,            /* scn of last transaction to refresh */
  lastrefreshdate  date,              /* date of last transaction to refresh */
  refreshmode      number,            /* 0=none, 1=any, 2=inc, 3=full */
  pflags           number,            /* summary properties (bit flags): */
                                      /* refer to  QSMKSANA* flags */
  mflags           number,            /* summary mutable flags */
                                        /* 0x01 summary is known stale */
                                        /* 0x02 summary is unusable */
                                        /* 0x04 summary is disabled */
  numdetailtab     integer,           /* number of detail tables */
  numaggregates    integer,           /* number of measures */
  numkeycolumns    integer,           /* keys are group by fields */
  numjoins         integer,           /* number of joins in summary */
  sumtextlen       number not null,   /* length of text body */
  sumtext          long not null,     /* text from 'select' onward */
  fromoffset       number not null,   /* offset in bytes to FROM clause */
  fromlen          number not null,   /* length from the F to the last table */
  metaversion      number not null,   /* version of this the canonical forms */
  objcount         number not null,   /* number of dependant objects */
  spare1           number,
  spare2           number,
  spare3           varchar2(1000),
  spare4           date
)
/
create unique index i_sum$_1 on sum$(obj#)
/
create table sumdetail$            /* summary detail table */
( sumobj#       number not null,   /* object number */
  detailobj#    number not null,   /* obj number of detail table */
  detailobjtype number,            /* 1=table,2=view,3=snap,4=cont. table */
  detailalias   varchar2(30),/* alias if used */
  refreshscn    number,            /* scn of last refresh */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create index i_sumdetail$_1 on sumdetail$(sumobj#)
/
create index i_sumdetail$_2 on sumdetail$(detailobj#)
/
create table sumkey$             /* summary key table */
( sumobj#       number not null, /* parent summary obj */
  sumcolpos#    number not null, /* position of column in select list */
  containercol# number not null, /* pos. in container where stored */
  detailobj#    number not null, /* detail table object from FROM */
  detailobjtype number,          /* detail table type - see sumdetail$ */
  detailcol#    number,          /* col number within detail table */
  groupingpos#  number,          /* pos. in GB clause */
  exprlen       number,
  expression    varchar2(4000),
  textlen       number,
  text          long,
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create unique index i_sumkey$_1 on sumkey$(sumobj#,sumcolpos#,groupingpos#)
/
create table sumagg$             /* summary aggregate table */
( sumobj#       number not null, /* parent summary obj */
  sumcolpos#    number not null, /* position of column in select list */
  containercol# number not null, /* pos. in container where stored */
  aggfunction   integer,         /* type of aggregate function */
  flags         number,          /* sumagg properties (bit flags): */
                                 /* 0x01=distinct aggregate */
  exprlen       number,          /* len of the canonical form */
  expression    varchar2(2000),  /* expr in canonical form */
  textlen       number,          /* len of aggregate text */
  aggtext       long,            /* text of agg. expression */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create unique index i_sumagg$_1 on sumagg$(sumobj#,sumcolpos#)
/
create table sumjoin$             /* summary join table */
( sumobj#       number not null,  /* Object number of the summary */
  tab1obj#      number not null,  /* Object number of the first table */
  tab1col#      number not null,  /* Column number in the first table */
  tab2obj#      number not null,  /* Object number of the second table */
  tab2col#      number not null,  /* Column number of second table */
  joinop        integer,          /* Op code as defined in opndef.h (OPTTEQ) */
  flags         number,           /* currently unused */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create index i_sumjoin$_1 on sumjoin$(sumobj#,tab1obj#)
/
create table dim$                     /* dimension table */
( obj#             number not null,   /* object number */
  dimtextlen       number,            /* length of the dimension text */
  dimtext          long,
  flags            number,            /* currently only maintains force */
  numlevels        number,            /* number or levels */
  numlevkeys       number,            /* number of level keys */
  numjoinkeys      number,            /* number of join keys */
  numhier          number,            /* number or hierarchies */
  numhierlv        number,            /* number of hierarchy levels */
  numattr          number,            /* number of attributes */
  spare1           number,
  spare2           number,
  spare3           varchar2(1000),
  spare4           date
)
/
create unique index i_dim$_1 on dim$(obj#)
/
create table dimlevel$              /* a dimension level */
( dimobj#       number not null,    /* object number */
  levelid#      number not null,    /* unique number within dimension */
  levelname     varchar2(30), /* text name associated with the level */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create unique index i_dimlevel$_1 on dimlevel$(dimobj#,levelid#)
/
create table dimlevelkey$          /* the columns of a level */
( dimobj#       number not null,   /* object number */
  levelid#      number not null,   /* unique number within dimension */
  keypos#       number not null,   /* postition of column in the key */
  detailobj#    number,            /* detail table object */
  col#          number,            /* col number within detail table */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create index i_dimlevelkey$_1 on dimlevelkey$(dimobj#,levelid#)
/
create index i_dimlevelkey$_2 on dimlevelkey$(detailobj#,col#)
/
create table dimjoinkey$           /* describes a join in a dimension */
( dimobj#       number not null,   /* object number */
  joinkeyid#    number not null,   /* unique number within dimension */
  keypos#       number not null,   /* postition of column in the key */
  hierid#       number,            /* number of hier which this key links */
  levelid#      number,           /* number of hier which this key links */
  detailobj#    number,            /* detail table object */
  col#          number,            /* col number within detail table */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create index i_dimjoinkey$_1 on dimjoinkey$(dimobj#,joinkeyid#)
/
create index i_dimjoinkey$_2 on dimjoinkey$(detailobj#,col#)
/ 
create table dimattr$             /* describes attributes of a dimension */
( dimobj#       number not null,  /* object number */
  levelid#      number not null,  /* id of the level */
  detailobj#    number not null,  /* detail table object */
  col#          number,           /* col number within detail table */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create index i_dimattr$_1 on dimattr$(dimobj#,levelid#)
/
create index i_dimattr$_2 on dimattr$(detailobj#,col#)
/
create table hier$                  /* a hierarchy */
( dimobj#       number not null,    /* object number */
  hierid#       number not null,    /* unique number within dimension */
  hiername      varchar2(30), /* text name or hierarchy */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create unique index i_hier$_1 on hier$(dimobj#,hierid#)
/
create table hierlevel$            /* identifies a level in a hierarchy */
( dimobj#       number not null,   /* object number */
  hierid#       number not null,   /* unique number of the hierarchy */
  pos#          number not null,   /* postition of level in hierarchy */
  levelid#      number not null,   /* id of the level */
  joinkeyid#    number,            /* join key - if levels are from 2 tables */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create index i_hierlevel$_1 on hierlevel$(dimobj#,hierid#)
/
create table sumdep$                             /* summary dependency table */
( sumobj#       number not null,                  /* dependent object number */
  order#        number not null,                             /* order number */
  p_obj#        number not null,		     /* parent object number */
  p_type        number not null,                       /* parent object type */
  p_ref_time    date,                            /* parent refresh timestamp */
  p_ref_scn     number,                                /* parent refresh scn */
  flags         number
)
/
create index i_sumdep$_1 on sumdep$(sumobj#,order#)
/
create index i_sumdep$_2 on sumdep$(p_obj#,p_type)
/
create table sumdelta$           /* sumdelta table */
( tableobj#     number not null, /* detail table obj# loaded */
  partitionobj# number not null, /* partition obj# that was loaded */
  dmloperation  varchar2(1),     /* I=insert, D=delete */
  scn           number not null, /* SCN when the bulk DML occurred. */
  timestamp     date not null,   /* Time when the bulk DML occurred. */
  lowrowid      rowid not null,  /* low rowid modified in this partition. */
  highrowid     rowid not null,  /* high rowid modified in this partition. */
  spare1        number,
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/
create unique index i_sumdelta$ on 
  sumdelta$(tableobj#, partitionobj#, timestamp, lowrowid, highrowid) 
/

commit
/

create table proxy$
( client#       NUMBER NOT NULL,                           /* client user ID */
  proxy#        NUMBER NOT NULL,                            /* proxy user ID */
  role#         NUMBER NOT NULL,                                  /* role ID */
  flags         NUMBER NOT NULL       /* Mask flags of associated with entry */
             /* Flags values:
              * 1 = proxy can activate all client roles
              * 2 = proxy can activate no client roles
              * 4 = role can be activated by proxy,
              * 8 = role cannot be activated by proxy
              */
)
/
create unique index i_proxy$ on proxy$(client#, proxy#, role#, flags)
/

alter table audit$ add (proxy# number)
/
drop index i_audit
/
create unique index i_audit on audit$(user#, proxy#, option#)
/

Rem =========================================================================
Rem Upgrade for DBMS Resource Manager
Rem =========================================================================

Rem create dictionary tables
create table resource_plan$
( obj#                number not null,                       /* obj# of plan */
  name                varchar2(30),                          /* name of plan */
  mandatory           number,                   /* whether plan is mandatory */
  cpu_method          varchar2(30),        /* cpu resource allocation method */
  mast_method         varchar2(30),       /* max. active sess. target method */
  pdl_method          varchar2(30),          /* parallel degree limit method */
  num_plan_directives number,           /* number of directives for the plan */
  description         varchar2(2000),                             /* comment */
  status              varchar2(30)              /* whether active or pending */
)
/
create table resource_consumer_group$
( obj#              number not null,                        /* obj# of group */
  name              varchar2(30),                           /* name of group */
  mandatory         number,                    /* whether group is mandatory */
  cpu_method        varchar2(30),          /* cpu resource allocation method */
  description       varchar2(2000),                               /* comment */
  status            varchar2(30)                /* whether active or pending */
)
/
create table resource_plan_directive$
( obj#                      number not null,          /* obj# of parent plan */
  plan                      varchar2(30),             /* name of parent plan */
  group_or_subplan          varchar2(30),        /* name of group or subplan */
  is_subplan                number not null,  /* whether the above is a plan */
  description               varchar2(2000),                       /* comment */
  mandatory                 number,    /* whether the directive is mandatory */
  cpu_p1                    number, /* first parameter for cpu alloc. method */
  cpu_p2                    number,   /* second param. for cpu alloc. method */
  cpu_p3                    number, /* third parameter for cpu alloc. method */
  cpu_p4                    number,   /* fourth param. for cpu alloc. method */
  cpu_p5                    number, /* fifth parameter for cpu alloc. method */
  cpu_p6                    number, /* sixth parameter for cpu alloc. method */
  cpu_p7                    number,  /* seventh param. for cpu alloc. method */
  cpu_p8                    number,   /* eighth param. for cpu alloc. method */
  max_active_sess_target_p1 number,                        /* mast parameter */
  parallel_degree_limit_p1  number,                         /* pdl parameter */
  status                    varchar2(30)        /* whether active or pending */
)
/

Rem =========================================================================
Rem histgrm$ column addition
Rem =========================================================================

alter table sys.histgrm$
add
(
  epvalue         varchar2(1000)               /* endpoint value information */
)
/

Rem
Rem Row Level Security
Rem
create table rls$                                      /* Row Level Security */
(
  obj#            NUMBER NOT NULL,                   /* parent object number */
  pname           VARCHAR2(30) NOT NULL,                   /* name of policy */
  stmt_type       NUMBER NOT NULL,             /* applicable statement type: */
                           /* 1 = select, 2 = insert, 4 = update, 8 = delete */
  check_opt       NUMBER NOT NULL,                      /* with check option */
                                            /* 0 = no checking, 1 = checking */
  enable_flag     NUMBER NOT NULL,              /* 0 = disabled, 1 = enabled */
  pfschma         VARCHAR2(30) NOT NULL,        /* schema of policy function */
  ppname          VARCHAR2(30),                       /* policy package name */
  pfname          VARCHAR2(30) NOT NULL              /* policy function name */
)
/
create unique index i_rls on rls$(obj#, pname)
/
create table ruleset$ (
        obj#            number not null,                    /* object number */
        baseobj         number not null,               /* base object number */
        rules_table     number not null         /* table in which rules live */
)
/

REM 
REM upgrade for OUTLN user.  Only let it pass if the user
REM doesn't already exist.
REM

@@dbmssql
@@prvthssq.plb
@@prvtssql.plb
@@prvtsql.plb
@@dbmsotpt

set serveroutput on

DECLARE
  user_exists EXCEPTION;
  outln_user number;
  outln_tables number;
  extra_outln_tables number;
  DDL_CURSOR integer;
BEGIN
  select count(*) into outln_user from user$ where name='OUTLN';

  select count(*) into outln_tables from obj$ where name in
	('OL$', 'OL$HINTS') and owner#=
	(select user# from user$ where name='OUTLN');  

  select count(*) into extra_outln_tables from obj$ where name not in
	('OL$', 'OL$HINTS') and type#=2 and owner#=
	(select user# from user$ where name='OUTLN');  


  DDL_CURSOR := dbms_sql.open_cursor;	
  IF outln_user = 0 THEN
	dbms_sql.parse(DDL_CURSOR, 'create user outln identified by outln',
		 dbms_sql.native);
	dbms_sql.parse(DDL_CURSOR, 
                 'grant connect, resource, execute any procedure to outln',
		 dbms_sql.native);
	dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$ ( '||
	  'ol_name           varchar2(30), ' ||      
  	  'sql_text          long,  ' ||               
  	  'textlen           number,  ' ||             
  	  'signature         raw(16), ' ||             
  	  'hash_value        number, ' ||              
  	  'category          varchar2(30), ' ||        
  	  'version           varchar2(64), ' ||        
  	  'creator           varchar2(30), ' ||        
  	  'timestamp         date,  ' ||               
  	  'flags             number, ' ||              
  	  'hintcount         number)', dbms_sql.native);
	dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$hints ( '||
	  'ol_name           varchar2(30),  '||
  	  'hint#             number,  '||      
  	  'category          varchar2(30),  '||
  	  'hint_type         number,  '||      
  	  'hint_text         varchar2(512), '||
  	  'stage#            number,  '||      
  	  'node#             number, '||       
  	  'table_name        varchar2(30),  '||
  	  'table_tin         number,  '||      
  	  'table_pos         number)', dbms_sql.native);
	dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$name '||
	  'on outln.ol$(ol_name)', dbms_sql.native);
	dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$signature '||
	  ' on outln.ol$(signature,category)', dbms_sql.native);
	dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$hnt_num '||
	  ' on outln.ol$hints(ol_name, hint#)', dbms_sql.native);
	dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
  ELSE
    	IF outln_tables!=2 or extra_outln_tables!=0 THEN
		dbms_output.put_line('ERROR - OUTLN USER ALREADY EXISTS');
		RAISE user_exists;
	ELSE
		dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
	END IF;
  END IF;

  EXCEPTION
   	WHEN user_exists THEN
	  RAISE;

END;
/

create table partlob$ (
  lobj#       number not null,                 /* object number of partition */
  tabobj#     number not null,    /* obj# of the table containing lob column */
  intcol#     number not null,       /* internal column id of the lob column */
  defts#      number,             /* default TABLESPACE; NULL if unspecified */
  defchunk    number not null,     /* default oracle blocks in one lob chunk */
  defpctver$  number not null,                       /* default version pool */
  defflags    number not null,            /* default CACHE and LOGGING attrs */
                                                           /* 0x0000 = CACHE */
                                                 /* 0x0001 = NOCACHE LOGGING */
                                               /* 0x0002 = NOCACHE NOLOGGING */
  defpro      number not null,                 /* default partition property */
                                             /* 0x02 = enable storage in row */
  definiexts  number,    /* default INITIAL extent size; NULL if unspecified */
  defextsize  number,       /* default NEXT extent size; NULL if unspecified */
  defminexts  number,             /* default MINEXTENTS; NULL if unspecified */
  defmaxexts  number,             /* default MAXEXTENTS; NULL if unspecified */
  defextpct   number,            /* default PCTINCREASE; NULL if unspecified */
  deflists    number,        /* default FREELISTS value; NULL if unspecified */
  defgroups   number,        /* default FREELIST GROUPS; NULL if unspecified */
  defbufpool  number,     
  spare1      number,
  spare2      number,
  spare3      number       
)
/
create table lobfrag$ (
  fragobj#     number not null,           /* object number of a LOB fragment */
  parentobj#   number not null,               /* object number of the parent */
                                        /* (LOB column for partitions or LOB */
  tabfragobj#  number not null,  
                                             /* partition for subpartitions) */
  indfragobj#  number not null,  /* object number of corresponding LOB index */
  frag#        number not null,       /* fragment # (partn w/in a LOB column */
                                  /* or subpartition w/in the LOB partition) */
  fragtype$    char(1),                                /* LOB fragment type: */
                                     /* 'P' - partition;  'S' - subpartition */
  ts#         number not null,          /* tablespace number of LOB fragment */
  file#       number not null,                 /* segment header file number */
  block#      number not null,                /* segment header block number */
  chunk       number not null,             /* oracle blocks in one lob chunk */
  pctversion$ number not null,                               /* version pool */
  fragflags   number not null,               /* CACHE and LOGGING attributes */
                                                           /* 0x0000 = CACHE */
                                                 /* 0x0001 = NOCACHE LOGGING */
                                               /* 0x0002 = NOCACHE NOLOGGING */
  fragpro     number not null,                          /* fragment property */
                                             /* 0x02 = enable storage in row */
  spare1      number,
  spare2      number,
  spare3      number       
)
/
create table lobcomppart$ (
  partobj#     number not null,          /* object number of a LOB partition */
  lobj#        number not null,  /* object number of the LOB column to which */
                                                    /* the partition belongs */
  tabpartobj#  number not null, 
  indpartobj#  number not null,      /* object number of the index partition */
                                      /* to which this partition corresponds */
  part#        number not null,        /* partition number w/in a LOB column */
  defts#       number,            /* default TABLESPACE; NULL if unspecified */
  defchunk     number not null,    /* default oracle blocks in one lob chunk */
  defpctver$   number not null,                      /* default version pool */
  defflags     number not null,           /* default CACHE and LOGGING attrs */
                                                           /* 0x0000 = CACHE */
                                                 /* 0x0001 = NOCACHE LOGGING */
                                               /* 0x0002 = NOCACHE NOLOGGING */
  defpro       number not null,                /* default partition property */
                                             /* 0x02 = enable storage in row */
  definiexts   number,   /* default INITIAL extent size; NULL if unspecified */
  defextsize   number,      /* default NEXT extent size; NULL if unspecified */
  defminexts   number,            /* default MINEXTENTS; NULL if unspecified */
  defmaxexts   number,            /* default MAXEXTENTS; NULL if unspecified */
  defextpct    number,           /* default PCTINCREASE; NULL if unspecified */
  deflists     number,       /* default FREELISTS value; NULL if unspecified */
  defgroups    number,       /* default FREELIST GROUPS; NULL if unspecified */
  defbufpool   number, 
  spare1       number,
  spare2       number,
  spare3       number       
)
/


Rem
Rem Update the refcon$ - scoped references to delete the WITH ROWID bit
Rem in case the scoped ref bit is set.
Rem This does not require a downgrade.
Rem
update refcon$ 
set    reftyp = 1
where  reftyp = 3
/


Rem
Rem SYSTEM_PRIVILEGE_MAP has a new column property
Rem
alter table SYSTEM_PRIVILEGE_MAP add(
	PROPERTY      number          /* 0x01 = do not export this privilege */
				      /* using sql statements */
)
/
update SYSTEM_PRIVILEGE_MAP set property = 0
/
alter table SYSTEM_PRIVILEGE_MAP modify (property number not null)
/
comment on column SYSTEM_PRIVILEGE_MAP.PROPERTY is
'Property flag of privilege like not export this privilege, etc'
/
Rem add new system privileges
insert into SYSTEM_PRIVILEGE_MAP values (-186, 'EXTENDS TYPE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-200, 'CREATE OPERATOR', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-201, 'CREATE ANY OPERATOR', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-202, 'ALTER OPERATOR', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-203, 'DROP ANY OPERATOR', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-204, 'EXECUTE OPERATOR', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-205, 'CREATE INDEXTYPE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-206, 'CREATE ANY INDEXTYPE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-207, 'ALTER ANY INDEXTYPE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-208, 'DROP ANY INDEXTYPE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-209, 'EXTENDS ANY TYPE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-210, 'REWRITE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-211, 'GLOBAL REWRITE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-214, 'CREATE DIMENSION', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-215, 'CREATE ANY DIMENSION', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-216, 'ALTER ANY DIMENSION', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-217, 'DROP ANY DIMENSION', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-218, 'MANAGE ANY QUEUE', 1);
insert into SYSTEM_PRIVILEGE_MAP values (-219, 'ENQUEUE ANY QUEUE', 1);
insert into SYSTEM_PRIVILEGE_MAP values (-220, 'DEQUEUE ANY QUEUE', 1);
insert into SYSTEM_PRIVILEGE_MAP values (-222, 'CREATE ANY CONTEXT', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-223, 'DROP ANY CONTEXT', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-224, 'CREATE ANY OUTLINE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-225, 'ALTER ANY OUTLINE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-226, 'DROP ANY OUTLINE', 0);
insert into SYSTEM_PRIVILEGE_MAP values (-227, 'ADMINISTER RESOURCE MANAGER', 1);

Rem
Rem  STMT_AUDIT_OPTION_MAP has a new column property
Rem
alter table  STMT_AUDIT_OPTION_MAP add(
	PROPERTY      number          /* 0x01 = do not export this privilege */
				      /* using sql statements */
)
/
update STMT_AUDIT_OPTION_MAP set property=0
/
alter table  STMT_AUDIT_OPTION_MAP modify(
	property  number not null
)
/
comment on column STMT_AUDIT_OPTION_MAP.PROPERTY is
'Property flag of auditing option'
/
insert into STMT_AUDIT_OPTION_MAP values ( 34, 'DIMENSION', 0);
insert into STMT_AUDIT_OPTION_MAP values ( 39, 'SUMMARY', 0);
insert into STMT_AUDIT_OPTION_MAP values (210, 'REWRITE', 0);
insert into STMT_AUDIT_OPTION_MAP values (211, 'GLOBAL REWRITE', 0);
insert into STMT_AUDIT_OPTION_MAP values (214, 'CREATE DIMENSION', 0);
insert into STMT_AUDIT_OPTION_MAP values (215, 'CREATE ANY DIMENSION', 0);
insert into STMT_AUDIT_OPTION_MAP values (216, 'ALTER ANY DIMENSION', 0);
insert into STMT_AUDIT_OPTION_MAP values (217, 'DROP ANY DIMENSION', 0);
insert into STMT_AUDIT_OPTION_MAP values (218, 'MANAGE ANY QUEUE', 1);
insert into STMT_AUDIT_OPTION_MAP values (219, 'ENQUEUE ANY QUEUE', 1);
insert into STMT_AUDIT_OPTION_MAP values (220, 'DEQUEUE ANY QUEUE', 1);
insert into STMT_AUDIT_OPTION_MAP values (221, 'CONTEXT', 0);
insert into STMT_AUDIT_OPTION_MAP values (224, 'CREATE ANY OUTLINE', 0);
insert into STMT_AUDIT_OPTION_MAP values (225, 'ALTER ANY OUTLINE', 0);
insert into STMT_AUDIT_OPTION_MAP values (226, 'DROP ANY OUTLINE', 0);

insert into TABLE_PRIVILEGE_MAP values (20, 'ENQUEUE');
insert into TABLE_PRIVILEGE_MAP values (21, 'DEQUEUE');

create table reg$
( subscription_name varchar2(128) not null,
  location_name     varchar2(256) not null,
  user#             number not null,
  user_context      raw(128) not null,
  context_size      number not null,
  namespace         number)
/
create table loc$
( location_name     varchar2(256) not null,
  emon#             number,
  connection#       number)
/
grant create indextype, create operator to resource
/
grant all privileges to dba with admin option
/


REM =========================================================================
REM BEGIN Snapshots/Materialized Views
REM =========================================================================
---
--- snap_site$
---
create table snap_site$ (                    /* mapping of site names to IDs */
  site_name  varchar2(128) not null,                            /* site name */
  site_id    integer)                                             /* site ID */
/
create unique index i_snap_site1 on snap_site$(site_name)
/
create sequence snapsite_id$                    /* snapshot Site ID sequence */
  increment by 1
  start with 1
  minvalue 1
  maxvalue 4294967295                           /* max portable value of UB4 */
  nocycle
/
---
--- snap_logdep$
---
CREATE TABLE snap_logdep$ (                           /* slog$ for sumdelta$ */
  tableobj#  number,                             /* obj# of the master table */
  snapid     integer,           /* internal id of the snapshot using the log */
  snaptime   date)                  /* time of last refresh for the snapshot */
/
create unique index i_snap_logdep1 on snap_logdep$(tableobj#, snapid)
/
---
--- snap_loadertime$
---
CREATE TABLE snap_loadertime$ (                       /* mlog$ for sumdelta$ */
  tableobj#  number,                             /* obj# of the master table */
  oldest     date,                /* oldest information for the master table */
  youngest   date,                 /* most recent refresh timestamp assigned */
  flag       number)                            /* (reserved for future use) */
/
create unique index i_snap_loadertime1 on snap_loadertime$(tableobj#)
/
---
--- snap$
---
alter table snap$ add (
  rscn	     number,                                     /* last refresh scn */
  refhnt     number,                                         /* refresh hint */
  query_len  integer,                 /* length of snapshot definition query */
  instsite   integer default 0,                        /* instantiating site */
  flavor_id  number)                                            /* flavor id */
/
drop index i_snap1
/
create unique index i_snap1 on snap$(vname, sowner, instsite)
/
DECLARE
  CURSOR all_snaps IS
    SELECT vname, sowner, instsite, query_txt FROM snap$
    FOR UPDATE;
  var1 number:=0;
BEGIN
  FOR snap IN all_snaps LOOP
    var1 := LENGTHB(snap.query_txt);
    UPDATE snap$ s 
       SET s.query_len = var1,
           s.rscn      = 0,
           s.refhnt    = 0,
           s.flavor_id = 0
     WHERE s.vname = snap.vname AND s.sowner = snap.sowner
       AND s.instsite = snap.instsite;
  END LOOP;
  COMMIT;
END;
/
Rem 
Rem Set the bits indicating that the snapshot is an ON DEMAND snapshot and
Rem is disabled for query re-write
Rem
update snap$ set flag = flag + 64 + 2097152
where bitand(flag, 64) = 0
/
Rem 
Rem Set the bit indicating that the snapshot has hidden columns for all 
Rem rowid snapshots
Rem
update snap$ set flag = flag + 65536
 where bitand(flag, 16) != 0
   and bitand(flag, 65536) = 0
/
COMMIT
/
---
--- snap_reftime$
---
alter table snap_reftime$ add (
  masflag      number,                     /* additional master information: */
  masobj#      number,                           /* obj# of the master table */
  loadertime   date,          /* last refresh with respect to SQL*Loader log */
  refscn       number,      /* scn of latest info used to refresh this table */
  instsite     integer default 0,                      /* instantiating site */
  lastsuccess  date)                /* time of last known successful refresh */
/
drop index i_snap_reftime1
/
create unique index i_snap_reftime1 on 
  snap_reftime$(vname, sowner, instsite, tablenum)
/
update snap_reftime$
   set masflag = 0, masobj# = 0, refscn = 0;
COMMIT
/
---
--- snap_refop$
---
alter table snap_refop$ add (
  instsite  integer default 0)                         /* instantiating site */
/
drop index i_snap_refop1
/
create unique index i_snap_refop1 on 
  snap_refop$(sowner, vname, instsite, operation#, tabnum)
/
delete from snap_refop$;
update snap$ set status = 0;
COMMIT
/
---
--- snap_colmap$
---
alter table snap_colmap$ add (
  instsite  integer default 0,                         /* instantiating site */
  snapos    integer default 0)          /* position of col in snapshot table */
/
drop index i_snap_colmap1
/
create unique index i_snap_colmap1 on 
  snap_colmap$(sowner, vname, instsite, tabnum, snacol)
/
---
--- rgroup$
---
alter table rgroup$ add (
  instsite          integer default 0,                   /* snapshot site id */
  refresh_sequence  number)                   /* refresh sequence for RepAPI */
/
drop index i_rgroup
/
create unique index i_rgroup on rgroup$ (owner, name, instsite)
/
---
--- rgchild$
---
alter table rgchild$ add (
  instsite  integer default 0)                           /* snapshot site id */
/
drop index i_rgchild
/
create unique index i_rgchild on rgchild$ (owner, name, instsite, type#)
/
---
--- mlog$
---
alter table mlog$ add (
  temp_log varchar2(30))             /* temp table as updatable snapshot log */
/
Rem 
Rem Set the bit indicating that the snapshot log does not log new values and
Rem 
update mlog$  set flag = flag + 32
where bitand(flag, 32) = 0
/
Rem 
Rem Set the temp_log and yscn to their initial default values of NULL and 0
Rem
update mlog$ set temp_log = NULL, yscn = 0   
/
COMMIT
/
REM =========================================================================
REM END Snapshots/Materialized Views
REM =========================================================================

alter table tabpart$ modify (
  ts#         number NULL,     /* tablespace number where partition resides */
  file#       number NULL,                     /* segment header file number */
  block#      number NULL)
/
alter table partobj$ modify (
  defts#      number NULL)
/
create table tabsubpart$ (
  obj#        number not null,              /* object number of subpartition */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */
  dataobj#    number not null,                   /* data layer object number */
  pobj#       number not null,
                       /* object number of partition containing subpartition */
  subpart#    number not null,  
                     /* subpartition number within parent partition (base 1) */
  flags   number not null,
                    /* 0x02 = partition has been analyzed                   */ 
                    /* 0x04 = NO LOGGING for partition                      */
  ts#         number not null,
                             /* tablespace number where subpartition resides */
  file#       number not null,        /* segment header relative file number */
  block#      number not null,                /* segment header block number */
  pctfree$    number not null,   /* minimum free space percentage in a block */
  pctused$    number not null,   /* minimum used spare precentage in a block */
  initrans    number not null,             /* initial number of transactions */
  maxtrans    number not null,             /* maximum number of transactions */
  analyzetime date,                          /* timestamp when last analyzed */
  samplesize  number,                   /* number of rows sampled by analyze */
  rowcnt      number,                                      /* number of rows */
  blkcnt      number,                                    /* number of blocks */
  empcnt      number,                              /* number of empty blocks */
  avgspc      number,                        /* average available free space */
  chncnt      number,                              /* number of chained rows */
  avgrln      number,                                  /* average row length */
  /* These spare columns are for future needs, e.g. values for the 
   * PARALLEL(degree, instances) parameters.
   */
  spare1      number,
  spare2      number,
  spare3      number)
/
create index i_tabsubpart$ on tabsubpart$(pobj#, obj#)
/
create table indsubpart$ (
  obj#          number not null,	    /* object number of subpartition */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE	
   * TRANSACTION DURING TRUNCATE */
  dataobj#      number not null,                 /* data layer object number */
  pobj#	        number not null, 	       /* object number of partition */
  subpart#	number not null, 
	                   /* subpartition # within parent partition(base 1) */
  flags         number not null,      /* 0x01 = Unusable                   */
                            /* 0x02 = subpartition has been analyzed       */ 
                            /* 0x04 = NO LOGGING for subpartition          */
  ts#           number not null,   /* tabspc num where subpartition resides */
  file#         number not null,              /* segment header file number */
  block#        number not null,             /* segment header block number */
  pctfree$      number not null, /* minimum free space percentage in a block */
  initrans      number not null,           /* initial number of transactions */
  maxtrans      number not null,           /* maximum number of transactions */
  analyzetime   date,                        /* timestamp when last analyzed */
  samplesize    number,                          /* samplesize for histogram */
  rowcnt        number,                                    /* number of rows */
  blevel        number,                                      /* B-tree level */
  leafcnt       number,                             /* number of leaf blocks */
  distkey       number,                           /* number of distinct keys */
  lblkkey       number,             /* average number of leaf blocks per key */
  dblkkey       number,             /* average number of data blocks per key */
  clufac        number,                                 /* clustering factor */
  spare1        number,
  	/* These spare columns are for future needs, e.g. values for the
   	 * PARALLEL(degree, instances) parameters.
   	 */
  spare2        number,
  spare3        number)
/
create index i_indsubpart$ on indsubpart$(pobj#, obj#)
/
create table tabcompart$ (
  obj#        number not null,                 /* object number of partition */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */
  dataobj#    number not null,                   /* data layer object number */
  bo#         number not null,                /* object number of base table */
  part#       number not null,    /* partition number (see discussion below) */
  hiboundlen  number not null,      /* length of high bound value expression */
  hiboundval  long,                   /* text of high-bound value expression */
  subpartcnt  number not null,                    /* number of subpartitions */
  flags       number not null,                              /* for any flags */
                                  /* 0x01 = 7.3 -> 8.0 data object migration */
                                  /* 0x02 = partition has been analyzed      */
                                  /* 0x04 = NO LOGGING for partition         */
  defts#      number,                                  /* default TABLESPACE */
  defpctfree  number not null,                            /* default PCTFREE */
  defpctused  number not null,                            /* default PCTUSED */
  definitrans number not null,                           /* default INITRANS */
  defmaxtrans number not null,                           /* default MAXTRANS */
  definiexts  number,                         /* default INITIAL extent size */
  defextsize  number,                            /* default NEXT extent size */
  defminexts  number,                                  /* default MINEXTENTS */
  defmaxexts  number,                                  /* default MAXEXTENTS */
  defextpct   number,                                 /* default PCTINCREASE */
  deflists    number,                    /* default FREELISTS value */
  defgroups   number,                    /* default FREELIST GROUPS */
  deflogging  number,                          /* default LOGGING attribute: */
			      /* 0 - unspecified; 1 - LOGGING; 2 - NOLOGGING */  defbufpool  number,                           /* default BUFFER_POOL value */
  analyzetime date,                          /* timestamp when last analyzed */
  samplesize  number,                            /* samplesize for histogram */
  rowcnt      number,                                      /* number of rows */
  blkcnt      number,                                    /* number of blocks */
  empcnt      number,                              /* number of empty blocks */
  avgspc      number,                        /* average available free space */
  chncnt      number,                              /* number of chained rows */
  avgrln      number,                                  /* average row length */
  /* These spare columns are for future needs, e.g. values for the 
   * PARALLEL(degree, instances) parameters.
   */
  spare1      number,                       
  spare2      number,                       
  spare3      number)
/
create unique index i_tabcompart$ on tabcompart$(obj#)
/
create table indcompart$ (
  obj#        number not null,                 /* object number of partition */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */
  dataobj#    number not null,                   /* data layer object number */
  bo#         number not null,                /* object number of base index */
  part#       number not null,                           /* partition number */
  hiboundlen  number not null,      /* length of high bound value expression */
  hiboundval  long,                   /* text of high-bound value expression */
  subpartcnt  number not null,                    /* number of subpartitions */
  flags       number not null,                              /* for any flags */
                                  /* 0x01 = Unusable                         */
                                  /* 0x02 = partition has been analyzed      */
                                  /* 0x04 = NO LOGGING for partition         */
  defts#      number,                                  /* default TABLESPACE */
  defpctfree  number not null,                            /* default PCTFREE */
  definitrans number not null,                           /* default INITRANS */
  defmaxtrans number not null,                           /* default MAXTRANS */
  definiexts  number,                         /* default INITIAL extent size */
  defextsize  number,                            /* default NEXT extent size */
  defminexts  number,                                  /* default MINEXTENTS */
  defmaxexts  number,                                  /* default MAXEXTENTS */
  defextpct   number,                                 /* default PCTINCREASE */
  deflists    number,                    /* default FREELISTS value */
  defgroups   number,  /* default FREELIST GROUPS (N/A for indexes) */
  deflogging  number,                          /* default LOGGING attribute: */
			      /* 0 - unspecified; 1 - LOGGING; 2 - NOLOGGING */
  defbufpool  number,                           /* default BUFFER_POOL value */
  analyzetime date,                          /* timestamp when last analyzed */
  samplesize  number,                            /* samplesize for histogram */
  rowcnt      number,                                      /* number of rows */
  blevel      number,                                        /* B-tree level */
  leafcnt     number,                               /* number of leaf blocks */
  distkey     number,                             /* number of distinct keys */
  lblkkey     number,               /* average number of leaf blocks per key */
  dblkkey     number,               /* average number of data blocks per key */
  clufac      number,                                   /* clustering factor */
  spare1      number,
  /* These spare columns are for future needs, e.g. values for the
   * PARALLEL(degree, instances) parameters.
   */
  spare2   number,
  spare3   number)
/
create unique index i_indcompart$ on indcompart$(obj#)
/
create table indtypes$
( obj#          number not null,                           /* indextype obj# */
  implobj#      number not null,        /* implementation type object number */
  property      number not null                            /* property flags */
)
/
create table indop$
( obj#          number not null,                         /* indextype object */
  oper#         number not null,          /* obj# for operator schema object */
  bind#         number not null,                         /* operator binding */
  property      number not null,                           /* property flags */
                                 /* 0x01 = INEXACT match ... look for filter */
  filt_nam      varchar2(30),           /* name of the filter function */
  filt_sch      varchar2(30),         /* schema of the filter function */
  filt_typ      varchar2(30) /* type or package of the filter function */ 
)
/
create table javasnm$
( short         varchar2(30) not null,       /* shortened form of name */
  longname      long raw not null                 /* long form of name */
)
/
create unique index i_javasnm1 on javasnm$ (short)
/                       

Rem =========================================================================
Rem type$ column addition
Rem  	NOTE: if db was migrated from o7 to o8.1 using migration utility 
Rem	  	this column will already be in the dictionary
Rem =========================================================================
alter table type$ add(  
  supertoid raw(16))
/

Rem =========================================================================
Rem mlslabel changes
Rem =========================================================================
alter table job$ modify (
  cur_ses_label varchar2(4),
  clearance_hi varchar2(4),
  clearance_lo varchar2(4))
/
drop table lab$
/

Rem =========================================================================
Rem update these new spare values 
Rem =========================================================================

update partobj$ set spare2=0
/
update tab$ set spare3=0
/
update ts$ set spare1=0, spare2=0
/
update icol$ set spare1=0
/
update col$ set spare1=0, spare2=0
/
update ind$ set spare1=intcols
/

Rem =========================================================================
Rem Update flags for all varray and ADT columns that are not virtual columns.
Rem =========================================================================

update coltype$ ct
set ct.flags = ct.flags + 128
where exists
   (select c.intcol# from col$ c
    where c.obj# = ct.obj# and c.intcol# = ct.intcol# and
       (c.type# = 123 or /* Varray column */
        (c.type# = 121 and bitand(c.property, 8) != 8)))
    and bitand(ct.flags, 128) != 128;

Rem =========================================================================
Rem Update the flags for nested table columns that will ultimately involve
Rem using the old image format.
Rem =========================================================================

update coltype$ ct
set ct.flags = ct.flags + 128
where bitand(ct.flags, 4) = 4 and
   exists
   (select *
    from   ntab$ n, coltype$ ct2
    where  n.ntab# = ct2.obj# and
           n.obj# = ct.obj# and
           n.intcol# = ct.intcol# and
           bitand(ct2.flags, 128) = 128)
    and bitand(ct.flags, 128) != 128;

Rem =========================================================================
Rem New export tables for procedural object and action support
Rem =========================================================================

create table exppkgobj$    /* Register procedural object sup pkgs for export */
(
  package     varchar2(30) not null,                         /* package name */
  schema      varchar2(30) not null,                       /* package schema */
  class       number not null,      /* 1=system, 2=schema, 3=object instance */
  type#       number not null,           /* object type of instances in obj$ */
  prepost     number,     /* 0=export prior to parent schema, 1=after parent */
  level#      number default 1000 not null
        /* Determines ordering in dump file of objects exported at same time */
)
/
create unique index i_objtype on exppkgobj$(type#)
/
create table exppkgact$    /* Register procedural action sup pkgs for export */
(
  package     varchar2(30) not null,                         /* package name */
  schema      varchar2(30) not null,                       /* package schema */
  class       number not null,      /* 1=system, 2=schema, 3=object instance */
  level#      number default 1000 not null
        /* Determines ordering in dump file of actions exported at same time */
)
/
create unique index i_actpackage on exppkgact$(package, schema, class)
/
create table expdepobj$  /* Register procedural object dependents for export */
(
  d_obj#      number not null,              /* dependent procedural object # */
  p_obj#      number not null                             /* parent object # */
)
/
create unique index i_dependobj on expdepobj$(d_obj#)
/
create table expdepact$     /* Register objects that have associated actions */
(
  obj#        number not null,                                   /* object # */
  package     varchar2(30),          /* package containing instance_info_exp */
  schema      varchar2(30)                                 /* package schema */
)
/
create unique index i_actobj on expdepact$(obj#, package, schema)
/

Rem
Rem =========================================================================
Rem AQ related upgrade
Rem =========================================================================
 
-- drop the affinity table if it existed
 
DROP TABLE sys.aq$_queue_table_affinities
/
 
-- contains information about queue table affinities (for OPS)
--
CREATE TABLE sys.aq$_queue_table_affinities(
       table_objno        NUMBER   NOT NULL,  -- table object number
       primary_instance   NUMBER   NOT NULL,  --  primary owner instance-id
       secondary_instance NUMBER   NOT NULL,  --  secondary owner instance-id
       owner_instance     NUMBER   NOT NULL,  --  current owner instance-id
CONSTRAINT aq$_qtable_affinities_pk PRIMARY KEY (table_objno))
/
 
-- For every queue table, create an entry in the affinity table */
 INSERT INTO  sys.aq$_queue_table_affinities
          (table_objno, primary_instance, secondary_instance, owner_instance)
   SELECT objno, 0, 0, 0
     FROM system.aq$_queue_tables
/
 
-- Update the owner instance of the tables to the current instance
  UPDATE sys.aq$_queue_table_affinities
    SET owner_instance = (SELECT instance_number from v$instance);

COMMIT
/

-- Reorder the unique constraint index on system.aq$_queues
ALTER TABLE system.aq$_queues
  DROP CONSTRAINT aq$_queues_check
/

ALTER TABLE system.aq$_queues
  ADD CONSTRAINT aq$_queues_check UNIQUE(name, table_objno)
/ 
Rem =========================================================================
Rem begin temporary tables related upgrade:
Rem Change all 8.0.3 temporary tables to permanent and then drop them
Rem Recreate ATEMPTAB$ and ATEMPIND$ in 8.1
Rem =========================================================================

update obj$ set flags = flags-2 where bitand(flags, 2) = 2 and 
	name='ATEMPTAB$' or name='ATEMPIND$'
/
alter system flush shared_pool
/
drop table atemptab$
/
create global temporary table atemptab$                     /* see atempind$ */
(
  id    number
)
/
comment on table ATEMPTAB$ is
'Temporary table whose definition is used internally.  Never contains data'
/
create index atempind$ on atemptab$(id)
/             /* indexes backing up workspaces on disk claim to be atempind$ */
 
Rem =========================================================================
Rem end temporary tables related upgrade
Rem =========================================================================

Rem =========================================================================
Rem remove obsolete fixed view information
Rem =========================================================================
delete from dependency$ where d_obj# in (select obj# from obj$ where name in 
    ('V_$CURRENT_BUCKET', 'V_$RECENT_BUCKET', 'GV_$CURRENT_BUCKET', 
     'GV_$RECENT_BUCKET'));
commit;
alter system flush shared_pool;

drop view v_$current_bucket;
drop view v_$recent_bucket;
drop view gv_$current_bucket;
drop view gv_$recent_bucket;
drop public synonym v$current_bucket;
drop public synonym v$recent_bucket;
drop public synonym gv$current_bucket;
drop public synonym gv$recent_bucket;



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

Rem =========================================================================
Rem BEGIN STAGE 2: upgrade 8.1.3 to 8.1.4
Rem    use to be c0801030.sql
Rem =========================================================================

Rem    rshaikh     06/04/99 - bug 902213: mv trigger and related trig tables
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 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#)
/

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 2: upgrade from 8.1.3 to 8.1.4
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 3: upgrade 8.1.4 to 8.1.5
Rem    use to be c0801040.sql
Rem =========================================================================
Rem    rshaikh     02/12/99 - call 816 script
Rem    rshaikh     01/21/99 - add longdbcs to javasnm
Rem    jiyang      11/16/98 - delete the NLS_DUAL_CURRENCY inserted by migratio
Rem    jiyang      11/12/98 - upgrade NLS_UNION_CURRENCY to NLS_DUAL_CURRENCY
Rem    rshaikh     11/17/98 - change rewrite=>query rewrite
Rem				(for mziauddi)
Rem    masubram    11/07/98 - entries for primary key snapshots in mlog_refcol
Rem                           and add two columns to snap_reftime$
Rem    rshaikh     11/09/98 - add ADMINISTER DATABASE TRIGGER
Rem    wesmith     11/05/98 - Add additional comments                          
Rem    wesmith     11/03/98 - Add new columns in system.repcat$_template_sites 
Rem                           and system.def$_pushed_transactions
Rem    sbasu       10/14/98 -  replace 2147483647 (used to represent default
Rem                           values) with NULL for PARTOBJ$.[DEFTINIEXTS|
Rem                           DEFTEXTSIZE|DEFMINEXTS|DEFMAXEXTS|DEFEXTPCT]
Rem    atsukerm    10/12/98 - adjust property flag for triggers on IOTs.
Rem    rshaikh     10/05/98 - create
Rem    rshaikh     10/05/98 - Created
Rem

REM Add upgrade here!!!!

alter table javasnm$ add (longdbcs varchar2(4000));


update SYSTEM_PRIVILEGE_MAP set 
        name='QUERY REWRITE' where name='REWRITE';

update SYSTEM_PRIVILEGE_MAP set 
        name='GLOBAL QUERY REWRITE' where name=' GLOBAL REWRITE';

update STMT_AUDIT_OPTION_MAP set 
        name='QUERY REWRITE' where name='REWRITE';

update STMT_AUDIT_OPTION_MAP set 
        name='GLOBAL QUERY REWRITE' where name='GLOBAL REWRITE';

Rem Add new columns to sys.snap_reftime$
ALTER TABLE sys.snap_reftime$ ADD
(fcmaskvec RAW(255),
 ejmaskvec RAW(255))
/

Rem Add primary key columns to sys.mlog_refcol$ and update the flags of the 
Rem existing filter columns
DECLARE
 constno NUMBER;
 objno   NUMBER;
 CURSOR pksnaps IS
   SELECT u.user#, m.mowner, m.master, m.oldest_pk 
     FROM sys.mlog$ m, sys.user$ u
    WHERE bitand(m.flag, 2) = 2
      AND u.name = m.mowner;
 CURSOR pkcols(tabobjno NUMBER, constno NUMBER) IS
   SELECT c.name
     FROM sys.col$ c 
    WHERE c.obj# = tabobjno
     AND c.intcol# IN (SELECT cc.intcol# FROM sys.ccol$ cc 
                        WHERE cc.con# = constno
                          AND cc.obj# = c.obj#) 
 ORDER BY c.intcol# desc;
BEGIN
 -- Update the flags of the filter columns to KKZLRCFCCOL (1)
 UPDATE sys.mlog_refcol$ SET flag = 1 
 WHERE nvl(flag, 0) = 0;

 -- Add primary key columns for each of the primary key snapshot logs
 FOR rec IN pksnaps LOOP
    -- Get the constraint number of the primary key
    SELECT cd.con#, cd.obj# INTO constno, objno FROM cdef$ cd 
    WHERE cd.type# = 2 
      AND cd.obj# IN (SELECT o.obj# FROM sys.obj$ o 
                      WHERE o.owner# = rec.user#
                        AND o.name = rec.master
                        AND type#=2);
    -- For each pk col, insert a new entry in mlog_refcol$. If an entry 
    -- already exists then set the flag for that entry to KKZLRCPKCOL (2)
    FOR col IN pkcols(objno, constno) LOOP
      BEGIN
        INSERT INTO sys.mlog_refcol$(mowner, master, colname, oldest, flag)
        VALUES (rec.mowner, rec.master, col.name, rec.oldest_pk, 2);
      EXCEPTION WHEN dup_val_on_index THEN
        UPDATE sys.mlog_refcol$ m SET flag = 2
        WHERE m.mowner = rec.mowner
          AND m.master = rec.master
          AND m.colname = col.name;
      END;
     END LOOP;
     COMMIT;
 END LOOP;
END;
/
COMMIT
/

Rem
Rem system.repcat$_template_sites
Rem
Rem Note: Assumes that Advanced Replication option is installed
Rem

Rem Add new columns for system.repcat$_template_sites
alter table system.repcat$_template_sites
add refresh_template_name varchar2(30);    

alter table system.repcat$_template_sites
add refresh_group_name varchar2(30);

alter table system.repcat$_template_sites
add template_owner varchar2(30);

alter table system.repcat$_template_sites
add repapi_site_id number;

alter table system.repcat$_template_sites
add user_name varchar2(30);               

Rem drop the constraints we created
alter table system.repcat$_template_sites
drop constraint REPCAT$_TEMPLATE_SITES_U1;

alter table system.repcat$_template_sites
drop constraint REPCAT$_TEMPLATE_SITES_FK1;

Rem disable the not null constraints
alter table system.repcat$_template_sites
modify refresh_template_id null;

alter table system.repcat$_template_sites
modify user_id null;

alter table system.repcat$_template_sites
modify site_name null;

Rem populate the table
update system.repcat$_template_sites ts
set refresh_template_name = 
  (select rt.refresh_template_name
   from system.repcat$_refresh_templates rt
   where rt.refresh_template_id = ts.refresh_template_id),
refresh_group_name = 
  (select rt.refresh_group_name
   from system.repcat$_refresh_templates rt
   where rt.refresh_template_id = ts.refresh_template_id),
template_owner = 
  (select rt.owner 
   from system.repcat$_refresh_templates rt
   where rt.refresh_template_id = ts.refresh_template_id),
user_name = 
  (select u.username
   from sys.dba_users u
   where ts.user_id = u.user_id),
repapi_site_id = 
  (select s.site_id
   from sys.snap_site$ s
   where s.site_name = ts.site_name);

Rem finally, null out the site_name if repapi_site_id is not null
update system.repcat$_template_sites ts
set ts.site_name = NULL
where ts.repapi_site_id is not null;
  
Rem set up the new constraints
alter table system.repcat$_template_sites
modify "REFRESH_TEMPLATE_NAME" not NULL;

alter table system.repcat$_template_sites
modify "USER_NAME" not null;

alter table system.repcat$_template_sites
add constraint repcat$_template_sites_c2 check
((site_name is not null and repapi_site_id is null) or 
   (site_name is null and repapi_site_id is not null));

alter table system.repcat$_template_sites
add constraint repcat$_template_sites_u1 unique
(refresh_template_name,user_name,site_name,repapi_site_id);

Rem
Rem END system.repcat$_template_sites
Rem

Rem
Rem system.def$_pushed_transactions
Rem
Rem Note: Assumes that Procedural option is installed
Rem

Rem Add new columns for system.def$_pushed_transactions
alter table system.def$_pushed_transactions
add source_site_id  NUMBER;    

Rem drop the constraints we created
alter table system.def$_pushed_transactions
drop constraint def$_pushed_tran_primary;

Rem populate the table
update system.def$_pushed_transactions pt
set source_site_id = 
 (select s.site_id
  from sys.snap_site$ s
  where s.site_name = pt.source_site);

Rem set up the new constraints
alter table system.def$_pushed_transactions
add constraint def$_pushed_tran_primary
PRIMARY KEY(source_site_id);

Rem
Rem END system.def$_pushed_transactions
Rem


Rem ===================================================================
Rem For partitioned tables/indexes, if no default table/index level 
Rem storage attr.
Rem were specified then we were storing 2147483647 in PARTOBJ$.[DEFTINIEXTS|
Rem DEFTEXTSIZE|DEFMINEXTS|DEFMAXEXTS|DEFEXTPCT] instead on NULL's
Rem ===================================================================
alter table partobj$ modify (deftiniexts number null,
                             defextsize  number null,
                             defminexts  number null,
                             defmaxexts  number null,
                             defextpct   number null )
/

update partobj$ set deftiniexts = null where deftiniexts = 2147483647
/

update partobj$ set defextsize = null where defextsize = 2147483647
/

update partobj$ set defminexts = null where defminexts = 2147483647
/

update partobj$ set defmaxexts = null where defmaxexts = 2147483647
/

update partobj$ set defextpct = null where defextpct = 2147483647
/

commit
/
Rem Adjust property flag for triggers on IOTs.
update trigger$ set property=property+64 where baseobject in
 (select obj# from tab$ where bitand(property,64) <> 0);
/
Rem Delete NLS_DUAL_CURRENCY first that may be inserted the migration
Rem utility, to keep the NLS_UNION_CURRENCY setting
delete from sys.props$ where name='NLS_DUAL_CURRENCY';

Rem Upgrade the NLS_UNION_CURRENCY to NLS_DUAL_CURRENCY
update sys.props$
 set name='NLS_DUAL_CURRENCY', comment$='Dual currency symbol'
 where name='NLS_UNION_CURRENCY';

commit;

insert into SYSTEM_PRIVILEGE_MAP values 
	(-228, 'ADMINISTER DATABASE TRIGGER', 0) 
/ 
commit;

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

Rem =========================================================================
Rem BEGIN STAGE 4: upgrade to new release
Rem =========================================================================

@@c0801050

Rem =========================================================================
Rem END STAGE 4: upgrade to new release
Rem =========================================================================

Rem *************************************************************************
Rem END c0800050.sql
Rem *************************************************************************

