Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
changing the parameters in procedures and functions in packages or just recompiling stand alone procedures will do it to. – kevinsky Oct 15 '12 at 12:09

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
  insert into invalidations
  select ora_sysevent, ora_dict_obj_name, ora_dict_obj_owner,, 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 
  for c in (select * from t1) loop
  end loop;

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;

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.

share|improve this answer
What you say is technically not correct for packages and procedures, so I would presume also incorrect for triggers: Oracle automatically tries to revalidate objects when they are used. As long as the code recompiles correctly, the only thing you can notice as a user is the increase in execution time for that request. On the other hand, if your session has PL/SQL state for packages that are recompiled, then you'll get the famous ORA-04068 "Existing state of packages has been discarded". – Colin 't Hart Oct 15 '12 at 17:16
@Colin'tHart - I'm not sure what you're saying is incorrect - could you clarify please? – Chris Saxon Oct 16 '12 at 7:45
It's incorrect to say that "if you manage to invalidate the BEFDDL_TRG, you won't be able to run any DDL in the schema again!" For the above reasons, the trigger may become invalid temporarily and be revalidated by Oracle on next execution. – Colin 't Hart Oct 16 '12 at 7:46
@Colin'tHart Yes, it's true that Oracle will attempt to revalidate it. What I meant was if you permanently invalidate it (e.g. by dropping the invalidations table) then the revalidation will fail. Because this is a BEFORE DDL trigger this error will be thrown before executing any DDL, so no statements will run! I've updated the answer to clarify this. – Chris Saxon Oct 16 '12 at 7:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.