Invalid Objects in Oracle Database :
We always wonder why there are invalids in my schema/database. What has been changed/caused to increase invalids.The reason could be , there is a change in the dependent objects structure. There could be a change in the package which has many referencing/dependent objects.Or there are some errors in your package/ procedure/ functions by which objects become invalid.
Generally, these invalids are most common in the development database, development would be done my many developers and they would be doing changes to may of the objects which cause invalids. Also in the database where there is a major upgrade or patch deployed, in production databases where there is release happened.
How do I deal with these? Here is what i generally do , when i find invalids.
Find number of the invalid objects:
SELECT COUNT(*) FROM All_Objects WHERE STATUS = 'INVALID';
Find what all objects are invalid :
SELECT * FROM All_Objects WHERE STATUS='INVALID';
To check what object got changed recently:
SELECT * FROM All_Objects ORDER BY last_ddl_time DESC;
with above the query you will get the recently changed objects.
To check the dependent objects of an object:
SELECT * FROM All_Dependencies WHERE NAME ='MOVE_DATA';
To check for the objects with errors:
SELECT * FROM All_Errors;
ALTER ... COMPILE
when you now the objects with errors, the most important thing is resolve them. The dependent object will get compiled only when the referencing object is in valid status.
How to compile an individual/single object manually:
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
DBMS_UTILITY
I have a huge number of invalid objects in my schema, How do I compile ?
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'HR',compile_all => FALSE);
compile_all parameter :when false, compiles only invalid objects in a schema; when true, compiles the entire schema.
UTL_RECOMP
An alternative way of compiling objects in a schema level, database level.
Schema Level
EXEC UTL_RECOMP.recomp_serial('HR');
EXEC UTL_RECOMP.recomp_parallel(8, 'HR');
Database Level
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(8);
RECOMP_SERIAL : Compile one by one object.
RECOMP_PARALLEL :Recompile all objects using parallel threads.
Operational Notes as defined in documentation : https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_recomp.htm
This package uses the job queue for parallel recompilation.
This package must be run using SQL*PLUS.
You must be connected AS SYSDBA to run this script.
This package expects the following packages to have been created with VALID status:
STANDARD (standard.sql)
DBMS_STANDARD (dbmsstdx.sql)
DBMS_JOB (dbmsjob.sql)
DBMS_RANDOM (dbmsrand.sql)
There should be no other DDL on the database while running entries in this package. Not following this recommendation may lead to deadlocks.
