It is quite normal for Oracle database objects to become INVALID, especially when a dependency chain is broken.This article takes a closer look at:
We need to know about Typical reasons.....
Why Oracle objects become INVALID?
How to identify INVALID objects in Oracle?
How to convert INVALID objects to VALID?
Why do Oracle objects become INVALID ?
Oracle objects often become invalid during patching or upgrades because objects they depend on get dropped or altered in some way.
Let’s see this in action with a table having a dependent view and procedure.
I am going to create new table here ..
-- Table on which other objects depend --
SQL> create table temp_table
2 ( id number);
Table created.
--The dependent view and procedure:--
SQL> create view temp_view
2 as select * from temp_table;
View created.
-- Dependent procedure--
SQL> create procedure temp_proc
2 as
3 l_id number;
4 begin
5 select id into l_id
6 from temp_table;
7 end;
8 /
Procedure created.
All the three objects – the table, the view and procedure – are valid at this time:
-- Check object validity---
SQL> select object_name
2 , object_type
3 , status
4 from all_objects
5 where object_name like 'TEMP%';
Now, we drop the table…
-- Drop the table; break the
- dependency chain----
SQL> drop table temp_table;
Table dropped.
…and check the status of the view and the procedure ---
Outcome: no more table – and both of the dependent objects have become invalid.
Another way objects become invalid is when errors occur at the time of creation or change. For example, a stored procedure gets created/replaced as valid if it compiles successfully, else it is invalid.
We can test this out with a procedure containing a coding error (a missing quote in dbms_output.put_line):
-- Procedure with compilation error
-- Will get created as INVALID; will not autocompile---
SQL> create or replace procedure temp_proc_with_bug
2 as
3 begin
4 dbms_output.put_line('Missing closing quote!);
5 end;
6 /
Warning: Procedure created with compilation errors.
When we check ALL_OBJECTS, we find that this procedure got created despite the error, but with status INVALID.
-- Check object validity--
SQL> select object_name
2 , object_type
3 , status
4 from all_objects
5 where object_name = 'TEMP_PROC_WITH_BUG';
How to identify INVALID objects in Oracle ?
The first step is to get the list of objects and their relevant details (type, last DDL time, etc.), with a filter for status INVALID. To get this information, query [DBA/ALL/USER]_OBJECTS depending on your privileges:
DBA_OBJECTS : All objects in the database
ALL_OBJECTS : All objects accessible to the user
USER_OBJECTS : All objects owned by the user
-- Check for invalid objects---
SQL> select object_name
2 , object_type
3 , status
4 from all_objects
5 where status = 'INVALID';
The next step is to find out *why* the object is invalid. To get this information, query [DBA/ALL/USER]_ERRORS depending on your privileges.
DBA_ERRORS : Current errors on all objects in the database
ALL_ERRORS : Current errors on all objects accessible to the user
USER_ERRORS : Current errors on all objects owned by the user
-- Check the error details---
SQL> select name
2 , type
3 , line
4 , position
5 , text
6 from all_errors
7 where name like 'TEMP%';
Note that not all invalid objects will have errors listed for them in *_ERRORS;
some will get an entry when we make an attempt to access them.
You can see this in the results of the last two SQLs:
though TEMP_VIEW and TEMP_PROC are listed as invalid in ALL_OBJECTS, they are absent from ALL_ERRORS as they have not been accessed yet.
Let’s try to access the invalid objects and check ALL_ERRORS again.
-- Query the INVALID view--
SQL> select * from temp_view;
select * from temp_view
*
ERROR at line 1:
ORA-04063: view "HR.TEMP_VIEW" has errors
-- Execute the INVALID package
SQL> exec temp_proc;
BEGIN temp_proc; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object HR.TEMP_PROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
-- Check the error details--
SQL> select name
2 , type
3 , line
4 , position
5 , text
6 from all_errors
7 where name like 'TEMP%';
The errors are now listed in ALL_ERRORS, since an attempt was made to query or access the invalid objects.
How to convert INVALID objects to VALID ?
Most of the time, you do not need to explicitly recompile objects. When there are no compilation failures, the object will turn from invalid to valid as soon as it is executed or accessed again.
To see how this works, let’s reinstate the missing object (table TEMP_TABLE) which had caused dependent objects (view TEMP_VIEW and procedure TEMP_PROC) to become invalid, and then check the status of dependent objects.
-- Reinstate the dropped table--
SQL> create table temp_table
2 ( id number);
Table created.
-- BEFORE querying dependent view---
-- Check object validity--
SQL> select object_name
2 , object_type
3 , status
4 from all_objects
5 where object_name like 'TEMP_VIEW';
OBJECT_NAME OBJECT_TYPE STATUS
------------------ -------------- -------
TEMP_VIEW VIEW INVALID
-- Query the dependent view--
SQL> select * from temp_view;
no rows selected
-- Query the dependent view--
SQL> select * from temp_view;
no rows selected
-- AFTER querying dependent view---
-- Check object validity--
SQL> select object_name
2 , object_type
3 , status
4 from all_objects
5 where object_name like 'TEMP_VIEW';
OBJECT_NAME OBJECT_TYPE STATUS
------------------ -------------- -------
TEMP_VIEW VIEW VALID
…which goes to show that the invalid object fixed itself on its own.
If you want to compile invalid objects in advance –
perhaps to distinguish between those that would auto-fix themselves and those that wouldn’t – you can follow one of the these approaches.
1. ALTER…COMPILE per object
Compile objects selectively, one at a time, using the syntax:
ALTER [object_type] [object_name] COMPILE;
SQL> ALTER PROCEDURE temp_proc COMPILE;
Procedure altered.
If the compilation fails, use “sho err” on SQL*Plus to display errors.
SQL> ALTER PROCEDURE temp_proc_with_bug COMPILE;
Warning: Procedure altered with compilation errors.
SQL> sho err
2. DBMS_UTILITY.COMPILE_SCHEMA
Use DBMS_UTILITY.COMPILE_SCHEMA to compile all object types in the specified schema. This takes as input a “compile_all” option:
if TRUE (the default), all objects in the schema are compiled, if FALSE only invalid objects are compiled.
exec dbms_utility.compile_schema(
schema => '<schema_name>'
, compile_all => FALSE -- Compile only INVALID objects
);
Let’s see this action:
SQL> exec dbms_utility.compile_schema(schema => USER, compile_all => FALSE);
PL/SQL procedure successfully completed.
-- AFTER compile_schema---
-- Check object validity---
SQL> select object_name
2 , object_type
3 , status
4 from all_objects
5 where object_name like 'TEMP%';
OBJECT_NAME OBJECT_TYPE STATUS
------------------ -------------- -------
TEMP_PROC PROCEDURE VALID
TEMP_PROC_WITH_BUG PROCEDURE INVALID
TEMP_TABLE TABLE VALID
TEMP_VIEW VIEW VALID
The objects that remain invalid after running DBMS_UTILITY.COMPILE_SCHEMA are those that need a review and fix of errors in *_ERRORS.
-----------------------S U M M A R Y
Oracle objects typically become invalid when a dependency chain is broken, or when they have errors at the time of compilation.
Invalid objects can be identified by querying *_OBJECTS with a filter on status = INVALID. Error details can be queried from *_ERRORS.
Invalid objects get automatically recompiled when they are accessed or executed.
To compile them in advance, you can use ALTER…COMPILE per object, or DBMS_UTILITY.COMPILE_SCHEMA for all objects in the schema.
-----------------------------------------------
S C R I P T F O R Y O U ............
S C R I P T F O R Y O U ............
set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;
spool off;
set heading on;
set feedback on;
set echo on;
