• 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



Recent Posts

See All

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose

Top resource intensive SQL

Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is runnin

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback