RE: batch recompile invalid packages

From: Muthu Avudaiyappan <MAvudaiyappan_at_Vertek.com>
Date: Tue, 9 Jan 2001 10:27:01 -0500
Message-Id: <10736.126145@fatcity.com>

Hi John Dunn ,

Hope this helps...

This script automatically recompiles all objects in the database that have a   status of 'INVALID'. Because PL/SQL objects can have interdependencies, it is
  important to compile them in reverse order of dependency so that only one pass
  is needed.

  Requirements:

---

  DBA role

  Script:

---

 

-----------cut-----------cut--------------cut----------------cut-----------

  rem *********************************************************
  rem * file:    comp_all.sql
  rem * purpose: compile all database stored objects
  rem * to use:  log in using the appropriate account then
  rem *          execute this script using the following syntax:
  rem *
  rem *            SQL> @comp_all
  rem *
  rem * NOTE:    You should not have to run this script more
  rem *          than once since it uses
  rem *          order_object_by_dependency table to compile
  rem *          objects in the proper order.  Any
  rem *          compilation errors generated should be
  rem *          investigated.
  rem *********************************************************

  set heading off
  set pagesize 0
  set linesize 79
  set verify off
  set echo off

  spool comp_all.sql

  select

      decode( OBJECT_TYPE, 'PACKAGE BODY',


      'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',

      'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || '

compile;' )
  from

      dba_objects a,
      sys.order_object_by_dependency b
  where
      A.OBJECT_ID = B.OBJECT_ID(+) and
      STATUS = 'INVALID' and
      OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
                        'TRIGGER', 'VIEW' )
  order by
      DLEVEL DESC,
      OBJECT_TYPE,
      OBJECT_NAME;

  spool off

• end =============== end ================= end

  ---

  • script for 8.1.5 ========================= If this script fails when run on 8.1.5 with: ERROR at line 1: ORA-01436: CONNECT BY loop in user data. This is caused by [BUG:895238] fixed in 8.1.7.

  Then the following modification will run, though will not order by dependencies.  

===============cut====================cut=========================cut=======

  set heading off
  set pagesize 0
  set linesize 79
  set verify off
  set echo off

  spool comp_all.sql

  select

      decode( OBJECT_TYPE, 'PACKAGE BODY',


      'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',

      'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || '

compile;' )
  from

     dba_objects
  where

     STATUS = 'INVALID' and OBJECT_TYPE in 
     ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW'

)
  order by OWNER, OBJECT_TYPE, OBJECT_NAME;

  spool off

• cut ==================== cut ============= cut

  ---

  EXAMPLE:   alter PACKAGE SYS.STANDARD compile;
  alter PACKAGE SYS.PIDL compile;
  alter VIEW SYS.EXU8COLNN compile;
  alter VIEW SYS.SYS_OBJECTS compile;

  alter PACKAGE SYS.DBMS_DEBUG compile;
  alter PACKAGE SYS.DBMS_SNAPSHOT_UTL compile;
  alter PACKAGE SYS.DBMS_SQL compile;
  alter PACKAGE SYS.DBMS_UTILITY compile;
  alter PACKAGE SYS.DIUTIL compile;
  alter PACKAGE SYS.PBUTL compile;
  alter VIEW SYS.CODE_PIECES compile;
  alter VIEW SYS.DBA_AUDIT_TRAIL compile;

  alter VIEW SYS.DBA_SNAPSHOTS compile;
  alter VIEW SYS.DBA_SNAPSHOT_LOGS compile;   alter PROCEDURE SYS.PSTUBT compile;
  alter PROCEDURE SYS.SUBPTXT2 compile;

Muthu

> -----Original Message-----
> From:	John Dunn 
> Sent:	Tuesday, January 09, 2001 7:11 AM
> To:	Multiple recipients of list ORACLE-L
> Subject:	batch recompile invalid packages
> 
> Is there a easy way for me to easily recompile all my packages, functions
> for a user etc  without doing a create/replace
> 
> John
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: John Dunn
>   INET: john.dunn_at_sefas.co.uk
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L

Received on Tue Jan 09 2001 - 09:27:01 CST