Oracle Invalid Objects | Compile Invalid Objects Using utlrp.sql

I will explain Oracle Invalid Objects | Compile Invalid Objects Using utlrp.sql in this post.

 

Oracle Invalid Objects

When you perform database upgrades, patches,database migration and some DDL Changes, your database objects may be Invalid because of these major changes. When any package,procedure,views and etc.. has been invalid, the related code or object won’t run properly. So that you need to compile these objects and their status must be VALID.

 

 

 

 

 

You can check the INVALID Objects using the following script.

SELECT owner,
       object_name,
       object_type,
       status,
       created,
       last_ddl_time
FROM   dba_objects
WHERE  status = 'INVALID';

 

SELECT object_name, object_type, status,
created,
last_ddl_time FROM user_objects WHERE status = 'INVALID';

 

If you have found the Invalid Objects, you can compile them one by one as follows.

 

ALTER PROCEDURE PROCEDURE_NAME COMPILE;
ALTER FUNCTION FUNCTION_NAME COMPILE;
ALTER PACKAGE PACKAGE_NAME COMPILE;
ALTER PACKAGE PACKAGE_NAME COMPILE BODY;
ALTER VIEW VIEW_NAME COMPILE;
ALTER TRIGGER TRIGGER_NAME COMPILE;

 

Compile Invalid Objects Using utlrp.sql

Or You can compile all Invalid Objects using the utlrp.sql under $ORACLE_HOME/rdbms/admin directory.

 

SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> @utlrp.sql

 

You can check the count of Invalid objects before utlrp.sql and after utlrp.sql using the following script.

 SELECT count(*) FROM dba_objects WHERE status='INVALID';

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database