How to Find Oracle Table Creation Date Time

  • by

We usually retrieve table information from ALL_TABLES, but it has no table creation date. This is because table is also an object type, so we should turn to ALL_OBJECTS.

  1. Table Creation Date
  2. Index Creation Date

A. Oracle Table Creation Date

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select created from all_objects where object_type = 'TABLE' and owner = 'ERPAPP' and object_name = 'PAYMENT_DAY';

CREATED
-------------------
2017-11-03 15:49:39

Further reading: How Big a Table Can be?

B. Oracle Index Creation Date

As for index creation date, it's the same way to check.

SQL> select created from all_objects where object_type = 'INDEX' and owner = 'ERPAPP' and object_name = 'PAYMENT_DAY_IDX';

CREATED
-------------------
2017-11-05 09:36:02

Leave a Reply

Your email address will not be published. Required fields are marked *