Oracle Invalid objects : identify and repair

Oracle support complex and interconnected object structure and changing one object can cause some other database objects to become "invalid".

The following sql query will display a list of Oracle invalid objects:

SELECT owner ,object_type ,object_name FROM dba_objects
WHERE status != 'VALID' order by owner, object_type ;

You can also recompile Oracle invalid objects to make them valid. Oracle invalid objects sometimes have dependencies, so it may be necessary to run the recompile process according to the dependencies.

SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM dba_objects where status = 'INVALID' and owner='SCOTT';