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:
 
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>

No comments: