oracle11g - 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.