Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: compile invalid objects SCRIPT

RE: compile invalid objects SCRIPT

From: Gabriel Aragon <gabriel_gap_at_YAHOO.COM>
Date: Mon, 19 Aug 2002 10:33:32 -0800
Message-ID: <F001.004B92FD.20020819103332@fatcity.com> 





>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






"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).


Received on Mon Aug 19 2002 - 13:33:32 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US