Be aware that the last_ddl_time is updated for grants so the date value may not reflect an actual code change but may be the result of just a grant. My test shows only the specification date gets changed. UT1 > select object_type, to_char(last_ddl_time,'YYYYMMDD HH24:MI:SS') LTIME 2 from dba_objects 3 where object_name = 'DBA_MSG' 4 and object_type like 'PACKAGE%' 5 / OBJECT_TYPE LTIME ------------------ ----------------- PACKAGE 20090728 16:32:17 PACKAGE BODY 20090120 15:37:15 UT1 > grant execute on mark.dba_msg to public 2 / Grant succeeded. UT1 > select object_type, to_char(last_ddl_time,'YYYYMMDD HH24:MI:SS') LTIME 2 from dba_objects 3 where object_name = 'DBA_MSG' 4 and object_type like 'PACKAGE%' 5 / OBJECT_TYPE LTIME ------------------ ----------------- PACKAGE 20090728 16:36:01 PACKAGE BODY 20090120 15:37:15 -- Mark D Powell -- Phone (313) 592-5148 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stephane Faroult Sent: Tuesday, July 28, 2009 4:14 PM To: wellmetus@xxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: Last DDL Time for packages Roger, You have TWO objects in DBA_OBJECTS for a package, the package and the package body. If the interface doesn't change, LAST_DDL_TIME isn't updated for the package - but is for the package body. ORACLE-SQL> @foo Package created. Package body created. ORACLE-SQL> select object_name, object_type 2 ORACLE-SQL> ORACLE-SQL> ed Wrote file afiedt.buf 1 select object_name, object_type, last_ddl_time 2 from user_objects 3* where object_name = 'FOO' ORACLE-SQL> / OBJECT_NAME ------------------------------------------------------------------------ -------- OBJECT_TYPE LAST_DDL ------------------- -------- FOO PACKAGE 22:10:05 FOO PACKAGE BODY 22:10:06 ORACLE-SQL> save pack Created file pack.sql ORACLE-SQL> ed foo [ some change to the package body] ORACLE-SQL> @foo Package created. Package body created. ORACLE-SQL> @pack OBJECT_NAME ------------------------------------------------------------------------ -------- OBJECT_TYPE LAST_DDL ------------------- -------- FOO PACKAGE 22:10:05 FOO PACKAGE BODY 22:11:38 ORACLE-SQL> Roger Xu wrote: > Hi List, > > We are trying to use below SQL to obtain the last time at which a > package/procedure/function being modified. It seems to be working for > procedure and functions, but not for the package. > > select to_char(LAST_DDL_TIME, 'YYYY MM DD HH:MI:SS') from dba_OBJECTs > where OBJECT_NAME = 'MYPACKAGE'; > > Is there any other way to find out a package's source code was > changed? Maybe I should look into the link between package and procedures? > > Thanks, > > Roger Xu -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l