Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mohana Subbu Ram.

Asked: January 31, 2018 - 4:59 am UTC

Last updated: February 01, 2018 - 1:01 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I want to view the time at which a particular column is created/added in the table.
USER_TAB_COLUMNS does not contain the column creation/modification timestamp.

USER_OBJECTS has LAST_DDL_TIME which is at the Table level. I need Column Level LAST_DDL_TIME and CREATION Time.

Is there any way to find this information.

Thanks in advance

Regards
Mohana Subbu Ram M

and Connor said...

Its unlikely.

Options to consider:

1) Some or all DDL statements are captured in the alert log, depending on the setting of "enable_ddl_logging"
2) Do you have any auditing enabled ? If so, check DBA_AUDIT_TRAIL
3) Do you have DDL triggers defined that might be capturing the details ?
4) If you did the change recently, you could use flashback to see when the column (dis)appeared

SQL> create table scott.t ( x int, y int, z int );

Table created.

SQL> select column_name from dba_tab_columns
  2  where owner = 'SCOTT'
  3  and  table_name = 'T';

COLUMN_NAME
------------------------------
X
Y
Z


SQL> alter table mcdonac.t add new_col int;

Table altered.

SQL> select column_name from dba_tab_columns
  2  where owner = 'SCOTT'
  3  and  table_name = 'T';

COLUMN_NAME
------------------------------
X
Y
Z
NEW_COL

SQL> select column_name from dba_tab_columns as of timestamp sysdate-120/86400
  2  where owner = 'SCOTT'
  3  and  table_name = 'T';

COLUMN_NAME
------------------------------
X
Y
Z



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Column creation time

Mohana Subbu Ram M, January 31, 2018 - 10:15 am UTC

Thanks for your response. It was very much informative and useful.
Connor McDonald
February 01, 2018 - 1:01 am UTC

glad we could help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database