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

User · Answer

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

User · Answer

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     lt name of schema owner gt      and object name     lt name of table gt     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

User · Answer

SELECT CREATED FROM USER OBJECTS WHERE OBJECT NAME   lt  lt YOUR TABLE NAME gt  gt

User · Answer

SELECT created   FROM dba objects  WHERE object name    lt  lt your table name gt  gt     AND owner    lt  lt owner of the table gt  gt     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          lt  lt other columns gt  gt    FROM  lt  lt your table gt  gt    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

User · Answer

Try this query   SELECT sysdate FROM schema name table name   This should display the timestamp that you might need

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

Examples related to oracle

Examples related to oracle11g

Examples related to database-metadata