Blog

How to check Invalid Objects in Oracle :

How to check Invalid Objects in Oracle :
Sometimes DDL changes/patches/upgrades may cause to an object becomes invalid. Today, in this article we will learn how to check invalid objects in Oracle Database with detailed commands. Also, how to resolve this, we will also go through that.

Problem :

Issue the below command to check Invalid Objects in Oracle :

set lines 300
set pages 400
col owner for a20
col object_type for a20
col status for a15
COL object_name FORMAT A25
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Here we can see the output as below :

check Invalid Objects in Oracle

Resolution :

We can make a dynamic query as below to compile the objects :

select 'alter '||object_type ||' '|| owner||'.'||object_name||'   compile ;' from dba_objects where status<>'VALID';

The output will be as below :

How to check Invalid Objects in Oracle :

Now, execute the commands came in the output.


SQL>alter FUNCTION SAMIR.TEST   compile ;
PL/SQL procedure successfully completed.

SQL>alter TYPE SAMIR.EMP   compile ;
PL/SQL procedure successfully completed.

SQL>alter PACKAGE SYS.TEST_PKG   compile ;
PL/SQL procedure successfully completed.

Now, check the status, it will be in valid status.

Also, you can run below script as well which will make the invalid status as valid as well.

SQL> @?/rdbms/admin/utlrp.sql

So, here are some simple steps to check invalid objects in Oracle and to resolve them also. Hope this helps !!

Also, you can get more reference from the Oracle Doc as well.

Reference Other Articles :

Leave a Reply