Take the 2-minute tour ×
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. It's 100% free, no registration required.

Is there a way to find objects that were compiled today?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

I think the timestamp column of dba_objects is what you are after:

create table t(id integer);
create function f return integer as
  n integer;
begin
  select count(*) into n from t;
  return n;
end;
/

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP           
------- ------------------- 
VALID   2011-09-05:11:01:52 

insert into t(id) values (1);
select f() from dual;             -- ***no recompile needed***

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP           
------- ------------------- 
VALID   2011-09-05:11:01:52 

alter table t add dummy integer;  -- ***invalidates f()***

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP           
------- ------------------- 
INVALID 2011-09-05:11:01:52 

select f() from dual;             -- ***forces recompile***
select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP           
------- ------------------- 
VALID   2011-09-05:11:01:53       -- ***note the different timestamp*** 

so to get all functions compiled today for example:

select * 
from dba_objects
where to_date(substr(timestamp,1,10), 'yyyy-mm-dd')>=trunc(sysdate)
      and object_type='FUNCTION';

--edit

it may be safer to use last_ddl_time instead (see @Alex's comment below), but the caveat is that last_ddl_time is also changed on grants and revokes.

share|improve this answer
    
The TIMESTAMP is for dependency checking (according to Tom), so it shows when something changed that external callers need to know - in your examples when the status changed, I think. If you do an ALTER ... COMPILE on a valid package the TIMESTAMP isn't updated, but LAST_DDL_TIME is. Of course for most practical purposes using TIMESTAMP would be fine, as you're unlikely to need to recompile a valid package, or care if you had... –  Alex Poole Sep 5 '11 at 12:02
    
@Alex good point, answer updated. Interestingly I find that timestamp and last_ddl_time both change on a package body when it is recompiled automatically. I wonder if anything other than alter ... compile and grant/revoke separates them –  Jack Douglas Sep 5 '11 at 12:31
    
Thank you very much... very helpfull answer... Now I want to invalidate these objects ... So return these objects back...How can I? –  kupa Sep 5 '11 at 12:51
    
@kupa have a look at this thread on OTN, especially the second page. However I cannot think of any good reason for wanting to force an object invalid. Do you have a good reason? –  Jack Douglas Sep 5 '11 at 12:59
    
My reason is that after validating invalid objects, application servers stoped working(maybe there is another reason don't know), Jboss and Tomcat are not starting, have some problems... Before these objects were Invalid everything was working perfectly.. not now, not sure but it may be the reason of this. I want to return them back... I found the utility DBMS_UTILITY.invalidate. it helped me... Now I trying to start app servers... –  kupa Sep 5 '11 at 14:36
show 1 more comment

Your Answer

 
discard

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.