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

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

share|improve this question

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`;
share|improve this answer
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';
share|improve this answer
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 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.