Is there any way to find out when particular view was compiled?

Question

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.)?

Answer 1

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`;

Answer 2

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';