Infrastructure at your Service

How often did I type a query like this to list the invalid objects in a database?

select count(*)
  from dba_objects
 where status  'VALID';
    -- and user in/not in

Today I learned another way to do the same.

Read More

There is a view called dba_invalid_objects. Surprisingly the view is not listed in the official 12c database reference. It is however mentioned in the upgrade guide.
Let’s do some tests. Currently I do not have any invalid objects:

SQL> select count(*) from dba_objects where status  'VALID';

  COUNT(*)
----------
	 0

SQL> select count(*) from dba_invalid_objects;

  COUNT(*)
----------
	 0

SQL> 

I’ll create an invalid procedure:

SQL> create procedure a ( a in number ) as a int; begin 1=1; end;
2 /

Warning: Procedure created with compilation errors.

Is it listed in dba_invalid_objects?

SQL> select OBJECT_NAME from dba_invalid_objects; 
OBJECT_NAME -------------------------------------------------------------------------------- A

Yes. Indeed this is a just a short cut to select from dba_objects:

SQL> r
  1* select TEXT from dba_views where view_name = 'DBA_INVALID_OBJECTS'

TEXT
--------------------------------------------------------------------------------
select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE
CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",
"SECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINE
D" from DBA_OBJECTS
where STATUS = 'INVALID' and
  (OBJECT_TYPE != 'TYPE' or (OBJECT_TYPE='TYPE' and SUBOBJECT_NAME is null))

Anyway, good to know.

Seems this view was introduced with Oracle 11gR1.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure