Compiling Invalid Objects

DBAs' are required to work on Invalid Oracle Objects. What does that mean? Objects are invalid when their dependent objects are missing or becomes invalid themselves. Before the database upgrade, all the objects must be valid. After the upgrade or patch, most of the objects get invalidated and I do not know why? They will become valid if the application calls them later. Re-compiling objects will validated the objects.
In this blog, we will talk about how to list invalid objects and how to re-compile them? First, we need to check if we have anything that are invalids. Let's find out the all the invalid objects in your database. The user running below command should have a DBA privilege to get ALL the invalid objects from a database. A developer or your non DBA log in will display objects that you have access to. The Oracle Query below will display the count of invalid objects for each admin account.

SELECT owner, 
       Count(owner) 
FROM   dba_objects 
WHERE  status LIKE 'INVALID' 
       AND owner LIKE '%ADMIN%' 
GROUP  BY owner 
ORDER  BY owner; 

After executing the query, we have few hundreds invalid objects for some accounts. Before diving into the detail on why they are invalid, I recommend re-compiling those invalid objects. Recompiling will fix the invalid object problem. If you see issue even after running the compile command, you will need to investigate the cause and fix it.

Let's see how we can re-compile them. Compiling these invalid objects may validate the schema objects. Re-compiling may not validate all the invalid objects, you need to ensure that the object’s dependencies exist and are valid. There are couple different ways to compile those objects. The one below is my favorite query that I wrote for recompiling. This compiles all the invalid objects for each schema and wont bother the valid objects. Isn't that what everyone wants?

BEGIN 
    FOR x IN (SELECT DISTINCT( owner ) 
              FROM   dba_objects 
              WHERE  status LIKE 'INVALID' 
              ORDER  BY owner) LOOP 
        dbms_output.Put_line (x.owner 
                              ||'####Started####'); 
        dbms_utility.Compile_schema (x.owner, FALSE); 
        dbms_output.Put_line (x.owner 
                              ||'####Completed####'); 
    END LOOP; 
END;
/ 

Note: See what dbms_utilty.compile_schema is made out of.. 

DBMS_UTILITY.COMPILE_SCHEMA (
schema VARCHAR2,
compile_all BOOLEAN DEFAULT TRUE,
reuse_settings BOOLEAN DEFAULT FALSE);

** Changing compile_all to FALSE will only compile invalid object. The default setting is TRUE. There is no reason to burn Oracle Juice when you need to compile particular objects. There's a package for it. Utilizing this package is more effective when you need to re-compile a materialized view or just one particular object. Compiling individual objects using Alter_Compile package.

DBMS_DDL.ALTER_COMPILE (
  type             VARCHAR2,
  schema           VARCHAR2,
  name             VARCHAR2
  reuse_settings   BOOLEAN := FALSE);

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);

Didn't like the alter_compile package, you have one other choice to re-compile each manually. You need to find the object type before you run this command. I would use user_objects table to find the objects type.

ALTER PACKAGE my_package COMPILE;   -- package
ALTER PACKAGE my_package COMPILE BODY;  --package body
ALTER PROCEDURE my_procedure COMPILE; -- procedure
ALTER FUNCTION my_function COMPILE; -- function
ALTER TRIGGER my_trigger COMPILE; -- trigger
ALTER VIEW my_view COMPILE; -- view
ALTER MATERIALIZED VIEW my_mview COMPILE; -- materialized view

Do you have any other ways to get this done? I would like to hear from you! are your database objects valid now?