Rem
Rem utlip.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem   NAME
Rem     utlip.sql - UTiLity script to Invalidate & Recompile Pl/sql modules
Rem
Rem   DESCRIPTION
Rem
Rem     This script can be used to invalidate all existing PL/SQL modules
Rem     (procedures, functions, packages, types, triggers, views) in a
Rem     database so that they will be forced to be recompiled later on
Rem     either automatically or deliberately.
Rem
Rem     This script must be run when it is necessary to regenerate the
Rem     compiled code because an action was taken that caused the old code's
Rem     format to be inconsistent with what it's supposed to be, e.g., when
Rem     migrating a 32 bit database to a 64 bit database or vice-versa.
Rem
Rem     STEPS:
Rem
Rem     (I)   Invalidate all stored PL/SQL units (procedures, functions,
Rem           packages, types, triggers, views).
Rem
Rem     (II)  Reload PL/SQL package STANDARD and package DBMS_STANDARD.
Rem     
Rem     (III) Recreate all libraries
Rem 
Rem   NOTES
Rem
Rem     * Must be connected as internal to run this.
Rem
Rem     * The scripts expects the following files to be available in the
Rem       current directory:
Rem         standard.sql
Rem         dbmsstdx.sql
Rem         dbmssql.sql
Rem         prvtssql.plb
Rem         prvtsql.plb
Rem 
Rem     * There should be no other DDL on the database while running the
Rem       script. Not following this recommendation may lead to deadlocks.
Rem
Rem   MODIFIED   (MM/DD/YY)
Rem    rshaikh     09/22/99 - quote library names
Rem    mjungerm    06/15/99 - add java shared data object type
Rem    rshaikh     02/12/99 - dont delete java idl objects
Rem    rshaikh     11/17/98 - remove obsolete comments
Rem    rshaikh     10/30/98 - add slash after last truncate stmt
Rem    abrik       10/01/98 - just truncate idl_*$ tables
Rem    rshaikh     10/14/98 - bug 491101: recreate libraries
Rem    ncramesh    08/04/98 - change for sqlplus
Rem    rshaikh     07/20/98 - add commits
Rem    usundara    06/03/98 - merge from 8.0.5
Rem    usundara    04/29/98 - creation (split from utlirp)
Rem                           Kannan Muthukkaruppan (kmuthukk) was the original
Rem                           author of this script.

Rem ===========================================================================
Rem BEGIN utlip.sql
Rem ===========================================================================

-- Step (I)
--
-- First we invalidate all stored PL/SQL units (procs, fns, pkgs,
-- types, triggers.)
--
--   The type# in the update statement below indicates the KGL
--   type of the object. They have the following interpretation:
--	 4 - views,  
--	 7 - pl/sql stored procedure 
--	 8 - pl/sql stored function, 
--	 9 - pl/sql pkg spec 
--	11 - pl/sql pkg body 
--	12 - triggers, 
--	13 - type spec 
--	14 - type body 
-- 
--   Note: for migration to 8.0.4S we don't need to invalidate
--   views. We only need to invalidate stored PL/SQL modules,
--   as these have a platform dependent format.
--

update obj$ set status = 6 
        where type# in (7, 8, 9, 11, 12, 13, 14) 
        and status not in (5,6) 
        and linkname is null 
        and (oid$ is null or oid$ not in (select toid from type$ 
             where bitand(properties, 16) = 16)) 
/ 
commit 
/ 
alter system flush shared_pool 
/ 

--
-- Remove IDL by truncating tables, with the following exceptions:
-- o Java (type 28,29,30,56) - only have bytecodes; they cannot be recompiled
--

delete from idl_ub1$
    where obj# in (select o.obj# from obj$ o where o.type# not in (28,29,30,56))
/
commit
/
delete from idl_char$
    where obj# in (select o.obj# from obj$ o where o.type# not in (28,29,30,56))
/
commit
/
delete from idl_ub2$
    where obj# in (select o.obj# from obj$ o where o.type# not in (28,29,30,56))
/
commit
/
delete from idl_sb4$
    where obj# in (select o.obj# from obj$ o where o.type# not in (28,29,30,56))
/
commit
/
 

alter system flush shared_pool
/


-- Step (II)
--
-- Recreate package standard and dbms_standard. This is needed to execute
-- anonymous block in Step (III).
@@standard
@@dbmsstdx

REM The changes below complete the fix for bug 491101.  The bug was that
REM there was no way to recreate the diana for a LIBRARY object if the
REM diana is deleted.  The diana for objects is generally deleted during
REM upgrade and downgrade.  The fix for 491101 involves recreating the
REM diana just as we would for tables, without needing to invalidate the
REM LIBRARY object.  Having fixed the bug, we are now in a position to
REM simply truncate the idl tables for all objects including LIBRARY
REM objects.  However, truncating the diana without having the ability to
REM recreate the diana in the release to which the instance was downgraded
REM breaks the downgrade unless the fix is backported to those releases.
REM This would've required customers to first upgrade to a patchset
REM release before upgrading to 8.1.4, so we instead provide the
REM workaround below which essentially does something similar - it
REM recreates the diana for all library objects in the environment of the
REM release to which the user is downgrading (or upgrading), by
REM reconstructing the library statements and running them.  It turns out
REM that the diana for library objects is identical between 8.0 and 8.1
REM releases, so the fix for 491101 is really necessary when we downgrade
REM from some future release which has changes in the diana for library
REM objects.

@@dbmssql
@@prvtssql.plb
@@prvtsql.plb

DECLARE
 obj_number number := 0;

 cursor C1 is select o.obj#, 
     'CREATE OR REPLACE LIBRARY "' || u.name || '"."' || o.name || '" ' ||
       	decode(bitand(l.property,2), 2, 'TRUSTED ', ' ') || 'is ' || 
     	decode(bitand(l.property,1), 1, 'STATIC', '''' ||  l.filespec  || '''')
           from obj$ o, user$ u, library$ l
           where o.obj# > obj_number and 
	   l.obj#=o.obj# and 
           u.user# = o.owner# 
           order by o.obj#;

  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  iterations number;
  loop_count number;
  my_err     number;
BEGIN
 loop_count := 0;
 -- To make sure we eventually stop, pick a max number of iterations
 select count(*) into iterations from library$;


 DDL_CURSOR := dbms_sql.open_cursor;
 OPEN C1;

 LOOP

   BEGIN
     FETCH C1 INTO obj_number, ddl_statement;
     EXIT WHEN C1%NOTFOUND OR loop_count > iterations;
   EXCEPTION
    WHEN OTHERS THEN
      my_err := SQLCODE;
      IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
       CLOSE C1;
       -- Here is why C1 orders by obj#.  When we restart the query, we 
       -- will only find object with obj# greater than the last one tried.
       -- This keeps us from re-trying objects that failed.
       OPEN  C1;
       GOTO continue;
      ELSE
       RAISE;
      END IF;
   END;

   BEGIN
       -- Issue the Alter Statement  (Parse implicitly executes DDLs)
       dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
   EXCEPTION
       WHEN OTHERS THEN
        null; -- ignore, and proceed.
   END;

 <<continue>>
   loop_count := loop_count + 1;
 END LOOP;
 dbms_sql.close_cursor(DDL_CURSOR);
 CLOSE C1;
END;
/



Rem ===========================================================================
Rem END utlip.sql
Rem ===========================================================================
