Query to Generate Script for Compiling Invalid Objects in Oracle Database

Query  to list invalid objects

select
decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) ||
' ' || owner || '.' ||
object_name
as invalid_objects
from dba_objects
where status = 'INVALID' and owner='APPS'
object_type in
('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW')
ORDER BY 1;


Query to generate the SQL commands for compiling invalid objects.


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'
    AND   owner = 'APPS'
    AND   object_type IN (
        'PROCEDURE',
        'FUNCTION',
        'PACKAGE',
        'PACKAGE BODY',
        'TRIGGER',
        'VIEW'
    )
ORDER BY
    1;