>From the oracle Metalink..
/*
Doc ID: Note:1036972.6
DATABASE
Type: SCRIPT
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 15-OCT-1997
Last Revision Date: 27-APR-2000
Language: USAENG
======
Title:
======
Compiling Invalid Objects in the Database
===========
Disclaimer:
===========
This script is provided for educational purposes
only. It is not supported by
Oracle Support Services. This script has been tested
and appears to work as
intended; however, you should always test any script
before relying on it.
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to
differences in the way text
editors, e-mail packages, and operating systems
handle text formatting
(spaces, tabs, and carriage returns), this script may
not be in an executable
state when you first receive it. Check over the
script to ensure that errors
of this type are corrected.
=========
Abstract:
========
This script will compile INVALID objects in the
database.
=============
Requirements:
=============
DBA
=======
Script:
=======
-------------cut-------------cut---------------cut--------------
REM Script to compile INVALID Objects in the database
REM
REM VALIDATE.SQL
REM
REM This script recompiles all objects that have
become invalidated
REM
REM For proper generation of the log file, this
script should be
REM run after connecting as SYS (or internal)
using SQL*Plus.
REM
REM When run from Server Manager 2.3, all objects
will still be
REM recompiled, but the log file, VALIDATE.LOG,
will contain some
REM error messages. Those error messages are
generated because
REM Server Manager does not understand all of the
SET xxx messages
REM used in this script.
REM
*/
set pagesize 0
set linesize 120
set heading off
set feedback off
set trimspool on
set termout on
select 'Recompiling '||count(object_name)||' invalid
objects.'
from dba_objects where status='INVALID';
select 'This may take a long time. Please wait...'
from dual;
set termout off
spool validate_objects.sql
select 'spool validate.log' from dual;
select 'set trimspool on' from dual;
select 'alter ' || decode(object_type, 'PACKAGE
BODY', 'PACKAGE', object_type)
|| ' ' || owner || '.' || object_name || '
compile'
|| decode(object_type, 'PACKAGE BODY', '
body;', ';')
from dba_objects
where status='INVALID'
order by decode(owner, 'SYS', 'A', 'SYSTEM', 'B',
'C'||owner) asc,
decode(object_type, 'PACKAGE BODY', 'AAA',
'PACKAGE', 'AAB',
substr(object_type, 1, 3)) desc,
object_name;
/* REM
REM Compile SYS's objects first, then SYSTEM's, then
the rest.
REM This order by clause will result in compiling
objects
REM in this order:
REM
REM VIEWS, TRIGGERS, PROCEDURES, FUNCTIONS,
PACKAGES, PACKAGE BODIES.
REM
*/
select 'set heading on' from dual;
select 'set feedback on' from dual;
select 'select
substr(rpad(owner||''.''||object_name,40)' from dual;
select ' ||''(''||object_type||'')'', 1, 80)
"Remaining Invalid
Objects"' from dual;
select 'from dba_objects where status=''INVALID''
order by owner, object_type,
object_name;' from dual;
select 'spool off' from dual;
spool off
set termout on
set pagesize 25
set linesize 80
set heading off
set feedback off
select chr(13)||'Finished recompiling.' from dual;
select chr(13)||'There are '||count(*)||' remaining
invalid objects.'
||decode(count(*), 0, null, ' Please recompile
manually.')
from dba_objects where status='INVALID';
set heading on
select substr(rpad(owner||'.'||object_name,40)
||'('||object_type||')', 1, 80) "Remaining
Invalid Objects"
from dba_objects where status='INVALID' order
by owner, object_type,
object_name;
===============================================
the short version ;)
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 A.STATUS =
'INVALID'
AND A.OBJECT_TYPE IN ('PACKAGE BODY', 'PACKAGE',
'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
ORDER BY B.DLEVEL DESC, A.OBJECT_TYPE, A.OBJECT_NAME;
I hope this help
regards
Gabriel
--- Connor McDonald <[EMAIL PROTECTED]> wrote:
> @?/rdbms/admin/utlrp.sql
>
> hth
> connor
>
> --- "Vergara, Michael (TEM)" <[EMAIL PROTECTED]>
> wrote: > I have written scripts like this in the
> past,
> but
> > the problem
> > I run into is that recompiling some objects
> > invalidates others,
> > sometimes in a circular loop. What I'd like to
> > find/see/learn-how-to-do
> > is a script that compiles things in dependency
> > order. So far,
> > this capability eludes me. I use Oracle 8.0.5 ->
> > 8.1.7.4.
> >
> > Cheers,
> > Mike
> >
> > -----Original Message-----
> > Sent: Saturday, August 17, 2002 8:48 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Script "gen_recompile.sql" online at
> > http://www.EvDBT.com/tools.htm...
> >
> > As written, it will not execute the generated
> > "run_recompile.sql" script; you'll have to
> > uncomment the HOST command at the bottom to do
> that.
> > I like to leave it with it's teeth pulled however
> > -- at least at first -- so I can review the
> > generated script...
> >
> > Hope this helps...
> >
> > ----- Original Message -----
> > To: Multiple recipients of list ORACLE-L
> > <mailto:[EMAIL PROTECTED]>
> > Sent: Friday, August 16, 2002 4:28 PM
> >
> > I am feeling lazy today .. sooooo if someone can
> > give me script that compiles all invalid objects
> > untill all objects are valid ... will be great .
> Yes
> > I am looking for the one which does this in a loop
> > so that i dont have to run this many times to get
> 0
> > invalid objects
> >
> > Thanks ,
> > -Bp
> >
> >
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
>
> "Remember amateurs built the ark - Professionals
> built the Titanic"
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
=====
"Any dream worth having is a dream worth fighting for"(Cualquier sueño que valga la
pena tener, es un sueño por el que vale la pena luchar)Charles Xavier
__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gabriel Aragon
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
