Tablespace and their underlying datafiles

Now that I know where the control, data and the redo log files of my database "abc" are, I'm now interested in getting some information about the tablespaces. After reading the reams and reams of our lovely Oracle documentation I now know that tablespace are made up of one of more datafiles.

I was to first find out what the names of the tablespaces are for my database "abc" created by Oracle dbca.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP

Ok, now what are the files that are associated with these tablespaces.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf
USERS

/u02/app/oracle/oradata/ABC/datafile/o1_mf_undotbs1_6kt8jg8b_.dbf
UNDOTBS1

/u02/app/oracle/oradata/ABC/datafile/o1_mf_sysaux_6kt8jg6r_.dbf
SYSAUX


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/app/oracle/oradata/ABC/datafile/o1_mf_system_6kt8jg4q_.dbf
SYSTEM


SQL>