• Arun Kumar

Find Invalid Objects Inside Oracle

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 !=VALIDORDER BY OWNER, OBJECT_TYPE;

OR

SELECT OWNER, STATUS FROM DBA_OBJECTS 
WEHRE STATUS !=VALIDGROUP 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’);   --> Oracle 9i

EXEC DBMS_UTILITY.COMPILE_SCHEMA(SCOTT);      --> 10g and above
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.


639 views

Recent Posts

See All