Oracle in World: How to know when a table/object's ddl/code was last modified

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

Oracle in World

Thursday, February 19, 2009

How to know when a table/object's ddl/code was last modified

No comments:

Post a Comment