FW: Last DDL Time for packages
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Jul 2009 16:38:22 -0400
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
Other related posts:
