Posts

Showing posts from December, 2018

Recompiling Invalid/All schema objects in the database

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