Why an object became invalid in oracle databse.

Question

There are some invalid views and functions in my database.

I am not sure what changes caused these objects to became invalid since many people are working on the same database.

How can I see which change caused the view to become invalid?

Answer 1

In short, no.

You can however use the DBA_DEPENDENCIES view to check which objects the views and functions depend on, then query DBA_OBJECTS to check the last_ddl_time for each of these dependant objects - that should give you an indication of which objects have changed and caused the view to be invalid as a result.

It's quite normal for objects to become invalid in development databases when columns are added to tables etc.

Answer 2

As Phil says, you can't really to this retrospectively.

You can create DDL triggers to capture this in the future however. These will fire before/after a DDL event, allowing you to capture the dependencies to a table:

create table invalidations (
  operation           varchar2(30),
  invalidating_object varchar2(30),
  invalidating_owner  varchar2(30),
  invalidated_object  varchar2(30),
  invalidated_owner   varchar2(30),
  invalidation_date   date
);

create or replace trigger befddl_trg
before ddl
on schema
declare
begin
  insert into invalidations
  select ora_sysevent, ora_dict_obj_name, ora_dict_obj_owner, d.name, d.owner, sysdate
  from   all_dependencies d 
  where  referenced_name = ora_dict_obj_name 
  and    referenced_owner = ora_dict_obj_owner;

end befddl_trg;
/

create table t1 (x integer);

create view v1 as
  select * from t1;

create or replace procedure prc as 
begin
  for c in (select * from t1) loop
    null;
  end loop;
end;
/

alter table t1 add y integer;

select invalidated_object || ' was invalidated by ' || invalidating_object || ' at ' || 
       to_char(invalidation_date, 'dd/mm/yyyy hh24:mi') output 
from   invalidations;

OUTPUT
-------------------------------------------------
V1 was invalidated by T1 at 15/10/2012 17:21
PRC was invalidated by T1 at 15/10/2012 17:21

Note that just because an object is listed as a dependency, doesn't necessarily mean it'll be invalidated when you modify the base object. This becomes more likely in 11g with its finer-grained dependencies. So you'll need to extend this to check the ALL_OBJECTS.STATUS field to check whether whether you have actually invalidated the dependencies, with checks as to whether it was already invalid. How far you need to extend depends upon why you need to know what the "invalidator" was.

A word of warning - if you manage to (permanently) invalidate the BEFDDL_TRG (e.g. by dropping the INVALIDATIONS table), you won't be able to run any DDL in the schema again! A suitably privileged other user will have to drop/recreate the trigger for you.