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

1
2
3
4
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
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:

1
2
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?

1
2
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:

1
2
3
4
5
6
7
8
9
10
11
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.

Post Views: 1,598
Thumbnail [60x60]
by
Daniel Westermann