Rem
Rem $Header: c0703040.sql 10-nov-99.14:30:28 rshaikh Exp $
Rem
Rem c0703040.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      c0703040.sql - additional dictionary upgrade script to be 
Rem			run when migrating a
Rem
Rem    DESCRIPTION
Rem
Rem      Additional upgrade script to be run during the migration of a
Rem      7.3.x database to the new release.
Rem
Rem	 This script is called from u0703040.sql
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 a0703040.sql
Rem	 as catalog.sql and catproc.sql will be run before a0703040.sql
Rem	 is invoked.
Rem
Rem      The upgrade is performed in the following stages:
Rem        STAGE 1: load package STANDARD
Rem        STAGE 2: perform some migration related cleanup
Rem        STAGE 3: additional steps to migrate from 7.3.x to 8.0.2
Rem        STAGE 4: upgrade from 8.0.2 to 8.3
Rem        STAGE 5: upgrade from 8.0.3 to the new release
Rem
Rem    RETURNS
Rem 
Rem    NOTES
Rem      You must connect internal to run this script
Rem  
Rem    MODIFIED   (MM/DD/YY)
Rem     rshaikh   11/10/99 -  add commit
Rem     rshaikh   10/14/98 -  add comments advanced replication
Rem     ncramesh  08/04/98 -  change for sqlplus
Rem     rshaikh   06/17/98 -  move non-anonymous block stmts from
Rem			      u0703040.sql and u0800020.sql here.
Rem     rshaikh   06/08/98 -  remove atemptab$ create since it is done
Rem			      in u0800050.sql also
Rem     usundara  06/03/98 -  rework upgrade/downgrade/migration scripts:
Rem                           * merge from 8.0.5:
Rem                             call u0800020 instead of the old cat8003.sql
Rem                           * change name cat8000.sql->u0703040.sql
Rem                           * copy the contents of the old cat8000s.sql
Rem                             directly into STAGE 6 of this file.
Rem                           * [rshaikh] add 8.1 dict tables
Rem     bgoyal    05/26/98  - make global keyword required                     
Rem     mkrishna  07/18/97 -  bug 506942 - move alter stmt
Rem     rsarwal   05/29/97 -  Remove Echo
Rem     mkrishna  05/27/97 -  Fix # lines
Rem     tlee      05/20/97 -  remove mig_temp_source$ creation
Rem     mkrishna  05/13/97 -  remove updates
Rem     mkrishna  05/02/97 -  Modify test to not invalidate sys types
Rem     mkrishna  05/08/97 -  Invalidate obj status changes
Rem     mkrishna  05/05/97 -  Change status update to 6
Rem     vkarra    04/21/97 -  move changes to cat8003.sql
Rem     mkrishna  04/24/97 -  Add standard package
Rem     rshaikh   03/31/97 -  removal of conflict symbols
Rem     rshaikh   03/19/97 -  fix bug 464891
Rem     mkrishna  03/24/97 -  Fix bug 464891 - drop user migrate removal
Rem     vkarra    03/17/97 -  set default value for partobj$.spare1
Rem     liwong    02/20/97 -  Default last_queue_batch to 0
Rem     rshaikh   01/20/97 -  drop package MIGRATE_STANDARD_THROW_AWAY
Rem     rshaikh   01/06/97 -  drop user migrate
Rem     liwong    01/06/97 -  Modify repcat$_repcatlog_request
Rem     liwong    11/27/96 -  Added replication related upgrade
Rem     rshaikh   09/16/96 -  Creation
Rem

Rem =========================================================================
Rem BEGIN STAGE 1: load package STANDARD
Rem =========================================================================

@@standard

Rem =========================================================================
Rem END STAGE 1: load package STANDARD
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 2: perform some migration related cleanup
Rem =========================================================================

drop package MIGRATE_STANDARD_THROW_AWAY
/

Rem =========================================================================
Rem END STAGE 2: perform some migration related cleanup 
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 3: additional steps to migrate from 7.3.x to 8.0.2
Rem =========================================================================

Rem =========================================================================
Rem replication-related upgrade starts here
Rem =========================================================================
 
-----------------------------------------------------------------------------
-- Note: 
--   1. comments on added columns will be created when catrep.sql is loaded
--
--   2. Precondition: 
--        no deferred transaction, i.e., system.def$_call,
--        system.def$_error and system.def$_calldest must be empty
--        This is ensured by the migration utility
--
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
--   Case: add receiver column to system.def$_error for new security model
--         add origin_tran_db, origin_tran_id for AQ conversion
-----------------------------------------------------------------------------

-- drop constraint which references def$_call
ALTER TABLE system.def$_calldest DROP CONSTRAINT def$_calldest_call
/

-- system.def$_call is replaced by system.def$_aqcall and is obsolete in V8
DROP TABLE system.def$_call
/


ALTER TABLE system.def$_error ADD 
(
  origin_tran_db    VARCHAR2(128), -- node which originated this txn
  origin_tran_id    VARCHAR2(30)   -- tid of the txn which originated this     
)
/

ALTER TABLE system.def$_error ADD 
(
  receiver         NUMBER           -- User ID of the original receiver
)
/

ALTER TABLE system.def$_error 
  DROP CONSTRAINT def$_error_primary
/

ALTER TABLE system.def$_error 
  ADD CONSTRAINT def$_error_primary 
    PRIMARY KEY(origin_tran_db, origin_tran_id)
/

-- add more space to deferred_tran_id in system.def$_calldest
ALTER TABLE system.def$_calldest 
  MODIFY (deferred_tran_id VARCHAR2(30))
/


ALTER TABLE system.def$_error 
  MODIFY (deferred_tran_id VARCHAR2(30))
/


ALTER TABLE system.def$_destination
  MODIFY (last_tran_id VARCHAR2(30))
/


-- add new column last_queue_batch to table system.def$_destination
ALTER TABLE system.def$_destination ADD
(
  last_queue_batch NUMBER
                     DEFAULT 0,
  last_seq         NUMBER    -- last delivered txn seq, 0 on clean termination
)
/


-----------------------------------------------------------------------------
--   Case: modify check reason constraint for replicated LOBs
-- because of adding reason type 9 for LOB package ($RL)
-----------------------------------------------------------------------------
REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_generated
  DROP CONSTRAINT repcat$_generated_obj
/

REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_generated
  ADD CONSTRAINT repcat$_generated_obj
      CHECK (reason IN (0, 1, 2, 3, 4, 5, 6, 7, 9))
                         -- 0 = trigger (Rep2 async propagation) $RT          
                         -- 1 = replication package              $RP
                         -- 2 = resolution package               $RR
                         -- 3 = priority package                 $RV
                         -- 4 = auditing package                 $RA
                         -- 5 = procedural replication wrapper
                         -- 6 = Rep3 trigger package             $TP
                         -- 7 = Rep3 trigger (mixed propagation) $RT
                         -- 9 = LOB package                      $RL
/


-- Add more columns to repcat$_repobject
REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_repobject ADD
(
  genpackage      INTEGER
                      -- this col is set and used at the master def site only
                    CONSTRAINT repcat$_repobject_genpackage
                      CHECK (genpackage IN (0, 1, 2)),
  genplogid       INTEGER,

  gentrigger      INTEGER
                      -- this col is set and used at the master def site only
                    CONSTRAINT repcat$_repobject_gentrigger
                      CHECK (gentrigger IN (0, 1, 2)),
  gentlogid       INTEGER,
  flag raw(4) DEFAULT '00000000'              -- for minimizing communication
)
/

REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
alter table system.repcat$_repcatlog 
  drop constraint repcat$_repcatlog_request
/

-- 12 (end_phase2) was added in V8
REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
alter table system.repcat$_repcatlog
  add constraint repcat$_repcatlog_request
    CHECK (request IN (-1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
/                                                                              

Rem =========================================================================
Rem replication-related upgrade ends here
Rem =========================================================================

Rem =========================================================================
Rem END STAGE 3: additional steps to migrate from 7.3.x to 8.0.2
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 4: upgrade from 8.0.2 to 8.0.3
Rem =========================================================================

Rem The section below used to be in u0800020.sql, but we no longer
Rem support upgrade from u0800020.sql.  Remove all this
Rem =========================================================================
Rem replication-related upgrade starts here
Rem =========================================================================

DROP TRIGGER system.repcatlogtrig
/

REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.def$_temp$lob MODIFY lob (temp$blob) (pctversion 0)
/

REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.def$_temp$lob MODIFY lob (temp$clob) (pctversion 0)
/

REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.def$_temp$lob MODIFY lob (temp$nclob) (pctversion 0)
/

REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE SYSTEM.REPCAT$_REPPROP ADD (recipient_key NUMBER)
/

DROP VIEW SYSTEM.DEF$_CALL 
/

DROP VIEW SYS.DEF$_TRAN 
/ 

DROP SEQUENCE SYSTEM.DEF$_CALL_QBATCH 
/

DROP TABLE SYSTEM.DEF$_ERROR 
/

DROP TABLE SYSTEM.DEF$_LOB 
/

DROP TABLE SYSTEM.DEF$_ORIGIN 
/

DROP TABLE SYSTEM.DEF$_CALLDEST 
/

REM
REM ORA-00942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE SYSTEM.REPCAT$_REPSCHEMA 
  DROP CONSTRAINT REPCAT$_REPSCHEMA_DEST 
/

DROP TABLE SYSTEM.DEF$_DESTINATION 
/

----------
-- the drop phase in migration.
-- drop all views and store the names of the queue tables in  
-- a temporary table- sys.AQ$_MIGRATION
-- the actual "client" queue tables will be dropped when
-- catqueue.sql is loaded.
----------

CREATE TABLE  sys.AQ$_802MIG803(schema varchar2(30), name varchar2(30))
/

INSERT INTO sys.AQ$_802MIG803 
  SELECT schema, name FROM system.aq$_queue_tables
/

commit
/

DELETE FROM sys.expact$
      WHERE EXISTS(SELECT schema, name from system.aq$_queue_tables
	           WHERE schema = sys.expact$.owner 
		   AND name = sys.expact$.name
		   AND obj_type = 2)
/

-- drop the tables and views

drop table system.aq$_queue_tables
/

drop table system.aq$_queue_table_sort
/

drop table system.aq$_queues
/

drop view DBA_QUEUE_TABLES
/

drop view USER_QUEUE_TABLES
/

Drop public synonym USER_QUEUE_TABLES
/

drop view DBA_QUEUES
/

drop view USER_QUEUES
/

drop public synonym USER_QUEUES
/

drop view DBA_QTABLE_SORT
/

drop view USER_QTABLE_SORT
/

drop public synonym USER_QTABLE_SORT
/

drop sequence system.aq$_qidseq
/

drop sequence sys.aq$_chainseq
/

drop library dbms_aq_lib
/

drop package DBMS_AQ
/

drop package DBMS_AQADM
/

-- Delete aq$ tables from noexp$ 
delete from noexp$ where name like 'AQ$_%'
/

commit
/

DROP TABLE SYSTEM.DEF$_CALL 
/
Rem =========================================================================
Rem - nchar related upgrade
Rem =========================================================================

-------------------------------------------------------------------------
---- nchar related upgrade
-------------------------------------------------------------------------

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_priority ADD
(
    nchar_value            nchar(500),
    nvarchar2_value        nvarchar2(1000),
    large_char_value       char(2000)
)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_priority MODIFY
(
    raw_value              raw(2000),
    varchar2_value         varchar2(4000)
)
/

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_priority_group
 DROP CONSTRAINT repcat$_priority_group_c1
/

Rem =========================================================================
Rem replication-related upgrade ends here
Rem =========================================================================

REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_priority_group
 ADD CONSTRAINT repcat$_priority_group_c1
   CHECK (data_type_id in (1, 2, 3, 4, 5, 6, 7))
/
REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_priority_group
 DROP CONSTRAINT repcat$_priority_group_c2
/
REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_priority_group
 ADD CONSTRAINT repcat$_priority_group_c2
   check ((data_type_id in (4, 7) and
           fixed_data_length is not null)
     or   (data_type_id in (1, 2, 3, 5, 6) and
           fixed_data_length is null))
/
REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_audit_attribute
  DROP CONSTRAINT repcat$_audit_attribute_c1
/
REM
REM ORA-000942 expected if Advanced Replication is not installed
REM THESE ARE OK IF ADVANCED REPLICATION IS NOT INSTALLED
REM
ALTER TABLE system.repcat$_audit_attribute
  ADD CONSTRAINT repcat$_audit_attribute_c1
    check ((data_type_id in (2, 4, 5, 6, 7) and
            data_length is not null)
        or (data_type_id not in (2, 4, 5, 6, 7) and
            data_length is null))
/

Rem =========================================================================
Rem - end of nchar related upgrade
Rem =========================================================================
Rem Insert default value (allow_all) into trusted_list$ IF no deny_all or
Rem allow_all was already there.
Rem
INSERT into sys.trusted_list$
SELECT  '+*', '*' from dual d1, dual d2
where not exists(select * from sys.trusted_list$
where dbname like '%*')
/

commit
/

update partobj$ 
  set spare1 = 65535
/
              -- default value for BUFFER_POOL in partobj$
COMMIT
/

Rem =========================================================================
Rem END STAGE 4: upgrade from 8.0.2 to 8.0.3
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 5: upgrade from 8.0.3 to the new release
Rem =========================================================================

Rem Now run c0800030.sql to upgrade from 8.0.3 to the new release.
Rem Note: catalog.sql and catproc.sql will get run when this script is run.
Rem

@@c0800030

Rem =========================================================================
Rem END STAGE 5: upgrade from 8.0.3 to the new release
Rem =========================================================================


Rem *************************************************************************
Rem END c0703040.sql
Rem *************************************************************************
