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:
- 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.
The table:
SQL> -- Table on which other objects depend SQL> create table temp_table 2 ( id number); Table created.
The dependent view and procedure:
SQL> -- Dependent view SQL> create view temp_view 2 as select * from temp_table; View created. SQL> -- 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:
SQL> -- 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_TABLE TABLE VALID TEMP_VIEW VIEW VALID
Now, we drop the table…
SQL> -- Drop the table; break the SQL> -- dependency chain SQL> drop table temp_table; Table dropped.
…and check the status of the view and the procedure:
SQL> -- 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 INVALID TEMP_VIEW VIEW INVALID
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):
SQL> -- Procedure with compilation error
SQL> -- 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.
SQL> -- Check object validity SQL> select object_name 2 , object_type 3 , status 4 from all_objects 5 where object_name = 'TEMP_PROC_WITH_BUG'; OBJECT_NAME OBJECT_TYPE STATUS ------------------ -------------- ------- TEMP_PROC_WITH_BUG PROCEDURE INVALID
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
SQL> -- Check for invalid objects SQL> select object_name 2 , object_type 3 , status 4 from all_objects 5 where status = 'INVALID'; OBJECT_NAME OBJECT_TYPE STATUS ------------------ -------------- ------- TEST_VIEW VIEW INVALID TEMP_PROC PROCEDURE INVALID TEMP_PROC_WITH_BUG PROCEDURE 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
SQL> -- Check the error details
SQL> select name
2 , type
3 , line
4 , position
5 , text
6 from all_errors
7 where name like 'TEMP%';
NAME TYPE LINE POSITION TEXT
-------------------- --------- ----- -------- -------------------------------------------
TEMP_PROC_WITH_BUG PROCEDURE 4 24 PLS-00103: Encountered the symbol "Missing
closing quote!);
end;" when expecting one of the following:
( ) - + case mod new not null <an identi
fier>
<a double-quoted delimited-identifier> <
a bind variable>
table continue avg count current exists
max min prior sql
stddev sum variance execute multiset the
both leading
trailing forall merge year month day hou
r minute second
timezone_hour timezone_minute timezone_r
egion timezone_abbr
time timestamp interval date
<a string literal with
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.
SQL> -- 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
SQL> -- 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
SQL> -- Check the error details
SQL> select name
2 , type
3 , line
4 , position
5 , text
6 from all_errors
7 where name like 'TEMP%';
NAME TYPE LINE POSITION TEXT
-------------------- --------- ----- -------- -------------------------------------------
TEMP_PROC PROCEDURE 5 3 PL/SQL: SQL Statement ignored
TEMP_PROC PROCEDURE 6 8 PL/SQL: ORA-00942: table or view does not e
xist
TEMP_PROC_WITH_BUG PROCEDURE 4 24 PLS-00103: Encountered the symbol "Missing
closing quote!);
end;" when expecting one of the following:
( ) - + case mod new not null <an identi
fier>
<a double-quoted delimited-identifier> <
a bind variable>
table continue avg count current exists
max min prior sql
stddev sum variance execute multiset the
both leading
trailing forall merge year month day hou
r minute second
timezone_hour timezone_minute timezone_r
egion timezone_abbr
time timestamp interval date
<a string literal with
TEMP_VIEW VIEW 0 0 ORA-00942: table or view does not exist
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.
SQL> -- Reinstate the dropped table SQL> create table temp_table 2 ( id number); Table created. SQL> -- BEFORE querying dependent view SQL> -- 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 SQL> -- Query the dependent view SQL> select * from temp_view; no rows selected SQL> -- AFTER querying dependent view SQL> -- 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
Errors for PROCEDURE TEMP_PROC_WITH_BUG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/24 PLS-00103: Encountered the symbol "Missing closing quote!);
end;" when expecting one of the following:
( ) - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table continue avg count current exists max min prior sql
stddev sum variance execute multiset the both leading
trailing forall merge year month day hour minute second
timezone_hour timezone_minute timezone_region timezone_abbr
time timestamp interval date
<a string literal with
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. SQL> -- AFTER compile_schema SQL> -- 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.
Summary
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.
For Further Reading
- SQLCODE and SQLERRM
- DBMS_UTILITY.GET_PARAMETER_VALUE to find out your database name
- DBMS_UTILITY.GET_DEPENDENCY to find out object dependencies
