Friday, November 13, 2015

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: