• 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.


215 views

Recent Posts

See All

Generate Table DDL Command

The DBMS_METADATA package allows you to generate DDL commands for any of the database objects inside Oracle database. Generate Table DDL Command Generate View DDL Command Generate Materialized View DD

Temp Tablespace Utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp