How to Check Tablespace Creation Time in Oracle

https://anuj-singh.blogspot.com/2025/08/oracle-datafile-info.html

col FILE_NAME for a45
col tablespace_name for a25
col creation_time for a15
col last_datafile_creation_time for a25
select  a.tablespace_name, min(b.creation_time) creation_time , max(b.creation_time) last_datafile_creation_time,count(FILE#) total_files from dba_data_files a, v$datafile b 
where a.file_name=b.name
group by a.tablespace_name;

set pages 500 lines 400 term off
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
set pages 700 lines 500
col TABLESPACE_NAME for a15
col FILE_NAME format a95
col MAXSIZE_MB for 99999.99
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
col file_name 	        for &fname   heading "File Name"
select d.TABLESPACE_NAME
, d.FILE_NAME
, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, 
d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
,CREATION_TIME
,t.BIGFILE,ENCRYPTED,t.COMPRESS_FOR,t.CONTENTS,d.STATUS
from dba_data_files d, v$datafile v
 ,DBA_TABLESPACES t 
where 1=1
and d.FILE_ID = v.FILE#
--and d.TABLESPACE_NAME='ANUJ' ---<<<
and d.tablespace_name = upper( decode('&&tablespace_name',null,d.tablespace_name,'&&tablespace_name'))
and d.TABLESPACE_NAME=t.TABLESPACE_NAME
order by d.TABLESPACE_NAME, d.FILE_NAME
;