Thanks guys .
-Bp
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Monday, August 19, 2002 11:33 AM
> 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 <hamcdc_at_yahoo.co.uk> wrote:
> > @?/rdbms/admin/utlrp.sql
> >
> > hth
> > connor
> >
> > --- "Vergara, Michael (TEM)" <mvergara_at_guidant.com>
> > 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:ORACLE-L_at_fatcity.com>
> > > 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: hamcdc_at_yahoo.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).
>
>
> =====
> "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: gabriel_gap_at_yahoo.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
> (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: BigP
INET: big_planet_2000_at_hotmail.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
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Aug 20 2002 - 12:37:26 CDT
