I have an Issue with a view, recently I made changes to the view and committed the code (in SVN). Code was moved to another repository and compiled, but my modifications are not reflected. I searched on Google to find when the view was last compiled, but I didn't find any clue.

Is there any way to find when particular view was compiled? Does Oracle store historic data (when was it compiled/modified/etc.)?


I think that you can determine the time a view was last compiled querying the data dictionary view dba_objects:

sql> select last_ddl_time from dba_objects where object_type = 'VIEW' and object_name = 'MOO';

You can also see the text the view is based on in the dba_views dictionary view:

sql> select * from dba_views where view_name = `MOO`;
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' 
from dba_objects
where status='INVALID';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' 
from dba_objects
where status='INVALID' 
and owner=upper('&user_name');

select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE','UNDEFINED',
'MATERIALIZED VIEW', object_type)||' '||object_name||' compile;' 
from dba_objects 
where status='INVALID' 
and owner= '&TB';
  • 2
    What is the point of these statements? How do they relate to the question? – Vincent Malgrat Mar 19 '13 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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