One of the most important things that affect performance is invalid objects, such as (Procedures, Indexes, Materialized Views, Views, Triggers, Packages, and Functions).

The fact is that these objects can become invalid either at the moment of their creation or after their creation, due to several reasons. One of these reasons is programming errors when writing these procedural objects. In such cases, the object is created but is marked as (INVALID), making it unusable. Additionally, when changes are made to the Data Objects related to these objects, many of the objects created are associated with some Data Objects, such as tables. When compiling procedural objects, it is verified whether the associated tables or Data Objects are correct, and it is ensured that the references are valid.

For example, if the code points to a specific column in a table, this column must exist; otherwise, the compilation of the code will fail. If any of the Data Objects related to a procedural object change, this procedural object will be flagged as (INVALID).

The same scenarios can occur for Views. Many views might be created without issues, but when changes are made to the tables associated with these views, those views become invalid (INVALID).

A database administrator can identify invalid objects through the DBA_OBJECTS view, which includes a column named STATUS that indicates whether the object is valid or invalid (VALID or INVALID).

select object_name from dba_objects where status='INVALID' and object_type='PROCEDURE' 

In the query above, we reviewed all the invalid procedures in the database. The question now that the database administrator should ask is: why are these procedures invalid?

Generally, if some procedural objects are no longer needed, they can be deleted. However, as a first step, the database administrator can attempt to compile the invalid objects. The compilation process may succeed or fail, and if it fails, error messages will be returned. But in general, a good database administrator handles invalid objects before the users notice them.

Fixing Invalid Objects:

To compile procedural objects, we can write the following code:

ALTER PROCEDURAL_TYPE PROCEDURAL_NAME COMPILE;

alter trigger repcatlogtrig compile; 

This applies to all types of procedural objects.

Additionally, the database administrator can also compile views.

alter view tuser compile; 

The compile process will succeed if there is no issue. However, if the operation fails, the cause must be investigated. If the compile process fails for a specific operation, we can search for the issue using the SHOW ERRORS command. But, it is important to note that the SHOW ERRORS command does not work with views.

But what if there are hundreds or more invalid objects that need to be compiled? Can the database administrator compile all of these objects individually? Without a doubt, this will be very tedious. In such cases, it is better for the database administrator to use a ready-made script to compile all invalid objects at once.

However, this script must be executed after connecting to the database as SYSDBA.

The script can be found at the following location:

%oracle_home%\rdbms\admin\utlrp.sql

Of course, this script might succeed or fail in compiling invalid objects. If the operation fails, the causes should be investigated, but this should be done individually for each of the objects.