Rem
Rem $Header: a0703040.sql 06-feb-2000.16:59:28 ppjanic Exp $
Rem
Rem a0703040.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      a0703040.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 anonymous block related changes here. 
Rem	 Any dictionary create, alter, updates and deletes 
Rem 	 that must be performed before catalog.sql and catproc.sql go 
Rem	 in c0703040.sql 
Rem
Rem      The upgrade is performed in the following stages:
Rem        STAGE 1: finish off 7.3.x migration
Rem        STAGE 2: additional snapshot related steps to migrate from 7.3.x
Rem                 to the new release
Rem        STAGE 3: upgrade from 8.0.2 to 8.0.3
Rem        STAGE 4: 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     ppjanic   02/06/00 - 1154181: Remove package/type body from objauth$
Rem     ncramesh  08/04/98 - change for sqlplus
Rem     rshaikh   06/17/98 - move all anonymous block stmts from
Rem			      u0703040.sql and u0800020.sql here
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: perform additional steps to upgrade from 8.0.2 to 8.0.3
Rem =========================================================================

Rem ========================================================================
Rem - now for some remaining steps to finish the migration from 7.x to the
Rem - the new release.
Rem ========================================================================


Rem Now copy the grant permissions on the sys tables in to the objauth$ table
Rem Make sure that no duplicates are inserted -- e.g. tables which were not
Rem dropped during the mig process.
Rem Do not worry about the error message below if u are only doing an upgrade
Rem from 8.0.2. The stmt below is only for migration from Oracle7.

insert into sys.objauth$  
select o.obj#, u1.user#, u2.user#,
       priv.privilege#, priv.sequence#, priv.parent,priv.option$, priv.col#
from   grant_mig_priv$ priv, user$ u1, user$ u2, obj$ o , user$ u3
where  priv.name = o.name
and    priv.owner = u3.name 
and    o.type# <> 10                             /* ignore dropped objects */
and    o.type# <> 11                              /* ignore package bodies */
and    o.type# <> 14                                 /* ignore type bodies */
and    u3.user#   = o.owner#
and    priv.grantorname = u1.name
and    priv.granteename = u2.name
and  not exists ( select * from objauth$ auth where 
      auth.obj# = o.obj# and auth.grantee#  = u2.user# );

commit
/


Rem ========================================================================
Rem End of changes to library stuff! 
Rem ========================================================================

drop user migrate cascade
/
commit
/
Rem =========================================================================
Rem END STAGE 1: perform additional steps to upgrade from 8.0.2 to 8.0.3
Rem =========================================================================

Rem =========================================================================
Rem BEGIN STAGE 2: additional snapshot related steps to migrate from 7.3.x
Rem                to the new release
Rem =========================================================================

Rem ========================================================================
Rem upgrade snapshot-specific packages and views from 7.3.x to the new release
Rem ========================================================================

Rem These changes were pulled in from cat8000s.sql;
Rem Here's the old modification history.
rem
Rem     liwong     05/06/97 -  Update master_version
Rem     liwong     12/24/96 -  Added alter mlog$ tables from cat8000r.sql
Rem     liwong     11/26/96 -  Created 

-------------------------------------------------------------------------
--   Case: drop views mview$_[snapshotname] in snap$.mview$ and
-- set snap$.mview to null because the snapshot definition query is put
-- directly in the FROM clause of the generated query during refresh in V8
-------------------------------------------------------------------------
DECLARE
  sql_cursor     NUMBER;
  rows           NUMBER;
  dummy          NUMBER;

  CURSOR view_snap IS
    SELECT sowner, vname, mview
    FROM sys.snap$;

  -------------------------------------------------------------------------
  -- definition of the auxiliary function
  -------------------------------------------------------------------------
  FUNCTION object_exists(oowner IN VARCHAR2, oname IN VARCHAR2,
                         otype IN VARCHAR2) RETURN BOOLEAN IS
    found    BINARY_INTEGER := 0;
 
  BEGIN
     SELECT 1 INTO found
       FROM sys.dba_objects
         WHERE owner = oowner
           AND object_name = oname
           AND object_type = otype;
     RETURN TRUE;
 
  EXCEPTION
    -- no data found --> object does not exist
    WHEN others THEN RETURN FALSE;
  END;
  -------------------------------------------------------------------------
  -- end of the definition of the auxiliary function
  -------------------------------------------------------------------------

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN view_snap LOOP
    BEGIN
      IF OBJECT_EXISTS(rec.sowner, rec.mview, 'VIEW') THEN
        --- Drop the view
        DBMS_SQL.PARSE(sql_cursor,    
            'DROP VIEW "' || rec.sowner || '"."' || rec.mview || '"',
            DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);
      END IF;

      -- update only if the drop succeeds or object does not exist
      UPDATE sys.snap$ SET mview = NULL
        WHERE sowner = rec.sowner AND vname = rec.vname;
      COMMIT;

    EXCEPTION 
      WHEN OTHERS THEN                 
        NULL;
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor); 
  COMMIT;
END;
/


-------------------------------------------------------------------------
-- create internal triggers for updatable snapshot logs and drop triggers 
-- ustrg$_[snapshotname] in snap$.ustrg
-- also change the underlying uslog$_ table (add one more column)
-------------------------------------------------------------------------
-- Note: 
--   create internal triggers first before dropping V7 triggers

DECLARE
  sql_cursor    NUMBER;
  rows          NUMBER;
  CURSOR snap_trgs IS 
    SELECT vname, sowner, tname, ustrg, uslog
    FROM sys.snap$
    WHERE bitand(flag,2) = 2 ;           -- skip read-only snapshot
 
  -------------------------------------------------------------------------
  -- definition of the auxiliary function
  -------------------------------------------------------------------------
  FUNCTION object_exists(oowner IN VARCHAR2, oname IN VARCHAR2, 
                         otype IN VARCHAR2) RETURN BOOLEAN IS
    found    BINARY_INTEGER := 0;

  BEGIN
     SELECT 1 INTO found
       FROM sys.dba_objects
         WHERE owner = oowner
           AND object_name = oname
           AND object_type = otype;
     RETURN TRUE;

  EXCEPTION
    -- no data found
    WHEN others THEN RETURN FALSE;
  END;
  -------------------------------------------------------------------------
  -- end of the definition of the auxiliary function
  -------------------------------------------------------------------------

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN snap_trgs LOOP
    BEGIN

      BEGIN
        -- alter the underlying uslog$_ tables due to primary key and 
        -- subquery snapshots
        DBMS_SQL.PARSE(sql_cursor, 'ALTER TABLE "' || rec.sowner || '"."' ||
               rec.uslog || '" ADD (OLD_NEW$$ VARCHAR2(1))', DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);

        BEGIN
          -- generate internal trigger for updatable snapshot (snap$_% base
          -- table)
          dbms_snapshot_utl.sync_up_upd(rec.sowner, rec.tname);

        EXCEPTION 
          WHEN others THEN
            DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' ||
                               SQLERRM);
            DBMS_SYSTEM.KSDWRT(2, '  Error in creating internal trigger for "'
                               || rec.sowner || '"."' || rec.tname || '"'); 
        END;
        COMMIT;

      EXCEPTION 
        WHEN others THEN
          DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' ||
                               SQLERRM);
          DBMS_SYSTEM.KSDWRT(2, '  Error in altering table for "'
                                  || rec.sowner || '"."' || rec.uslog || '"'); 
      END;

      -- call auxilliary function
      IF OBJECT_EXISTS(rec.sowner, rec.ustrg, 'TRIGGER') THEN
        -- drop trigger
        DBMS_SQL.PARSE(sql_cursor, 
          'DROP TRIGGER "' || rec.sowner || '"."' || rec.ustrg || '"',
          DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);
      END IF;

      -- set the sys.snap$.trig to NULL if drop succeeds or 
      -- the object does not exist
      UPDATE sys.snap$ SET ustrg = NULL 
        WHERE vname = rec.vname AND sowner = rec.sowner;
      COMMIT;

    EXCEPTION 
      WHEN others THEN
        DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                             SQLERRM);
        DBMS_SYSTEM.KSDWRT(2, '  Error in dropping trigger "' || rec.sowner ||
                              '"."' || rec.ustrg || '"');
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor);
  COMMIT;
END;
/


--   create internal triggers for master snapshot log and drop triggers 
-- tlog$_[master_table] in mlog$.trig
DECLARE
  sql_cursor    NUMBER;
  rows          NUMBER;
  CURSOR master_trgs IS 
    SELECT mowner, master, trig, log
    FROM sys.mlog$;

  -------------------------------------------------------------------------
  -- definition of the auxiliary function
  -------------------------------------------------------------------------
  FUNCTION object_exists(oowner IN VARCHAR2, oname IN VARCHAR2,
                         otype IN VARCHAR2) RETURN BOOLEAN IS
    found    BINARY_INTEGER := 0;
 
  BEGIN
     SELECT 1 INTO found
       FROM sys.dba_objects
         WHERE owner = oowner
           AND object_name = oname
           AND object_type = otype;
     RETURN TRUE;
 
  EXCEPTION
    -- no data found
    WHEN others THEN RETURN FALSE;
  END;
  -------------------------------------------------------------------------
  -- end of the definition of the auxiliary function
  -------------------------------------------------------------------------

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN master_trgs LOOP
    -- Drop the trigger
    BEGIN
      BEGIN
        -- generate internal trigger for master snapshot log
        dbms_snapshot_utl.sync_up_log(rec.mowner, rec.master);

      EXCEPTION
        WHEN others THEN
          DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' ||
                             SQLERRM);
          DBMS_SYSTEM.KSDWRT(2, '  Error in creating internal trigger for "' ||
                                rec.mowner || '"."' || rec.master || '"');
      END;
      COMMIT;

      IF OBJECT_EXISTS(rec.mowner, rec.trig, 'TRIGGER') THEN
        DBMS_SQL.PARSE(sql_cursor, 
          'DROP TRIGGER "' || rec.mowner || '"."' || rec.trig || '"',
          DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);
      END IF;

      -- set the sys.mlog$.trig to NULL
      UPDATE sys.mlog$ SET trig = NULL 
        WHERE master = rec.master AND mowner = rec.mowner;
      COMMIT;

    EXCEPTION 
      WHEN others THEN
        DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                             SQLERRM);
        DBMS_SYSTEM.KSDWRT(2, '  Error in dropping trigger "' || rec.mowner
                              || '"."' || rec.trig || '"');
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor);
  COMMIT;
END;
/


-----------------------------------------------------------------------------
--   Case: truncate and invalidate all master snapshot logs 
-- (mlog$_[master_name]) to comply with new ROWID format in V8, leave
-- uslog$_[snapshotname] untouched, but don't delete entries in sys.mlog$
-- in addition, alter the underlying log table due to primary key/subquery 
-- snapshots
-----------------------------------------------------------------------------

-- Now truncate all mlog$_ tables for master snapshot logs by purging them
-- and alter the underlying log table (add more columns)
-- We do not drop the snapshot log and then recreate them again because
-- we need to specify (preserve) the tablespace and storage parameters
-- in the original snapshot logs
DECLARE
  sql_cursor    NUMBER;
  rows          NUMBER;

  CURSOR mlogs IS 
    SELECT mowner, master, log
    FROM sys.mlog$;

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;
  FOR rec IN mlogs LOOP
    BEGIN
      -- purging 9999 entries in the mlog$ table. This effectively truncates
      -- the table
      DBMS_SNAPSHOT.PURGE_LOG('"' || rec.mowner || '"."' || rec.master || '"',
                               9999);

      -- alter underlying log table structure
      BEGIN                  
        -- add old_new$$ and change_vector$$ (delta_vec$$) columns
        DBMS_SQL.PARSE(sql_cursor, 'ALTER TABLE "' || rec.mowner || '"."' ||
                   rec.log || '" ADD ( old_new$$ varchar2(1), ' || 
                   ' change_vector$$ raw(255))', DBMS_SQL.NATIVE);
        rows := DBMS_SQL.EXECUTE(sql_cursor);

      EXCEPTION
        -- column being added already exists in table
        WHEN others THEN
          NULL;
      END;

    EXCEPTION 
      WHEN others THEN
        DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                               SQLERRM);
        DBMS_SYSTEM.KSDWRT(2, '  Error in purging log for "' || rec.mowner 
                                || '"."' || rec.master || '"');
        COMMIT;
    END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(sql_cursor);
  COMMIT;
END;
/

-------------------------------------------------------------------------
--   Case: set master_version in snap$ and we need to do that for each master
-------------------------------------------------------------------------
--   The following scenarios will be treated as V7: 
--   o. the master site is not available (it may be V8) 
--   o. the database link is not correct
--   o. the remote site is indeed V7
--
-- Note: database links are not upgraded accordingly using migration utility,
--       DBA needs to make sure there are appropriate database links to 
--       V8 remote masters in order to have the masater_version set correctly
--
DECLARE
  CURSOR masters IS
    SELECT DISTINCT mlink
    FROM sys.snap$;

  sql_cursor    NUMBER;
  rows          NUMBER;
  compat        VARCHAR2(40);
  version       VARCHAR2(40);

BEGIN
  sql_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR rec IN masters LOOP
    BEGIN
      -- db_version is a PL/SQL ICD that was added in V8.0, if there is
      -- exception (this includes the site is not V8, or some problem in the 
      -- database link, or the remote site is unavailable), set the version 
      -- to 1 (means V7)
      IF rec.mlink IS NULL THEN
        BEGIN
          UPDATE sys.snap$ SET master_version = 2 WHERE mlink IS NULL;
        EXCEPTION 
          WHEN others THEN
            DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                           SQLERRM);
            DBMS_SYSTEM.KSDWRT(2, '  Error in updating smaster_version for ' ||
                                  'NULL MLINK in SYS.SNAP$');
        END;
      ELSE 
        DBMS_SQL.PARSE(sql_cursor, 
          'BEGIN dbms_utility.db_version' || rec.mlink || '(:b1, :b2); END;',
          DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(sql_cursor, 'b1', compat, 40);
        DBMS_SQL.BIND_VARIABLE(sql_cursor, 'b2', version, 40);
        rows := DBMS_SQL.EXECUTE(sql_cursor);
        UPDATE sys.snap$ SET master_version = 2 WHERE mlink = rec.mlink;
      END IF;
      COMMIT;

    EXCEPTION 
      -- after migrate, the dba may need to change the db links appropriately
      WHEN others THEN                         
        BEGIN
          -- if exception, assume it's V7
          -- DBMS_SYSTEM.KSDWRT(2, '  Warning: ' ||  SQLERRM);
          DBMS_SYSTEM.KSDWRT(2, '  Failed to execute dbms_utility.db_version'
                            || rec.mlink || ', assume master '
                            || substr(rec.mlink, 2, 128)
                            || ' is V7');
          UPDATE sys.snap$ SET master_version = 1
            WHERE mlink = rec.mlink;
          COMMIT;

        EXCEPTION
          WHEN others THEN
            DBMS_SYSTEM.KSDWRT(2, '  Error ' || TO_CHAR(SQLCODE) || ': ' || 
                           SQLERRM);
            DBMS_SYSTEM.KSDWRT(2, '  Failed to update master_version for "' ||
                               substr(rec.mlink, 2, 128) || '" in SYS.SNAP$');
        END;
    END;
 END LOOP;

 DBMS_SQL.CLOSE_CURSOR(sql_cursor);
 COMMIT;
END;
/

Rem =========================================================================
Rem END STAGE 2: additional snapshot related steps to migrate from 7.3.x
Rem              to the new release
Rem =========================================================================

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

/* Invalidate qualifying bitmap indexes */
declare
 in_trailing_null boolean;
 invalidate_indexes boolean;
 /* Loop over all tables that have bitmap indexes defined on them */
 cursor c1 is select distinct bo# from Ind$
     where type#=2 order by bo#;
 /* Loop over all columns of specified table */
 cursor c2 (objno number) is select  c.name,c.type#,c.null$ from col$ c
     where obj# = objno order by col# desc;
begin
 for c1_rec in c1 loop
     invalidate_indexes := FALSE;
     in_trailing_null := TRUE;
     for c2_rec in c2(c1_rec.bo#) loop
        /* If this is a trailing NULL column, skip over it */
        if c2_rec.null$ >0 then
           in_trailing_null := FALSE;
        end if;
        if (not in_trailing_null) and 
           (c2_rec.type# = 96 OR c2_rec.type# =12) theN
           invalidate_indexes := TRUE;
           exit;
        end if;
     end loop;
     if invalidate_indexes then
        /* Set bit 0 in flags - marking the index as being unusable */
        update ind$ set flags=(2 *(floor(flags/2)))+1 where bo# = c1_rec.bo# 
               and type#=2;
     end if; 
 end loop;
 commit; 
end;
/
Rem ========================================================================
Rem Creating the libraries back! 
Rem ========================================================================

declare
 
  cursor get_libnames  is 
     select obj$.obj# ,obj$.name,user$.name from obj$,user$ 
       where obj$.owner# = user$.user# and obj$.type#  = 22 and 
              not exists (select * from source$ where source$.obj# = obj$.obj#);
  cursor  get_type_info(objnum obj$.obj#%TYPE) is
     select decode(bitand(property,1),0,0,1,1,1) STATIC ,decode(bitand(property,
2) ,0,0,1,1,1) TRUSTED ,filespec from library$ where obj# = objnum;
 
  -- objno returned from get_objnum
  objno obj$.obj#%TYPE;
  -- for username and objectname
  objname obj$.name%TYPE;
  username user$.name%TYPE;
  -- txt  source$.source%TYPE;
  text varchar2(32637);
  -- dynamic cursors
  cur1 integer;
  -- the library$ return types
  trusted_flag number;
  static_flag number;
  filename  library$.filespec%TYPE;
 
begin
 
  open get_libnames;
  cur1 := dbms_sql.open_cursor;
  loop
 
    fetch get_libnames into objno,objname,username;
    exit when get_libnames%NOTFOUND ; 
    
    open get_type_info(objno);
 
    fetch get_type_info into static_flag,trusted_flag ,filename;
    if get_type_info%NOTFOUND then -- This should not occur
      goto ignore;
    end if;
    
    text := 'CREATE OR REPLACE LIBRARY '|| username || '.' || objname ;
    if trusted_flag = 1 then
       text := text || ' TRUSTED ' ;
    end if;
    text := text || ' IS ' ;
    if static_flag = 1  then
       text := text || ' STATIC;';
    else
       text := text || ' ''' || filename || '''; ' ;
    end if;
 
    begin
      dbms_sql.parse (cur1,text,dbms_sql.NATIVE);
    exception
      when others then
        NULL;
    end;
 
<<ignore>>
    close get_type_info;
  end loop;
    close get_libnames;
  dbms_sql.close_cursor(cur1);
 
 commit;
 
end;
/
 
Rem =========================================================================
Rem END STAGE 3: perform additional steps to upgrade from 8.0.2 to 8.0.3
Rem =========================================================================

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

@@a0800030

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


Rem *************************************************************************
Rem END a0703040.sql
Rem *************************************************************************
