Oracle Invalid Objects

Changing things in database can cause some objects to become “INVALID”. Query to check invalid objects in oracle:

SELECT OWNER, OBJECT_TYPE, OBJECT_NAME 

FROM DBA_OBJECTS

WHERE STATUS != ‘VALID’ 

ORDER BY OWNER, OBJECT_TYPE;

OR

SELECT OWNER, STATUS FROM DBA_OBJECTS 

WEHRE STATUS !=’VALID’

GROUP BY OWNER, STATUS;

Recompiling Invalid Objects

You can handle INVALID objects to make them VALID. Execute the below script:

EXEC UTL_RECOMP.recomp_serial(‘schema name’);     9i onwards

EXEC DBMS_UTILITY.COMPILE_SCHEMA(‘SCOTT’);   10g

Note: Oracle highly recommends running the script towards the end of any migration/upgrade/downgrade.

Oracle invalid objects sometimes have dependencies, so it may be necessary to run the oracle invalid objects recompile repeatedly

Was this article helpful?

Related Articles

Leave a Comment