GRANT SELECT Updates LAST_DDL_TIME

DDL stands for Data Definition Language. The CREATE TABLE, ALTER TABLE and DROP TABLE statements are examples of DDL. LAST_DDL_TIME is a column in the USER_OBJECTS view. It records the date and time of the most recent DDL statement applied to the object in question. Even granting SELECT access on a table will update its LAST_DDL_TIME. You can see this in the example below, which I tested in an Oracle 11.2 database.

 

First I created a table and checked that its LAST_DDL_TIME matched the creation time:

 

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

18:39:50

 

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now

  2  from user_objects

  3  where object_name = 'TAB1'

  4  /

 

TIME_NOW

--------

18:39:50

 

SQL>

 

Then I waited 10 seconds, noted the time again, ran some DDL on the table and checked that this had updated the LAST_DDL_TIME:

 

SQL> exec sys.dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

18:40:00

 

SQL> alter table tab1 add(col2 number)

  2  /

 

Table altered.

 

SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now

  2  from user_objects

  3  where object_name = 'TAB1'

  4  /

 

TIME_NOW

--------

18:40:00

 

SQL>

 

Finally, I waited a further 10 seconds, noted the time, did a GRANT SELECT on the table to another user and checked that the LAST_DDL_TIME had been updated again:

 

SQL> exec sys.dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

18:40:10

 

SQL> grant select on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now

  2  from user_objects

  3  where object_name = 'TAB1'

  4  /

 

TIME_NOW

--------

18:40:10

SQL>