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

 

 46,308 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply