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?

Cichocki

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.

Gladygladys

This can be applied for View search – Gussi

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

Sentience

Answer 3

You copy and paste the following code. It will display all the tables with Name and Created Date

SELECT object_name,created FROM user_objects
WHERE object_name LIKE  '%table_name%'
AND object_type = 'TABLE'; 

Note: Replace '%table_name%' with the table name you are looking for.

Prevalent

I've implemented this by using this example and added condition for ordering from oldest table to newest and with adding 60 days condition and solution works fine: select * FROM user_objects where object_type = 'TABLE' and created > current_date - 60 order by timestamp – Radius

Note: Maybe better to use ALL_OBJECTS because that table has objects for all schema and USER_OBJECTS table is related only to one schema. – Radius

Answer 4

SELECT CREATED FROM USER_OBJECTS WHERE OBJECT_NAME='<<YOUR TABLE NAME>>'

Tenderfoot

And how is this different to the 4 year old accepted answer? – Antiphlogistic

Here I am using different table nothing else. Difference between dba_object and user_object – Tenderfoot

Please edit your answer to add an explanation of how your code works and how it solves the OP's problem. Many SO posters are newbies and will not understand the code you have posted. – Smoot

Answer 5

Try this query:

SELECT sysdate FROM schema_name.table_name;

This should display the timestamp that you might need.

Bushido