Need To find when table was altered

Question

I found a new column in a table of my oracle database which was not present before.Is there any way to find when table was altered.

Answer 1

You can look at the last_ddl_time in user_objects, or all_objects or dba_objects if you aren't connected as the owner:

select last_ddl_time
from user_objects
where object_type = 'TABLE'
and object_name = '<your table name>'

or

select last_ddl_time
from dba_objects
where object_type = 'TABLE'
and owner = '<your table owner>'
and object_name = '<your table name>'

That will tell you the last time any DDL was performed against the table; though that may not be the column addition if any other changes were made later (e.g. constraints, column modifications, etc.). Remember that the owner and table name need to be in the same case they are stored in the dictionary - usually uppercase (unless you're used quoted identifiers).

Quick demo:

create table t42 (id number);

Table T42 created.

select to_char(last_ddl_time, 'YYYY-MM-DD hh24:MI:SS') as last_change
from user_objects
where object_type = 'TABLE'
and object_name = 'T42';

LAST_CHANGE        
-------------------
2017-05-10 11:12:18

Then some time later...

alter table t42 add (new_column varchar(10));

Table T42 altered.

select to_char(last_ddl_time, 'YYYY-MM-DD hh24:MI:SS') as last_change
from user_objects
where object_type = 'TABLE'
and object_name = 'T42';

LAST_CHANGE        
-------------------
2017-05-10 11:14:22