RECENTLY MODIFIED OBJECTS IN DATABASE

Subject : When Timestamp and Last_Ddl_time columns of dba_objects will differ?

Doc ID: 309235.1 Type: BULLETIN

Modified Date: 18-DEC-2007 Status: PUBLISHED

PURPOSE

-------

The purpose of this document is to give a clear understanding on:

1.The meaning of Last_ddl_time and Timestamp column of dba_objects

2.Under what circumstances these 2 columns differ

SCOPE & APPLICATION

-------------------

For all analysts and customers to get a clear understanding of these

2 important columns of dba_objects

LAST_DDL_TIME and TIMESTAMP

-----------------------------

First we should understand the meaning of last_ddl_time and timestamp columns of

dba_objects with a small example

LAST_DDL_TIME -->Timestamp for the last modification of the object resulting

from a DDL command (including grants and revokes)

TIMESTAMP --->Timestamp for the specification of the object

+Created a table called YY

SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from

dba_objects where object_name='YY';

DDL TIMESTAMP

-------------------- -------------------

30-MAR-2005 21:08:34 2005-03-30:21:08:34

As it can be seen when the table is created both has the same value

SQL> GRANT SELECT ON YY TO PUBLIC; <-----------DDL performed

Grant succeeded.

SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from

dba_objects where object_name='YY';

DDL TIMESTAMP

-------------------- -------------------

30-MAR-2005 21:13:44 2005-03-30:21:08:34

It is seen that last_ddl_time is different,TIMESTAMP remains same as object creation

Now we will see under what circumstances last_ddl_time and timestamp will differ.

1.When DDL is performed on the dependent objects of the table.

For example when an index is created or altered.

2.When a table is truncated

3.When roles/privileges are granted/revoked.

A small example to better understand point 1 :

Test case:

-----------

SQL> CREATE TABLE YY(A NUMBER);

Table created.

SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from

dba_objects where object_name='YY';

DDL TIMESTAMP

-------------------- -------------------

31-MAR-2005 15:16:54 2005-03-31:15:16:54

SQL> CREATE INDEX YYI ON YY(A);

Index created.

SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from

dba_objects where object_name='YY';

DDL TIMESTAMP

-------------------- --------------

31-MAR-2005 15:21:53 2005-03-31:15:16:54

It can be clearly seen that though we did not perform any DDL operation on the table

but the creation of an index on the table changed its last_ddl_time.

Timestamp does not change

_____________________________________________________________

Another Good Example

From the dba_objects/user_objects/all_objects view you can know about the information about when an object(table,index,function etc) was created or when the last DDL operation was done against the table or when last compilation was done.

As of other oracle views.
DBA_OBJECTS contains all database objects.
USER_OBJECTS contains all objects that is owned by the current user.
ALL_OBJECTS contains all objects on which current user has access.

Note that obj is the synonym of USER_OBJECTS view.

The CREATED column of the view contains date about when an object was created.

The LAST_DDL_TIME column of the view contains date about when the object was last modified by a DDL statement. Note that this column value also contain the timing of revoke and grant that was issued against the object. Similarly on procedure, function, trigger if you compile the object then only LAST_DDL_TIME is only modified.

The TIMESTAMP column of the view contains timestamp of the last ddl time excluding any grants, revoke or any compile time.

Before proceed let's set nls_date_format on sql*plus so that we can see the timings of date data type.

SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 21-SEP-08 00:49:16 2008-09-21:00:43:11 VALID

Now I am adding a column to the table. After adding column see the LAST_DDL_TIME
and TIMESTAMP column value is changed.

SQL> alter table test add col2 number;

Table altered.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:02:29 2009-02-20:11:02:29 VALID

SQL> select sysdate from dual;

SYSDATE
------------------
20-FEB-09 11:02:47

Now I grant select on test table to user arju. After grant note that LAST_DDL_TIME is changed but TIMESTAMP value is not changed.

SQL> grant select on test to arju;

Grant succeeded.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:12:33 2009-02-20:11:02:29 VALID

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST'
and object_type='PROCEDURE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 10:42:11 2009-02-20:10:42:11 VALID

SQL> alter procedure p_test compile;

Procedure altered.

SQL>  select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST'
and object_type='PROCEDURE';

CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 11:18:41 2009-02-20:10:42:11 VALID