Re: batch recompile invalid packages

As a matter of interest, there is an Oracle provided script to recompile all objects. It is in the ORACLE_HOME directory under rdbms/admin. The script is utlrp.sql and has been provided since 8.0

Terry

Muthu Avudaiyappan wrote:

> 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
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Muthu Avudaiyappan
> INET: MAvudaiyappan_at_Vertek.com
>
> 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 Wed Jan 10 2001 - 07:41:20 CST