When we are doing the operations like upgrades, patches, and DDL changes the associated schema objects will get invalid. So it is difficult to recompile all the user objects one by one since it is a time-consuming process, especially when complex dependencies are there. There is a utility available in Oracle to overcome this time-consuming process.
Initially, how to identify invalid objects in a schema.? For this, you can use the ‘USER_OBJECTS‘ table as shown in the below query.
SELECT * FROM USER_OBJECTS WHERE STATUS = ‘INVALID’;
Here you can see the list of invalid objects like Package, Procedure, Function, etc. So if you want to recompile all these invalid objects at a shot then you can use DBMS_UTILITY.compile_schema utility.
EXEC DBMS_UTILITY.compile_schema(schema => ‘SCHEMA_NAME’, compile_all => false);
Please don’t forget to give the exact schema name in the SCHEMA_NAME part.
The compile_schema procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema.
This can be called from SQL developer/SQL plus.
