Identifying Invalid Objects: Use all_objects table to identity invalid schema objects. Oracle has given the default pkg to recompiling invalid/all schema objects in the database. SELECT owner, object_type, object_name, status FROM all_objects WHERE status = 'INVALID' ORDER BY owner, object_type, object_name; Custom Script: When you have to compile "N" no of invalid objects in one go, the best approach is to write custom script as below, BEGIN FOR cur IN(SELECT object_name, object_type, owner FROM sys.all_objects WHERE object_type = 'MY_OBJECTS' AND owner = 'MY_SCHEMA' AND status = 'INVALID' -- For all objects, it should be excluded ) LOOP BEGIN IF cur.object_type = 'PACKAGE BODY' THEN EXECUTE IMMEDIATE 'alter
by Karkuvelraja Thangamariappan ♠ (@tkarkuvelraja)