Skip to content

How to Check Tablespace Creation Date

  • by

Tablespace Create Time

There's no direct way to know the creation date of a tablespace, because either DBA_TABLESPACES or V$TABLESPACE doesn't have such information. But we know there should be at least one data file in a new tablespace, so we can use the information in V$DATAFILE to determine the creation date of tablespaces.

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

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

Session altered.

Then we look for the earliest creation date of data files in every tablespace by GROUP BY clause and MIN() function.

SQL> column tablespace_name format a20;
SQL> column tablespace_create_time format a20;
SQL> select t.name tablespace_name, min(d.creation_time) tablespace_create_time from v$datafile d, v$tablespace t where d.ts# = t.ts# group by t.name order by 1;

TABLESPACE_NAME      TABLESPACE_CREATE_TI
-------------------- --------------------
ERPAPP2              2022-05-26 11:39:01
EXAMPLE              2020-07-20 23:25:31
SYSAUX               2020-07-14 23:22:46
SYSTEM               2020-07-14 23:22:46
UNDOTBS1             2020-07-14 23:22:46
USERS                2020-07-14 23:22:53

It lists creation dates for all tablespaces.

As you know, a tablespace is a logical set of data files, the maximum size of a tablespace is actually limited by the maximum number of data files in it.

Leave a Reply

Your email address will not be published.