What I have learned from Tom Kyte - 1

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1150834842942
"create or replace only if the code actually changed and we need to replace it package"


What´s the meaning of the TIMESTAMP column in the DBA_OBJECTS view ? It´s confusing. The reference manual says it the time of the "specification" for the object. 
What does that mean ? Besides, which of the columns in DBA_OBJECTS reflects the time of a REPLACE action (like in CREATE OR REPLACE PROCEDURE) ?  


The timestamp is used in remote dependency checking.  It is the time of the last update 
that changed the specification of the object.  If the object is a PACKAGE -- the 
"specification" is clear.  If the object is a VIEW, the specification is perhaps not as 
clear.  The specification of a view is the number, names, and types of columns it 
returns.  Same with other objects -- their specification is their "external interface".  
Anytime their external interface has changed -- their timestamp will change.


We use this timestamp to manage dependencies between remote objects (eg: in a distributed 
system where we have local procedures that call remote procedures -- we have to 
invalidate local procedures that depend on remote procedures whose timestamps have 
changed).  


CREATED = date of creation of the object


LAST_DDL_TIME = last ddl on object, would include CREATE OR REPLACE (example below)


TIMESTAMP = last time the external "view" or "specification" of the object changed -- 
will be between created and last_ddl_time.


here is an example with a view:


tkyte@TKYTE816> create view v
  2  as
  3  select * from dual;


View created.


tkyte@TKYTE816>
tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:26 13:11:26 13:11:26


it starts with the timestamp = created = last_ddl_time.  Now, lets alter the view 
(doesn't change its EXTERNAL interface):


tkyte@TKYTE816>
tkyte@TKYTE816> exec dbms_lock.sleep(2)


PL/SQL procedure successfully completed.


tkyte@TKYTE816>
tkyte@TKYTE816> alter view v compile
  2  /


View altered.


tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:26 13:11:26 13:11:28


Now, the timestamp did not change -- it is still the same as the created.  The 
last_ddl_time did however change as we just did some DDL on the view.  We can in fact do 
some DDL on the view you might think would change the timestamp:




tkyte@TKYTE816>
tkyte@TKYTE816> exec dbms_lock.sleep(2)


PL/SQL procedure successfully completed.


tkyte@TKYTE816> create or replace view v
  2  as
  3  select * from dual;


View created.


tkyte@TKYTE816>
tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:26 13:11:26 13:11:30


But it does not -- the "signature" or external interface of the view did not change.  
If we do a create or replace that changes the interface of the view:


tkyte@TKYTE816>
tkyte@TKYTE816> exec dbms_lock.sleep(2)


PL/SQL procedure successfully completed.


tkyte@TKYTE816> create or replace view v
  2  as
  3  select dummy x from dual;


View created.


tkyte@TKYTE816>
tkyte@TKYTE816> select timestamp, to_char(created,'hh24:mi:ss'),
  2         to_char(last_ddl_time,'hh24:mi:ss')
  3    from dba_objects
  4   where object_name = 'V'
  5     and owner = USER
  6  /


TIMESTAMP           TO_CHAR( TO_CHAR(
------------------- -------- --------
2000-12-13:13:11:32 13:11:26 13:11:32


tkyte@TKYTE816>


And so now the timestamp is different since the "specification" of this view is 
different.