How to find out when a particular table was created in Oracle?

Question

In Oracle, is there a way to find out when a particular table was created?

Similarly, is there a way to find out when a particular row was inserted/last updated?

Answer 1

SELECT created   FROM dba_objects  WHERE object_name = <<your table name>>    AND owner = <<owner of the table>>    AND object_type = 'TABLE' 

will tell you when a table was created (if you don't have access to DBA_OBJECTS, you could use ALL_OBJECTS instead assuming you have SELECT privileges on the table).

The general answer to getting timestamps from a row, though, is that you can only get that data if you have added columns to track that information (assuming, of course, that your application populates the columns as well). There are various special cases, however. If the DML happened relatively recently (most likely in the last couple hours), you should be able to get the timestamps from a flashback query. If the DML happened in the last few days (or however long you keep your archived logs), you could use LogMiner to extract the timestamps but that is going to be a very expensive operation particularly if you're getting timestamps for many rows. If you build the table with ROWDEPENDENCIES enabled (not the default), you can use

SELECT scn_to_timestamp( ora_rowscn ) last_modified_date,        ora_rowscn last_modified_scn,        <<other columns>>   FROM <<your table>> 

to get the last modification date and SCN (system change number) for the row. By default, though, without ROWDEPENDENCIES, the SCN is only at the block level. The SCN_TO_TIMESTAMP function also isn't going to be able to map SCN's to timestamps forever.

Answer 2

You can query the data dictionary/catalog views to find out when an object was created as well as the time of last DDL involving the object (example: alter table)

select *    from all_objects   where owner = '<name of schema owner>'    and object_name = '<name of table>' 

The column "CREATED" tells you when the object was created. The column "LAST_DDL_TIME" tells you when the last DDL was performed against the object.

As for when a particular row was inserted/updated, you can use audit columns like an "insert_timestamp" column or use a trigger and populate an audit table