Rem $Header: rdbms/admin/i1101000.sql /main/7 2009/01/22 08:47:55 hongyang Exp $
Rem
Rem i1101000.sql
Rem
Rem Copyright (c) 2007, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      i1101000.sql - load 11.1 specific tables that are need to
Rem                     process basic DDL statements
Rem
Rem    DESCRIPTION
Rem      This script MUST be one of the first things called from the 
Rem      top-level upgrade script.
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 server code may depend on new tables.
Rem      If these tables do not exist, a recursive sql error will occur,
Rem      causing the command to be aborted.
Rem
Rem      The upgrade is performed in the following stages:
Rem        STAGE 1: upgrade from 11.1 to the current release
Rem        STAGE 2: invoke script for subsequent release
Rem
Rem    NOTES
Rem      * This script must be run using SQL*PLUS.
Rem      * You must be connected AS SYSDBA to run this script.
Rem 
Rem    MODIFIED   (MM/DD/YY)
Rem    hongyang    01/12/09 - remove obsolete dependencie
Rem    rsamuels    12/16/08 - OLAP API new columns & renamed columns
Rem    rsamuels    11/21/08 - Add olap_multi_options$
Rem    nmacnaug    10/06/08 - remove hash locking
Rem    huagli      04/15/08 - lrg 3369670: move MV related upgrade script
Rem                           from c1101000.sql to this script
Rem    achoi       03/14/08 - remove versionable bit from public in user
Rem    geadon      03/04/08 - bug 5373923: TRANSIENT_IOT$
Rem    rburns      08/20/07 - created for 11.1 upgrade
Rem

Rem =========================================================================
Rem BEGIN STAGE 1: upgrade from 11.1 to the current release
Rem =========================================================================
update user$ set spare1=spare1-16 
   where bitand(spare1, 16) = 16 and name='PUBLIC'
/
commit
/

Rem transient_iot$ is used to track transient IOTs created during partition
Rem maintenance operations (PMOs) on IOTs (bug #5373923)
create table transient_iot$
( obj#            number not null,              /* obj# of the transient IOT */
  parent_obj#     number,                  /* IOT object targeted by the PMO */
  parent_ptn_obj# number             /* partition object targeted by the PMO */
)
tablespace system
/

Rem remove obsolete dependencies before dropping any fixed views
delete from dependency$ where d_obj# in (select obj# from obj$ where name in
  ('V_$STANDBY_APPLY_SNAPSHOT', 'GV_$STANDBY_APPLY_SNAPSHOT')); 

delete from dependency$ where p_obj# in (4294951141,4294951142);

Rem =================================
Rem  Begin Materialized View changes
Rem =================================

-- add new columns to MV log DD
alter table sys.mlog$ add (
  purge_start       date,                                /* purge start date */
  purge_next        varchar2(200),             /* purge next date expression */
  purge_job         varchar2(30),                          /* purge job name */
  last_purge_date   date,                                 /* last purge date */
  last_purge_status number,    /* last purge status: error# or 0 for success */
  rows_purged       number,                     /* last purge: # rows purged */
  oscn_pk           number,                    /* oldest SCN of primary key  */
  oscn_seq          number,                     /* oldest SCN of sequence no */
  oscn_oid          number,                       /* oldest SCN of object ID */
  oscn_new          number                       /* oldest SCN of new values */
);

-- add new column to MV DD
alter table sys.snap$ add (flag3 number);

-- add new column to MV direct loader log
alter table sys.sumdelta$ add (xid number);

-- add new clumn to MV PMOP log
alter table sys.sumpartlog$ add (xid number, cscn number);

-- add new column to MV direct loader log DD
alter table sys.snap_logdep$ add (rscn number);

-- create a new dictionary table snap_xcmt$
create table snap_xcmt$ /* xid and commit_scn mapping table */
( xid          number not null, /* transaction id */
  commit_scn   number not null  /* commit SCn */
);

-- add new column to MV log filter column DD
alter table sys.mlog_refcol$ add (oldest_scn number);

-- add new column to MV direct loader log DD
alter table sys.snap_loadertime$ add (oldest_scn number);

Rem ===============================
Rem  End Materialized View changes
Rem ===============================


Rem ========================
Rem  Begin OLAP API changes
Rem ========================

alter table olap_aw_deployment_controls$ modify (
  physical_name varchar2(64)            /* name of physical aw object */
) add (
  spare5 varchar2(1000)
)
/

alter table olap_impl_options$ modify (
  option_value varchar2(200)
) add (
  spare5 varchar2(1000)
)
/

create table olap_multi_options$
(
 owning_objectid number not null,       /* owning object ID */
 object_type number not null,           /* object type */
 option_type number not null,           /* option type enum */
 option_order number(20,0) not null,    /* order of this value in the option */
 option_value varchar2(80),             /* option value */
 option_num_value number,               /* option num value */
 option_ref_obj_type number,   /* if option_num_value represents an object, 
                                  the type of object */
 spare1 number,
 spare2 number,
 spare3 varchar2(1000),
 spare4 varchar2(1000)
)
/

create unique index i_olap_multi_options on olap_multi_options$ 
  (owning_objectid, object_type, option_type, option_order)
/

Rem ======================
Rem  End OLAP API changes
Rem ======================


Rem =========================================================================
Rem BEGIN STAGE 2: invoke script for subsequent release
Rem =========================================================================

Rem uncomment the following line for next release to call subsequent i script
Rem @@ixxxxxxx.sql

Rem =========================================================================
Rem END STAGE 2: invoke script for subsequent release
Rem =========================================================================

Rem *************************************************************************
Rem END i1101000.sql
Rem *************************************************************************