• Arun Kumar

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 !=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’);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



153 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir