REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM <SQLDIR_GRP>UTIL MOST</SQLDIR_GRP>
REM	<SQLDIR_TXT>
REM	Recompile all invalid objects including JAVA and TYPE.
REM	Allows compile of a single schema or all schemas.
REM	</SQLDIR_TXT>

REM $Header: recomp.sql 2.1 beta 1997/08/28 12:00:00 dsethi $
REM
REM Copyright (c) 1996 Oracle Corporation, Redwood Shores, California USA
REM                              All rights reserved.
REM
REM Notes: RUN THIS SCRIPT IN SQLPLUS UNDER USER 'SYS'
REM        Running as any other user may not work.
REM
REM -- Deepinder S Sethi
REM    Applications Core Technology Group
REM    Oracle Worldwide Customer Support
REM    dsethi@us.oracle.com
REM -- (02/16/2001 Vitaliy Mogilevskiy)
REM    Oracle OSI S&L
REM    vitaliy.mogilevskiy@oracle.com
REM    Added support for "JAVA CLASS Compile "
REM -- (03/02/2001 Vitaliy Mogilevskiy)
REM    Oracle OSI S&L
REM    vitaliy.mogilevskiy@oracle.com
REM    Added support for "TYPE Compile BODY"
REM -- (05/10/2001 Vitaliy Mogilevskiy)
REM    Oracle OSI S&L
REM    vitaliy.mogilevskiy@oracle.com
REM    Added support for single owner compile with DECODE in the WHERE
REM -- (11/12/2001 Vitaliy Mogilevskiy)
REM	Alcatel Internetworking
REM	vit100gain@earthlink.net
REM	Added support for snapshoot recompile
REM	at this point if DBA_OBJECTS.OBJECT_TYPE = UNDEFINED
REM	I try to substitute it with SNAPSHOOT we'll see how
REM	this works ...
REM

accept 1 prompt "Enter owner of the objects [Enter for all]: "

set verify off
set echo off
set heading off
set pagesize 0
set linesize 110
set feedback off
Set Term on


Prompt Counting number of invalid objects....


-- Count the number of invalid objects before running recomp
-- The value is printed later
-- *********************************************************


variable count_beg number
begin
   select count(*) into :count_beg
   from dba_objects 
   where status='INVALID'
   and   DECODE(UPPER('&&1'),NULL,'x',owner) like NVL(UPPER('&&1'),'x') ;
end;
/


Prompt Done.


-- Create an sql script with ALTER object.... statements for invalid objects
-- *************************************************************************


prompt Generating recompile statements....


set term off
spool recomp-r.sql


rem col  col_type         format A15
rem col  col_name         format A60
rem col  col_package_type format A13
col  sort_owner       noprint
col  sort_type        noprint


prompt spool recomp.log


prompt Select to_char(sysdate, 'mm-dd-yy hh:mi') from dual;
prompt /
prompt prompt **************


prompt set echo on
prompt set term on


select 
   'Alter ' ||
   decode( object_type, 
            'PACKAGE BODY', 'PACKAGE', 
            'TYPE BODY', 'TYPE', 
            'UNDEFINED', 'SNAPSHOT', object_type ) ||
   ' ' ||
   owner || '.' || DECODE(object_type,'JAVA CLASS','"'||dbms_java.longname(object_name)||'"',object_name) || 
                   ' Compile ' ||  
   decode( object_type, 
           'PACKAGE', 'SPECIFICATION', 
           'PACKAGE BODY', 'BODY',
           'TYPE BODY', 'BODY',
           ' ' 
         ) ||
   ';',
   decode( owner, 
           'SYS', 1,
           'SYSTEM', 2,
           3
         ) SORT_OWNER,
   decode( object_type,
           'VIEW', 1,
           'PACKAGE', 2,
           'TRIGGER', 9,
           3
         ) SORT_TYPE
from
   dba_objects
where status='INVALID'
and   DECODE(UPPER('&&1'),NULL,'x',owner) like NVL(UPPER('&&1'),'x')
order by
   SORT_OWNER, SORT_TYPE
/


prompt set echo off
spool off


prompt done.


spool recomp.log



@@recomp-r.sql




-- Print a list of Invalid objects after the recomp
-- ************************************************


set term on


col owner       format A17
col object_name format A30
col object_type format A30
col sort_owner  noprint
col sort_type   noprint


prompt     
prompt  
prompt List of first 50 Invalid objects after recompile.
prompt ================================================


Select owner, object_name, object_type,
       decode( owner, 
               'SYS', '1',
               'SYSTEM', '2',
               '3' || owner
             ) SORT_OWNER,
       decode( object_type,
               'VIEW', 1,
               'PACKAGE', 2,
               'TRIGGER', 9,
               3
             ) SORT_TYPE
from
     dba_objects
where  status='INVALID'
and   DECODE(UPPER('&&1'),NULL,'x',owner) like NVL(UPPER('&&1'),'x')
order by
     SORT_OWNER, SORT_TYPE
/



-- Count the number of invalid objects after running recomp and print remarks
-- **************************************************************************


variable remark1   varchar2(70)
variable remark2   varchar2(70)
variable remark3   varchar2(70)
variable count_end number


begin
   select count(*) into :count_end
   from dba_objects 
   where status='INVALID'
   and   DECODE(UPPER('&&1'),NULL,'x',owner) like NVL(UPPER('&&1'),'x');
   
   :remark1 := ' ' ;
   :remark2 := ' ' ;
   :remark3 := ' ' ;


   if :count_end = 0 then
      :remark1 := 'There are NO INVALID OBJECTS in the database now!';
   else
      if :count_beg = :count_end then
         :remark1 := 'The number of invalid objects is still the same after recompile.';
         :remark2 := 'Please analyze the existing invalid objects.';
         :remark3 := 'IF CALLING ORACLE SUPPORT, PLEASE HAVE THE RECOMP.LOG READY.';
         :remark3 := ' ';
      else
         :remark1 := 'Some objects have compiled successfully.';
         :remark2 := 'Please re-run RECOMP.SQL to compile the rest of invalid objects.';
      end if;


   end if;
end;
/


prompt
prompt
prompt Invalid objects before Recomp....
print count_beg
prompt Invalid objects after Recomp....
print count_end
prompt
print remark1
print remark2
print remark3


spool off