Skip to content

How to Check DataFile Creation Date

Data File Creation Date

To check the creation date time of data files, we should query V$DATAFILE, not DBA_DATA_FILES.

First of all, we format the date time of current session.

SQL> set linesize 200;
SQL> column name format a60;
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

Then we perform a query on V$DATAFILE.

SQL> select name, creation_time from v$datafile order by 1;

NAME                                                         CREATION_TIME
------------------------------------------------------------ -------------------
/u01/app/oracle/oradata/ORCL/example01.dbf                   2020-07-20 23:25:31
/u01/app/oracle/oradata/ORCL/sysaux01.dbf                    2020-07-14 23:22:46
/u01/app/oracle/oradata/ORCL/system01.dbf                    2020-07-14 23:22:46

For a multitenant container database (CDB), you should additionally check the container id.

SQL> column con_id format 9;
SQL> select con_id, name, creation_time from v$datafile order by 1,2;

CON_ID NAME                                                         CREATION_TIME
------ ------------------------------------------------------------ -------------------
     1 /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf                 2019-04-17 00:56:21
     1 /u01/app/oracle/oradata/ORCLCDB/system01.dbf                 2019-04-17 00:56:09
     1 /u01/app/oracle/oradata/ORCLCDB/undotbs01.dbf                2019-04-17 02:11:02

As we can see, every data file has its own creation time, we can use the information to determine the creation date of a tablespace.

Leave a Reply

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