Re: LAST_DDL_TIME from ALL_OBJECTS

Paul,

I'm sure others will point out too that GRANT *is* a DDL, because it modifies data dictionary (sysauth$, etc). Some shops do daily grant's until they find the impact of cursor reloads on library cache.

To find the time a column was added to the table recently, check v$sqlstats if you're on 10g. It has the last_active_time column. Otherwise rely on redo log drilling, using log miner for instance.

Last_ddl_time does not report all DDLs's times. Analyze is one. Compile trigger is another. There may be other cases.

Yong Huang

> I have been attempting to find the modification dates of varios objects by
> interrogating ALL_OBJECTS.LAST_DDL_TIME. However, I have noted that
> granting object privileges causes the last DDL time to be changed (even
> though granting an object priv is not actually DDL).
> Example
>
> 15:00 HOURS:
> SQL> ALTER TABLE schema_1.table_1 ADD col_1 VARCHAR2(100);
>
> 15:15 HOURS:
> SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON schema_1.table_1 TO schema_2;
>
> 15:30 HOURS:
> SQL> select object_name, TO_CHAR(LAST_DDL_TIME, 'YYYYMMDD HH24:MI:SS')
> last_ddl
> from all_objects
> where owner = 'SCHEMA_1'
> and object_name = 'TABLE_1';
>
> OBJECT_NAME LAST_DDL
> ------------------------------ -----------------
> TABLE_1 20080701 15:15:00 <- NOTE, 15:15, the time
> of the Obj Priv grant, not 15:00 when the table was modified by adding a
> column
>
> My question is: Which DD view can I interrogate to find the the time that
> the column was added to the table.
>
> Thank you
>
> P Clark
      

--
http://www.freelists.org/webpage/oracle-l