whatrecompile  (DBTier Script:  executes using -- sqlplus / as  sysdba)

What EBusiness Suite Database Objects are INVALID.

This Script also ReCompiles ALL Invalid Objects and Lists the resulting INVALID Objects.

Example: 

#---------------------------------------------------------------#

#- What (whatrecompile)                                        -#

#---------------------------------------------------------------#

                 OATC (oratest) Apps Invalid Objects           Page:   1

                 whatrecompile -- Sorted by Schema Name

Owner!Sche Object Type               Count

---------- -------------------- ----------

APPS       FUNCTION                      2

           PACKAGE                       6

           PACKAGE BODY                  6

           PROCEDURE                     5

           SYNONYM                       1

           TRIGGER                       3

           VIEW                          2

**********                      ----------

Total                                   25

#!/usr/bin/ksh

#whatrecompile ()

#-----------------------------------------------------------------------#

#    Module Name:  whatrecompile                                        #

#                                                                       #

#    Purpose:      Report on the Invalid Objects by Schama.             #

#                    ADMIN-Server    ConcMgr-Server                     #

#                    Forms-Server    Web-Server                         #

#                                                                       #

#    Maintenance                                                        #

#    Date          Author        Description                            #

#    -----------   ------------  -------------------------------------  #

#    10-Oct-2009   M.Barone      Module design/creation                 #

#-----------------------------------------------------------------------#

{

        DIRNAM=$(dirname $0)

        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')

        HNAME=$(uname -n)

        #---------------------------------------------------------------#

        #  TEST:  Number ($#) of arguments passed to this function?     #

        #---------------------------------------------------------------#

        if      [ $# = 0 ]

        then

            clear

            echo

            echo  " #--------------------------------------------------#"

            echo  " # whatrecompile:SYSDBA Recompile eBusiness Objects #"

            echo  " #--------------------------------------------------#"

            echo                                                              

            #-----------------------------------------------------------#

            #  Trap: Set CNTL-C CNTL-D CNTL-\Before Password Protection #

            #-----------------------------------------------------------#

            trap        "stty echo; return"     2                             

            trap        "stty echo; return"     3

            stty -echo

            read PASSWD?" Please Enter the Oracle APPS Passwd:  "

            stty echo

            echo

            #------------------------------------------------------------#

            #  Trap: Reset CNTL-C CNTL-D CNTL-\After Password Protection #

            #------------------------------------------------------------#

            trap        2

            trap        3

 

Appendix- whatrecompile script  (continued)

            clear

        else

            PASSWD=$1

            clear

        fi

#------------------------------------------------------------------------#

#  Initialize                                                            #

#------------------------------------------------------------------------#

        SPOOLNM=/tmp/whatrecompile_$$.txt

       echo

       echo Connecting as SYSDBA

       sleep 2

#------------------------------------------------------------------------#

#  SQL*Plus ("Here-Document")                                            #

#------------------------------------------------------------------------#

sqlplus  -s  /nolog  <<-END_FILE

        prompt #---------------------------------------------------------#

        prompt #- What (whatrecompile)                                  -#

        prompt #---------------------------------------------------------#

       PROMPT Connecting as SYSDBA

       PROMPT ** ReCompiling Invalid Objects **

      

       CONNECT / as SYSDBA

       @?/rdbms/admin/utlrcmp.sql

       set timing on

       exec utl_recomp.recomp_parallel(20);

       PROMPT Connecting as APPS

        CONNECT  APPS/$PASSWD

        SPOOL ${SPOOLNM}

        clear   BREAKS

        clear   BUFFER

        clear   COLUMNS

        clear   COMPUTES

        clear   SCREEN

        clear   SQL

        clear   TIMING

 

Appendix- whatrecompile script  (continued)

        set     feedback        off

        set     flush           on

        set     heading         on

        set     pause           off

        set     space           1

        set     termout         on

        set     verify          on

        set     linesize        80

        set     pagesize        999

        prompt #---------------------------------------------------------#

        prompt #- What (whatrecompile)                                  -#

        prompt #---------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR

        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        BREAK   ON  REPORT

        BREAK   ON  owner          skip 2       ON REPORT

        COMPUTE SUM LABEL 'Total'  OF  objcnt   ON OWNER

        COMPUTE SUM LABEL 'Total'  OF  objcnt   ON REPORT

       COLUMN  OWNER        format  A10   Heading "Owner!Schema"

       COLUMN  OBJECT_NAME  format  A40   Heading "Object Name"

       COLUMN  OBJECT_TYPE  format  A20   Heading "Object Type"

       COLUMN  OBJCNT       format  999999999    Heading "Count"

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps Invalid Objects'    -

                RIGHT   'Page:'     FORMAT 999 SQL.pno         skip 1  -

                CENTER  'whatrecompile -- Sorted by Schema Name'     skip 2

        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

        SELECT  DISTINCT owner, object_type, count(1) objcnt

         FROM dba_objects

        WHERE STATUS <>  'VALID'

       GROUP  BY owner, object_type

       ORDER  BY 1,2;

END_FILE

print

print  "Please see  /tmp/whatrecompile_$$.txt"

print

}